Please disregard my last post, I ignored the obvious and my ADOVBS.inc file was missing...
I did however have to change the RAND() to RND() in order that this work. Thanx for the help. -----Original Message----- From: Bostrup, Tore [mailto:[EMAIL PROTECTED]] 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:[EMAIL PROTECTED]] 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:[EMAIL PROTECTED]] 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:[EMAIL PROTECTED]] 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:[EMAIL PROTECTED]] > > 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:[EMAIL PROTECTED]] > > How exactly does the below code work?, I haven't seen that before. > > -----Original Message----- > From: Andrew Zetterman [mailto:[EMAIL PROTECTED]] > > 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:[EMAIL PROTECTED]] > > But how do I get a random pic from the remaining pics? > > -----Original Message----- > From: Matthew Small [mailto:[EMAIL PROTECTED]] > > 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:[EMAIL PROTECTED]] > > 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 PROTECTED]
