how to choose a random record from a database

[Jimmy]

ill have a database with 1 table and 3 fields:

ID FIRSTNAME LASTNAME

(the ID field will be the auto incrementing index)

there might be 10 records in the DB, there might be 10,000.
i need to open the DB and randomly select a record (and then display the
name, which i dont have a problem with)
how can i randomly select a record? im guessing id have to open a recordset
and check the count to get the number of records, so lets say there were 100
records. i imagine i would have to generate a random number between 1 and
100....

anyone have a small example?

[Jimmy]

Access for now....
can i use the same kind of query? would you be so kind as to show me an
example based on what i have already shown you?

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartr eb.noraawrote in message
news:ug******** ******@TK2MSFTN GP06.phx.gbl...

this opens up the table, gets a count of num records, generates a random
number between 0 and numrecords-1, then moves to that record, and displays
a random email address.

seems like it works perfectly... do you see any issues?

>>

I suppose you haven't read any of my comments about why the suggested
route is better. For example, you don't need to pull the whole table to
the client in order to pick a random row.

If you are going to be using SQL Server Express, then you can say it as
simply as

SELECT TOP 1 Email_Address FROM Table1 ORDER BY NEWID();

(As the link suggested way back at the start of this thread, but I still
suppose you haven't read it (at least not in full).)

A

[Aaron Bertrand [SQL Server MVP]]

Access for now....

can i use the same kind of query? would you be so kind as to show me an
example based on what i have already shown you?

Can you use what I already showed you? Or do I need to create an Access
database and attach everything for you?

<%
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1

set conn = CreateObject("A DODB.Connection ")
' create connection string here

sql = "SELECT TOP 1 EmailAddress," & _
"r = Rnd(" & randNum & ")" & _
"FROM Table1 " & _
"ORDER BY r"

set rs = conn.execute(sq l)

response.write rs("EmailAddres s")

' ...
rs.close: set rs = nothing
conn.close: set conn = nothing
%>

Have you tried this at all? STOP WORRYING ABOUT THE SIZE OF THE RANDOM
NUMBER POOL AND HOW MANY ROWS ARE IN THE TABLE. JUST TRY IT, PLEASE. This
thread has gone about 40 messages longer than it should have.

Of course, you are more than free to use what 'seems like it works
perfectly' if you like slicing bread with a chainsaw or flossing with a
bungee cable.

A

[Bob Barrows [MVP]]

Look. Just change your code to this and try it:

<%
Dim oConn, oRS, randNum, sql
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
sql = "SELECT TOP 1 EMAIL_ADDRESS, " & _
"r = Rnd(" & randNum & ") " & _
"FROM TABLE1"
Set oConn=Server.Cr eateObject("ADO DB.Connection")
oConn.Provider= "Microsoft.Jet. OLEDB.4.0"
oConn.Open Server.MapPath( "temp.mdb")
Set oRS=oconn.Execu te(sql, , 1)

Response.Write oRS("EMAIL_ADDR ESS")

oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
%>
Jimmy wrote:

ok i got ya...
so is there a way to "oRS.Move" to a particular record without having
to do it this way?
ie, can i open the DB to get a record count without SELECTing
anything?

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomwrote in message
news:%2******** ********@TK2MSF TNGP05.phx.gbl. ..

>Jimmy wrote:

>>this is what i have working so far... tell me what you think:
oRS.Open "SELECT EMAIL_ADDRESS FROM TABLE1", oConn, adOpenStatic,
adLockReadOnl y
Randomize()
randNum = CInt((oRS.Recor dCount - 1) * Rnd)

Response.Writ e("RecordCoun t: " & oRS.RecordCount & "<br><br>")

oRS.Move randNum

>>>
seems like it works perfectly... do you see any issues?

Yes. You're retrieving all the records from the database when you
only need one of them. Not a very efficient use of network or server
resources.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Aaron Bertrand [SQL Server MVP]]

<shudder>

Can't wait to see what happens here when we graduate to a CRUD interface
(create/retrieve/update/delete)...

[Bob Barrows [MVP]]

Aaron Bertrand [SQL Server MVP] wrote:

<shudder>

Can't wait to see what happens here when we graduate to a CRUD
interface (create/retrieve/update/delete)...

?
That sound you hear is the sound of that statement whizzing over my
head. :-)
Shall we take this offline?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Jimmy]

it doesnt like your code.... missing parameter here:

Set oRS=oconn.Execu te(sql, , 1)

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomwrote in message
news:eA******** ******@TK2MSFTN GP06.phx.gbl...

Look. Just change your code to this and try it:

