By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,454 Members | 3,191 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,454 IT Pros & Developers. It's quick & easy.

how to choose a random record from a database

P: n/a
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?
Sep 8 '06 #1
Share this Question
Share on Google+
26 Replies


P: n/a
http://databases.aspfaq.com/database...om-record.html

"Jimmy" <j@j.jwrote in message
news:Or**************@TK2MSFTNGP04.phx.gbl...
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?


Sep 8 '06 #2

P: n/a
thanks... few questions though. here is the code:

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

set conn = CreateObject("ADODB.Connection")

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

set rs = conn.execute(sql)

response.write rs(0)

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

what will randNum equal? a number between what and what?
in the SQL statement, what is "cols"? and "r"?

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in message news:OD**************@TK2MSFTNGP02.phx.gbl...
http://databases.aspfaq.com/database...om-record.html

"Jimmy" <j@j.jwrote in message
news:Or**************@TK2MSFTNGP04.phx.gbl...
>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?

Sep 8 '06 #3

P: n/a
what will randNum equal? a number between what and what?

Hit refresh a few times:

<%
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
response.write (randNum)
%>
In the SQL statement, what is "cols"? and "r"?
cols is your column list.

r is just an alias for the random number, and it should be in the result set
because it use to seed the random number in the query and is used in the
order by. You could do it all in the order by but some queries will not
allow you to order by something that is not in the select list.


"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in message
news:OD**************@TK2MSFTNGP02.phx.gbl...
http://databases.aspfaq.com/database...om-record.html

"Jimmy" <j@j.jwrote in message
news:Or**************@TK2MSFTNGP04.phx.gbl...
>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?



Sep 8 '06 #4

P: n/a
ok so

randNum = (CInt(9 * Rnd) + 1)

gives me 1-10, correct?
so to generate a random record from my database i would use:

randNum = (CInt((recordcount-1) * Rnd) + 1)
is that right?

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in message
news:uK**************@TK2MSFTNGP04.phx.gbl...
>what will randNum equal? a number between what and what?

Hit refresh a few times:

<%
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
response.write (randNum)
%>
>In the SQL statement, what is "cols"? and "r"?

cols is your column list.

r is just an alias for the random number, and it should be in the result
set because it use to seed the random number in the query and is used in
the order by. You could do it all in the order by but some queries will
not allow you to order by something that is not in the select list.


"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in
message news:OD**************@TK2MSFTNGP02.phx.gbl...
>http://databases.aspfaq.com/database...om-record.html

"Jimmy" <j@j.jwrote in message
news:Or**************@TK2MSFTNGP04.phx.gbl...
>>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?




Sep 8 '06 #5

P: n/a
Well, you have the same ability to try that code as I do. It really depends
on what you are then going to do with RandNum. What I was trying to
demonstrate is that you don't need to know the recordcount beforehand
(saving one query).
"Jimmy" <j@j.jwrote in message
news:O8**************@TK2MSFTNGP02.phx.gbl...
ok so

randNum = (CInt(9 * Rnd) + 1)

gives me 1-10, correct?
so to generate a random record from my database i would use:

randNum = (CInt((recordcount-1) * Rnd) + 1)
is that right?

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in
message news:uK**************@TK2MSFTNGP04.phx.gbl...
>>what will randNum equal? a number between what and what?

Hit refresh a few times:

<%
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
response.write (randNum)
%>
>>In the SQL statement, what is "cols"? and "r"?

cols is your column list.

r is just an alias for the random number, and it should be in the result
set because it use to seed the random number in the query and is used in
the order by. You could do it all in the order by but some queries will
not allow you to order by something that is not in the select list.


"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in
message news:OD**************@TK2MSFTNGP02.phx.gbl...
>>http://databases.aspfaq.com/database...om-record.html

"Jimmy" <j@j.jwrote in message
news:Or**************@TK2MSFTNGP04.phx.gbl...
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?




Sep 8 '06 #6

P: n/a
if i dont know the recordhand, how will i seed the random number generator?

