help with random select SQL query in Access

[Jimmy]

i have a database with one table that contains 2 fields: ID, EMAIL_ADDRESS

i need to pull a random email address from the database. im trying to use
the example found here:
http://databases.aspfaq.com/database/how-do-i-retrieve-a-random-record.html

but it simply does not work! the error i get is "No value given for one or
more required parameters"

here is my line of code how it appears in my ASP page:

"SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM TABLE1 ORDER BY
r"

can someone please help with this?

 

[Douglas J. Steele]

It would appear that no one bother trying that code before posting it, as
it's syntactically incorrect!

Try:

"SELECT TOP 1 EMAIL_ADDRESS, Rnd(" & randNum & ") FROM TABLE1 ORDER BY Rnd("
& randNum & ")"

However, I really doubt that it'll work: I don't believe that the Randomize
statement ahead of setting the SQL will have any impact on the query that's
being run.

To be honest, I'm not sure it's possible through ASP to Jet.

 

[Jimmy]

thank you. at least youre making sense! LOL

your code always displays the last record in the database... but hey, at
least no error!

do you know of a better way to get a random record?

the way i was doing it was opening a recordset and getting the "count"
property. then id generate a random number between 1 and count, then do a
MoveTo(randomnumber)

that works but im told its very inefficient.

 

[Douglas J. Steele]

Yeah, what's probably happening is that every row is getting the same random
number.

As I said, I don't think there's any easy way to do this using ASP
connecting to a Jet database.

The approach you were taking is certainly a valid one.

Do you have any numeric ID field on the table? You could pick a random
number between the lowest and highest ID values, and have the query return
the row that's closest to that number. That way you wouldn't have to deal
with the entire recordset.

Something like:

"SELECT TOP 1 EMAIL_ADDRESS FROM TABLE1 ORDER BY ID DESC WHERE ID <= " &
GeneratedID

 

[Jimmy]

yes i have the default ID column that autonumbers...
would your approach work if as time goes by i delete rows from the middle of
the database?
now the ID field wouldnt be in order numerically. it might go 1,2,3,5,56,89
etc...

 

[Douglas J. Steele]

That's why I suggested "SELECT TOP 1...WHERE ID <= " & GeneratedID, rather
"SELECT...WHERE ID = " & GeneratedID

Of course, I can't attest to just how random this will be. If you've got big
gaps (such as in your sample numbers), there will be an awful lot of
generated numbers that will result in the same record back. For example, a
random number between 5 to 55 inclusive will result in record 5 being
returned.