how to choose a random record from a database

[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?

 

[Jimmy]

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.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("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.RecordCount - 1) * Rnd)

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

oRS.Move randNum
Response.Write oRS("EMAIL_ADDRESS")

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.

 

[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.CreateObject("ADODB.Connection")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")
Set oRS=oconn.Execute(sql, , 1)

Response.Write oRS("EMAIL_ADDRESS")

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

 

[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("ADODB.Connection")
' create connection string here

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

set rs = conn.execute(sql)

response.write rs("EmailAddress")

' ...
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

 

[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]]

<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?

 

[Jimmy]

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

Set oRS=oconn.Execute(sql, , 1)

 

[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

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.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("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.RecordCount - 1) * Rnd)

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

oRS.Move randNum
Response.Write oRS("EMAIL_ADDRESS")

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.

What, this?


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

------=_NextPart_000_0049_01C6D359.C5231A80
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.CreateObject("ADODB.Connection")<BR>Set
oRS=Server.CreateObject("ADODB.recordset")</DIV>
<DIV><BR>oConn.Provider="Microsoft.Jet.OLEDB.4.0"<BR>oConn.Open
Server.MapPath("temp.mdb")</STRONG></FONT></DIV>
<DIV><STRONG><FONT 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.RecordCount - 1) *
Rnd)</STRONG></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff
size=1><STRONG><BR>Response.Write("RecordCount: " &amp; oRS.RecordCount &amp;
"&lt;br&gt;&lt;br&gt;")</STRONG></FONT></DIV>
<DIV><STRONG><FONT color=#0000ff size=1></FONT></STRONG>&nbsp;</DIV>
<DIV><FONT face=Arial color=#0000ff size=1><STRONG>oRS.Move
randNum<BR>Response.Write oRS("EMAIL_ADDRESS")</STRONG></FONT></DIV>
<DIV><STRONG><FONT color=#0000ff size=1></FONT></STRONG>&nbsp;</DIV>
<DIV><FONT face=Arial color=#0000ff
size=1><STRONG>oRS.close<BR>oConn.close<BR>Set oConn = nothing<BR>Set oRS =
nothing<BR>%&gt;</STRONG></FONT></DIV>
<DIV><STRONG><FONT 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:[email protected]"><FONT face=Arial
size=2>[email protected]</FONT></A><FONT face=Arial size=2>&gt; wrote in
message </FONT><A href="face=Arial size=2>face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>&gt; What,
this?<BR>&gt; <BR>&gt;&gt;&gt; randNum = (CInt((recordcount-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:[email protected]"><FONT
face=Arial size=2>[email protected]</FONT></A><FONT face=Arial size=2>&gt; wrote in message
<BR>&gt; </FONT><A href="face=Arial size=2>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.C5231A80--