obviously if i have 100 records i cant have a number generated thats over
100

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in message
news:uq**************@TK2MSFTNGP04.phx.gbl...
Well, you have the same ability to try that code as I do. It really
depends on what you are then going to do with RandNum. What I was trying
to demonstrate is that you don't need to know the recordcount beforehand
(saving one query).
"Jimmy" <j@j.jwrote in message
news:O8**************@TK2MSFTNGP02.phx.gbl...
>ok so

randNum = (CInt(9 * Rnd) + 1)

gives me 1-10, correct?
so to generate a random record from my database i would use:

randNum = (CInt((recordcount-1) * Rnd) + 1)
is that right?

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in
message news:uK**************@TK2MSFTNGP04.phx.gbl...
>>>what will randNum equal? a number between what and what?

Hit refresh a few times:

<%
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
response.write (randNum)
%>

In the SQL statement, what is "cols"? and "r"?

cols is your column list.

r is just an alias for the random number, and it should be in the result
set because it use to seed the random number in the query and is used in
the order by. You could do it all in the order by but some queries will
not allow you to order by something that is not in the select list.


"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in
message news:OD**************@TK2MSFTNGP02.phx.gbl...
http://databases.aspfaq.com/database...om-record.html

"Jimmy" <j@j.jwrote in message
news:Or**************@TK2MSFTNGP04.phx.gbl...
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?
>
>




Sep 8 '06 #7

P: n/a
i dont have a database ready yet to TRY it. so instead im trying to
UNDERSTAND it first.
my confusion is in the fact that im thinking if i have 100 records, i need
to generate a random number between 1 and 100 so that i can open THAT random
record. do you see what im trying to do?

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in message
news:uq**************@TK2MSFTNGP04.phx.gbl...
Did you TRY the code sample that you had questions about? The random
number is not important, it is merely used to seed the random number in
the query that gets *1* row.

I suggest you try it out.


"Jimmy" <j@j.jwrote in message
news:e4**************@TK2MSFTNGP05.phx.gbl...
>if i dont know the recordhand, how will i seed the random number
generator?

obviously if i have 100 records i cant have a number generated thats over
100


Sep 8 '06 #8

P: n/a
>i dont have a database ready yet to TRY it. so instead im trying to
>UNDERSTAND it first.
my confusion is in the fact that im thinking if i have 100 records, i need
to generate a random number between 1 and 100 so that i can open THAT
random record. do you see what im trying to do?
Sort of.

The problem is, if you have a number between 1 and 100, and you are trying
to get the row where [TableName]ID = that number, you're going to be
disappointed when you don't have a perfectly sequential set of [TableName]ID
values (even if the total number is exactly 100). Because of deletes,
rollbacks, and failures, you are likely going to have gaps in your
[TableName]ID. This is why the top solution is better than a solution that
relies on mapping recordcount to actual data.

A
Sep 8 '06 #9

P: n/a
Does either the pubs or Northwind sample database come with SQLExpress?
If so, apply the example query to a table in one of those databases.

Let's look at the query:
sql = "SELECT TOP 1

TOP 1 tells it to return only the first record in the resultset

cols," & _

cols is meant to be a list of the columns you wish the query to return

"r = Rnd(" & randNum & ")" & _

This assigns a random number to each record in the resultset (prior to
TOP being applied)

"FROM TableName " & _
"ORDER BY r"

Without the "TOP 1", you would have a resultset containing the columns
specified by "cols" as well as a calculated column (called r) containing
a random number generated by the Rnd function, ordered by the random
number assigned to each record. The "TOP 1" returns the first one. So
you don't need to know how many records are in your table.

Jimmy wrote:
i dont have a database ready yet to TRY it. so instead im trying to
UNDERSTAND it first.
my confusion is in the fact that im thinking if i have 100 records, i
need to generate a random number between 1 and 100 so that i can open
THAT random record. do you see what im trying to do?

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in
message news:uq**************@TK2MSFTNGP04.phx.gbl...
>Did you TRY the code sample that you had questions about? The random
number is not important, it is merely used to seed the random number
in the query that gets *1* row.

