The query returns all the columns from the table "Pictures". This in itself may be a problems, since I assume the picture is stored in a BLOB.
If you instead do: SELECT <select list Related Picture info you need>, picBLOB FROM... Assuming picBLOB is the column name, use GetChunk from the picBLOB field (see MSDN) or the ADO Stream object. You will then either need to output the picture data to a (temporary) file and link to it from your HTML, or you may be able to stream it directly from your app. HTH, Tore. -----Original Message----- From: [EMAIL PROTECTED] [mailto:Cadieux.Tim@;fin.gc.ca] Sent: Wednesday, October 02, 2002 12:04 PM To: ActiveServerPages Subject: RE: Random picture from a Database I can't believe I have to ask this, but how do I get the results of this query? It's not like I'm asking for a Field ? Tim -----Original Message----- From: Bostrup, Tore [mailto:TBostrup@;teamia.com] Sent: October 1, 2002 5:51 PM To: ActiveServerPages Subject: RE: Random picture from a Database CONVERT is a SQL Server data type conversion function. Form MS Access/Jet, use CLng() instead. You should modify the statement a little in order to stay within the boundaries... sSQL="SELECT TOP 1 a.* FROM Pictures a WHERE a.PIC_ID <= (SELECT MIN(b.PIC_ID) + CLng(ROUND(RAND() * (MAX(b.PIC_ID) - MIN(b.PIC_ID)), 0))" sSQL= sSQL & " FROM Pictures b) ORDER BY a.PIC_ID DESC" HTH, Tore. -----Original Message----- From: [EMAIL PROTECTED] [mailto:Cadieux.Tim@;fin.gc.ca] Sent: Tuesday, October 01, 2002 3:32 PM To: ActiveServerPages Subject: RE: Random picture from a Database David, I tried to use your code and came up with this, though it might be very buggy since I've never seen this type of thing before.... sSQL="SELECT TOP 1 a.* FROM Pictures a WHERE a.PIC_ID <= (SELECT MIN(b.PIC_ID) + CONVERT(int, ROUND(RAND() * MAX(b.PIC_ID), 0))" sSQL= sSQL & " FROM Pictures b) ORDER BY a.PIC_ID DESC" I get the below error.............(also, my Intro.inc file is only 20 lines long, so I have no idea where "Line 397" comes from) Microsoft JET Database Engine (0x80040E14) Undefined function 'CONVERT' in expression. /provinces/bc_yukon/intro.inc, line 397 Tim -----Original Message----- From: David L. Penton [mailto:david@;davidpenton.com] Sent: September 26, 2002 9:32 AM To: ActiveServerPages Subject: RE: Random picture from a Database SELECT TOP 1 a.* FROM MyTable a WHERE a.id <= ( SELECT MIN(b.id) + CONVERT(int, ROUND(RAND() * MAX(b.id), 0)) FROM MyTable b ) ORDER BY a.ID DESC is the other query [that should be converted to your particular platform - Access in this case) but of course the SQL Server solution is good (well, for SQL Server :-) SELECT * --put column names here!!! FROM tbl ORDER BY NEWID() David L. Penton, Microsoft MVP JCPenney Application Specialist / Lead "Mathematics is music for the mind, and Music is Mathematics for the Soul. - J.S. Bach" [EMAIL PROTECTED] Do you have the VBScript Docs or SQL BOL installed? If not, why not? VBScript Docs: http://www.davidpenton.com/vbscript SQL BOL: http://www.davidpenton.com/sqlbol -----Original Message----- From: Andy Hayman [mailto:AndyH@;KEN.co.uk] Odd I am using a DSN to connect to an Access DB using: strSQL = "SELECT TOP 1 * FROM Comments ORDER BY NEWID()" I am getting [Microsoft][ODBC Microsoft Access Driver] Undefined function 'NEWID' in expression. Andy > -----Original Message----- > From: Andrew Zetterman [mailto:AZetterman@;ACMCentral.com] > > SQL Books Online > ----------------------------- > > NEWID > Creates a unique value of type uniqueidentifier. > > Syntax > NEWID ( ) > > Return Types > uniqueidentifier > ----------------------------- > > So if you put it into your order by it will generate a unique > id for each record and then it will sort by that column. > Each time you query the unique id will be different so the > order of your records will change. So the top 1 record will > randomly change. > > Andrew > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:Cadieux.Tim@;fin.gc.ca] > > How exactly does the below code work?, I haven't seen that before. > > -----Original Message----- > From: Andrew Zetterman [mailto:AZetterman@;ACMCentral.com] > > David Penton posted a query a few weeks ago that could select > a random record. > > SELECT TOP 1 col FROM tbl ORDER BY NEWID() > > HTH, > > Andrew > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:Cadieux.Tim@;fin.gc.ca] > > But how do I get a random pic from the remaining pics? > > -----Original Message----- > From: Matthew Small [mailto:matt6@;showstopperonline.com] > > Put a binary field in your table. If the field is set, don't > use that picture. > > Matthew Small > IT Supervisor > Showstopper National Dance Competitions > 3660 Old Kings Hwy > Murrells Inlet, SC 29576 > 843-357-1847 > http://www.showstopperonline.com > > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:Cadieux.Tim@;fin.gc.ca] > > I just want to show a random picture form all those in my > database. I used to just get the number of pics in my DB, > then geta RND # and then call that ID, but, because I > Autonumber them, when a pic got deleted, I would sometimes > get an error. > > So, how can I get a rnd Pic ID from a database that has say > 20 pics, but 10 are set to NOT be shown....ideas? > > Tim --- You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] To unsubscribe send a blank email to %%email.unsub%% --- You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] To unsubscribe send a blank email to %%email.unsub%% --- You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] To unsubscribe send a blank email to %%email.unsub%% --- You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] To unsubscribe send a blank email to %%email.unsub%% --- You are currently subscribed to activeserverpages as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED]
