Tim, you're asking for a RECORD, the first record in the
randomly-ordered set of records. You retrieve your values as you do any
recordset:
If Not rs.EOF Then
intMyPicID = rs("PIC_ID")
strMyPicPath = rs("PicturePath")
Else
...
End If
or whatever your fields are called.
HTH
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
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:[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.unsub%%
---
You are currently subscribed to activeserverpages as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