I suggest you try it out.


"Jimmy" <j@j.jwrote in message
news:e4**************@TK2MSFTNGP05.phx.gbl...
>>if i dont know the recordhand, how will i seed the random number
generator?

obviously if i have 100 records i cant have a number generated
thats over 100
--
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.
Sep 8 '06 #10

P: n/a
this is what i have working so far... tell me what you think:

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

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?

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in message news:OD**************@TK2MSFTNGP03.phx.gbl...
i dont have a database ready yet to TRY it. so instead im trying to
UNDERSTAND it first.
my confusion is in the fact that im thinking if i have 100 records, i need
to generate a random number between 1 and 100 so that i can open THAT
random record. do you see what im trying to do?
Sort of.

The problem is, if you have a number between 1 and 100, and you are trying
to get the row where [TableName]ID = that number, you're going to be
disappointed when you don't have a perfectly sequential set of [TableName]ID
values (even if the total number is exactly 100). Because of deletes,
rollbacks, and failures, you are likely going to have gaps in your
[TableName]ID. This is why the top solution is better than a solution that
relies on mapping recordcount to actual data.

A

Sep 8 '06 #11

P: n/a
Jimmy wrote:
this is what i have working so far... tell me what you think:
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
>
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.
Sep 8 '06 #12

P: n/a
thank you.
im currently testing with an access db. do you see any issues with my
previous random record generating code?
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
Does either the pubs or Northwind sample database come with SQLExpress?
If so, apply the example query to a table in one of those databases.

Let's look at the query:
sql = "SELECT TOP 1

TOP 1 tells it to return only the first record in the resultset

cols," & _

cols is meant to be a list of the columns you wish the query to return

"r = Rnd(" & randNum & ")" & _

This assigns a random number to each record in the resultset (prior to
TOP being applied)

"FROM TableName " & _
"ORDER BY r"

Without the "TOP 1", you would have a resultset containing the columns
specified by "cols" as well as a calculated column (called r) containing
a random number generated by the Rnd function, ordered by the random
number assigned to each record. The "TOP 1" returns the first one. So
you don't need to know how many records are in your table.

Jimmy wrote:
>i dont have a database ready yet to TRY it. so instead im trying to
UNDERSTAND it first.
my confusion is in the fact that im thinking if i have 100 records, i
need to generate a random number between 1 and 100 so that i can open
THAT random record. do you see what im trying to do?

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in
message news:uq**************@TK2MSFTNGP04.phx.gbl...
>>Did you TRY the code sample that you had questions about? The random
number is not important, it is merely used to seed the random number
in the query that gets *1* row.

I suggest you try it out.


"Jimmy" <j@j.jwrote in message
news:e4**************@TK2MSFTNGP05.phx.gbl...
if i dont know the recordhand, how will i seed the random number
generator?

obviously if i have 100 records i cant have a number generated
thats over 100

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


Sep 8 '06 #13

P: n/a
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******@NOyahoo.SPAMcomwrote in message
news:%2****************@TK2MSFTNGP05.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,
adLockReadOnly
Randomize()
randNum = CInt((oRS.RecordCount - 1) * Rnd)