<%
Dim oConn, oRS, randNum, sql
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
sql = "SELECT TOP 1 EMAIL_ADDRESS, " & _
"r = Rnd(" & randNum & ") " & _
"FROM TABLE1"
Set oConn=Server.Cr eateObject("ADO DB.Connection")
oConn.Provider= "Microsoft.Jet. OLEDB.4.0"
oConn.Open Server.MapPath( "temp.mdb")
Set oRS=oconn.Execu te(sql, , 1)

Response.Write oRS("EMAIL_ADDR ESS")

oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
%>
Jimmy wrote:

>ok i got ya...
so is there a way to "oRS.Move" to a particular record without having
to do it this way?
ie, can i open the DB to get a record count without SELECTing
anything?

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomwrote in message
news:%2******* *********@TK2MS FTNGP05.phx.gbl ...

>>Jimmy wrote:
this is what i have working so far... tell me what you think:
oRS.Open "SELECT EMAIL_ADDRESS FROM TABLE1", oConn, adOpenStatic,
adLockReadOn ly
Randomize( )
randNum = CInt((oRS.Recor dCount - 1) * Rnd)

Response.Wri te("RecordCount : " & oRS.RecordCount & "<br><br>")

oRS.Move randNum
seems like it works perfectly... do you see any issues?
Yes. You're retrieving all the records from the database when you
only need one of them. Not a very efficient use of network or server
resources.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Mike Brind]

Look, the best way to see if anything works is to try it out. If, when
trying something out you get an error message, copy and paste the
meaningful part of the message into google, where you will more than
likely find that the earliest results either point you to a thread in
this or another similar group, or to aspfaq.com. If you can't make
sense of the answer, or don't get any meaningful results, post a new
topic here.

If it seems to work ok, try it a few times to make sure it always
works. If you get odd results, come back here and show the code you
are using with a description of the issues.

In addition, most of the SQL statements you are likely to run (at this
stage) against SQL server 2005 can be run against Access without
modification and vice versa. And anyway, if your SQL statement causes
an error while trying something out, google that too. Causing errors
to be raised during development and testing don't reduce your life
expectancy or anything. If they did, I'd be long gone from this world.

So, to summarise, if you want to know if the TOP 1 will run against
Access, try it and see.

--
Mike Brind
Jimmy wrote:

no, the confusion was because i had SQL Express at first but then was forced to go with Access. so i didnt know what would still work. it has been pointed out that this code is bad:

<%
Dim oConn, oRS, randNum

Set oConn=Server.Cr eateObject("ADO DB.Connection")
Set oRS=Server.Crea teObject("ADODB .recordset")

oConn.Provider= "Microsoft.Jet. OLEDB.4.0"
oConn.Open Server.MapPath( "temp.mdb")

oRS.Open "SELECT EMAIL_ADDRESS FROM TABLE1", oConn, adOpenStatic, adLockReadOnly
Randomize()
randNum = CInt((oRS.Recor dCount - 1) * Rnd)

Response.Write( "RecordCoun t: " & oRS.RecordCount & "<br><br>")

oRS.Move randNum
Response.Write oRS("EMAIL_ADDR ESS")

oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
%>

so i wanted to know if the "TOP 1" method could be done in access, and maybe see an example based on what i have here.

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartr eb.noraawrote in message news:OD******** *****@TK2MSFTNG P06.phx.gbl...

What, this?

>randNum = (CInt((recordco unt-1) * Rnd) + 1)

Again, you need to show us what you are doing with randNum before we can
comment.

But yes, as we've already suggested, there are issues.

My guess is you are going to say

sql = "SELECT EmailAddress FROM Table1 WHERE PK = " & RandNum

And like I already commented, this won't work reliably. Never mind the
unnecessary roundtrip to count the number of rows in the table.

I think you need to stop theorizing code and deal with this when you can
actually test it against a real database and understand the differences and
what we are talking about. Until then it seems you are hellbent on just
doing it your way and ignoring our advice.

A


"Jimmy" <[e-mail] in message
news:Od******** ********@TK2MSF TNGP06.phx.gbl. ..

thank you.
im currently testing with an access db. do you see any issues with my
previous random record generating code?