Response.Write("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.


Sep 8 '06 #14

P: n/a
Yes. Grossly inefficient. I would never do it that way. Let the database
do the job it can do so much more efficiently than any recordset/cursor
code you or I could write.

Jimmy wrote:
thank you.
im currently testing with an access db. do you see any issues with my
previous random record generating code?
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...
>Does either the pubs or Northwind sample database come with
SQLExpress? If so, apply the example query to a table in one of
those databases.

Let's look at the query:
sql = "SELECT TOP 1

TOP 1 tells it to return only the first record in the resultset

cols," & _

cols is meant to be a list of the columns you wish the query to
return

"r = Rnd(" & randNum & ")" & _

This assigns a random number to each record in the resultset (prior
to TOP being applied)

"FROM TableName " & _
"ORDER BY r"

Without the "TOP 1", you would have a resultset containing the
columns specified by "cols" as well as a calculated column (called
r) containing a random number generated by the Rnd function, ordered
by the random number assigned to each record. The "TOP 1" returns
the first one. So you don't need to know how many records are in
your table.

Jimmy wrote:
>>i dont have a database ready yet to TRY it. so instead im trying to
UNDERSTAND it first.
my confusion is in the fact that im thinking if i have 100 records,
i need to generate a random number between 1 and 100 so that i can
open THAT random record. do you see what im trying to do?

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in
message news:uq**************@TK2MSFTNGP04.phx.gbl...
Did you TRY the code sample that you had questions about? The
random number is not important, it is merely used to seed the
random number in the query that gets *1* row.

I suggest you try it out.


"Jimmy" <j@j.jwrote in message
news:e4**************@TK2MSFTNGP05.phx.gbl...
if i dont know the recordhand, how will i seed the random number
generator?
>
obviously if i have 100 records i cant have a number generated
thats over 100

--
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.
Sep 8 '06 #15

P: n/a
>
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
Sep 8 '06 #16

P: n/a
What, this?
>randNum = (CInt((recordcount-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" <j@j.jwrote in message
news:Od****************@TK2MSFTNGP06.phx.gbl...
thank you.
im currently testing with an access db. do you see any issues with my
previous random record generating code?

Sep 8 '06 #17

P: n/a
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*****@dnartreb.noraawrote in message
news:ug**************@TK2MSFTNGP06.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

Sep 8 '06 #18

P: n/a
ie, can i open the DB to get a record count without SELECTing anything?

For the 15th time, YOU DON'T NEED A RECORD COUNT!
Sep 8 '06 #19

P: n/a
You don't NEED oRs.Move. Using "TOP 1" you will only get a single record
back.

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******@NOyahoo.SPAMcomwrote in message
news:%2****************@TK2MSFTNGP05.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,
adLockReadOnly
Randomize()
randNum = CInt((oRS.RecordCount - 1) * Rnd)

Response.Write("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.
Sep 8 '06 #20

P: n/a
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.

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in message news:OD*************@TK2MSFTNGP06.phx.gbl...
What, this?
>>randNum = (CInt((recordcount-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" <j@j.jwrote in message
news:Od****************@TK2MSFTNGP06.phx.gbl...
>thank you.
im currently testing with an access db. do you see any issues with my
previous random record generating code?
Sep 8 '06 #21

P: n/a
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
Sep 8 '06 #22

P: n/a
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
%>
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******@NOyahoo.SPAMcomwrote in message
news:%2****************@TK2MSFTNGP05.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,
adLockReadOnly
Randomize()
randNum = CInt((oRS.RecordCount - 1) * Rnd)

Response.Write("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.
Sep 8 '06 #23

P: n/a
<shudder>

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

P: n/a
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.
Sep 8 '06 #25

P: n/a
it doesnt like your code.... missing parameter here:

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

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:eA**************@TK2MSFTNGP06.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.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
%>
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******@NOyahoo.SPAMcomwrote in message
news:%2****************@TK2MSFTNGP05.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,
adLockReadOnly
Randomize()
randNum = CInt((oRS.RecordCount - 1) * Rnd)

Response.Write("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.


Sep 8 '06 #26

P: n/a
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.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.

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in message news:OD*************@TK2MSFTNGP06.phx.gbl...
What, this?
>randNum = (CInt((recordcount-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" <j@j.jwrote in message
news:Od****************@TK2MSFTNGP06.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.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>S et
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: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*************@TK2MSFTNGP06.phx.gbl">< FONT
face=Arial size=2>news:OD*************@TK2MSFTNGP06.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((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: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.phx.gbl "><FONT
face=Arial size=2>news:Od****************@TK2MSFTNGP06.phx.gb l</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.C5231A80--
Sep 8 '06 #27

This discussion thread is closed

Replies have been disabled for this discussion.