------=_NextPart_000_ 0049_01C6D359.C 5231A80
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
X-Google-AttachSize: 4297

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2963" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=Arial size=2></FONT><FONT face=Arial size=2>no, the confusion
was because i had SQL Express at first but then was forced to go with Access. so
i didnt know what would still work. it has been pointed out that this code is
bad:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>
<DIV><FONT face=Arial size=2></FONT><FONT face=Arial color=#0000ff
size=1><STRONG> &lt;%<BR>Dim oConn, oRS, randNum</STRONG></FONT></DIV><FONT
face=Arial color=#0000ff size=1><STRONG>
<DIV><BR>Set oConn=Server.Cr eateObject("ADO DB.Connection") <BR>Set
oRS=Server.Crea teObject("ADODB .recordset")</DIV>
<DIV><BR>oConn. Provider="Micro soft.Jet.OLEDB. 4.0"<BR>oConn.O pen
Server.MapPath( "temp.mdb") </STRONG></FONT></DIV>
<DIV><STRONG><F ONT color=#0000ff size=1></FONT></STRONG>&nbsp;</DIV>
<DIV><FONT face=Arial color=#0000ff size=1><STRONG> oRS.Open "SELECT
EMAIL_ADDRESS FROM TABLE1", oConn, adOpenStatic,
adLockReadOnly< BR>Randomize()< BR>randNum = CInt((oRS.Recor dCount - 1) *
Rnd)</STRONG></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff
size=1><STRONG> <BR>Response.Wr ite("RecordCoun t: " &amp; oRS.RecordCount &amp;
"&lt;br&gt;&lt; br&gt;")</STRONG></FONT></DIV>
<DIV><STRONG><F ONT color=#0000ff size=1></FONT></STRONG>&nbsp;</DIV>
<DIV><FONT face=Arial color=#0000ff size=1><STRONG> oRS.Move
randNum<BR>Resp onse.Write oRS("EMAIL_ADDR ESS")</STRONG></FONT></DIV>
<DIV><STRONG><F ONT color=#0000ff size=1></FONT></STRONG>&nbsp;</DIV>
<DIV><FONT face=Arial color=#0000ff
size=1><STRONG> oRS.close<BR>oC onn.close<BR>Se t oConn = nothing<BR>Set oRS =
nothing<BR>%&gt ;</STRONG></FONT></DIV>
<DIV><STRONG><F ONT color=#0000ff size=1></FONT></STRONG>&nbsp;</DIV>
<DIV>so i wanted to know if the "TOP 1" method could be done in access, and
maybe see an example based on what i have here.</DIV></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>"Aaron Bertrand [SQL Server MVP]" &lt;</FONT><A
href="mailto:te *****@dnartreb. noraa"><FONT face=Arial
size=2>te*****@ dnartreb.noraa</FONT></A><FONT face=Arial size=2>&gt; wrote in
message </FONT><A href="news:OD** ***********@TK2 MSFTNGP06.phx.g bl"><FONT
face=Arial size=2>news:OD* ************@TK 2MSFTNGP06.phx. gbl</FONT></A><FONT
face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>&gt; What,
this?<BR>&gt; <BR>&gt;&gt;&gt ; randNum = (CInt((recordco unt-1) * Rnd) +
1)<BR>&gt; <BR>&gt; Again, you need to show us what you are doing with randNum
before we can <BR>&gt; comment.<BR>&gt ; <BR>&gt; But yes, as we've already
suggested, there are issues.<BR>&gt; <BR>&gt; My guess is you are going to
say<BR>&gt; <BR>&gt; sql = "SELECT EmailAddress FROM Table1 WHERE PK = " &amp;
RandNum<BR>&gt; <BR>&gt; And like I already commented, this won't work
reliably.&nbsp; Never mind the <BR>&gt; unnecessary roundtrip to count the
number of rows in the table.<BR>&gt; <BR>&gt; I think you need to stop
theorizing code and deal with this when you can <BR>&gt; actually test it
against a real database and understand the differences and <BR>&gt; what we are
talking about.&nbsp; Until then it seems you are hellbent on just <BR>&gt; doing
it your way and ignoring our advice.<BR>&gt; <BR>&gt; A<BR>&gt; <BR>&gt;
<BR>&gt; <BR>&gt; <BR>&gt; "Jimmy" &lt;</FONT><A href="mailto:j@ j.j"><FONT
face=Arial size=2>j@j.j</FONT></A><FONT face=Arial size=2>&gt; wrote in message
<BR>&gt; </FONT><A href="news:Od** **************@ TK2MSFTNGP06.ph x.gbl"><FONT
face=Arial size=2>news:Od* *************** @TK2MSFTNGP06.p hx.gbl</FONT></A><FONT
face=Arial size=2>...<BR>& gt;&gt; thank you.<BR>&gt;&gt ; im currently testing
with an access db. do you see any issues with my <BR>&gt;&gt; previous random
record generating code?<BR>&gt; <BR>&gt;</FONT></BODY></HTML>

------=_NextPart_000_ 0049_01C6D359.C 5231A80--