cant get multiple random records (can get 1 ok)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe Reynolds - 26 Sep 2006 14:37 GMT

hi everyone. i have the following code that pulls a random record from an
access database:

Randomize()
randNum = CInt((oRS.RecordCount - 1) * Rnd)
oRS.Move randNum
Response.Write(oRS("FIELD1") )

this works fine. it opens a recordset, gets a count of numrecords, then
moves to a random record within that count.
if i put this in a loop it fails

For i = 1 To 3
Randomize()
randNum = CInt((oRS.RecordCount - 1) * Rnd)
oRS.Move randNum
Response.Write(oRS("FIELD1"))
Next

this might load on the first try, but a refresh usually produces the error
"either eof or bof is true etc..."
how is this possibly happening?

Reply to this Message

Bob Barrows [MVP] - 26 Sep 2006 14:47 GMT

> hi everyone. i have the following code that pulls a random record
> from an access database:
[quoted text clipped - 18 lines]
> error "either eof or bof is true etc..."
> how is this possibly happening?

I can't say without seeing more of the code (what cursor type are you
using?).

Here is a better way to get a random record. It requires the existence
of an autonumber field in your table:

http://www.adopenstatic.com/faq/randomrecord.asp

Signature

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.

Reply to this Message

Joe Reynolds - 26 Sep 2006 14:56 GMT

heres the page im testing with:

Dim oConn, oRS, randNum, strSQL, strDate, strComments, intCount

Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("ADODB.recordset")

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

strSQL = "SELECT DATE_TEXT, COMMENTS_TEXT FROM TBL_COMMENTS"

oRS.Open strSQL, oConn, adOpenStatic, adLockReadOnly

For intCount = 1 To 2
Randomize()
randNum = CInt((oRS.RecordCount - 1) * Rnd)

oRS.Move randNum

Response.Write("recordcount: " & oRS.RecordCount & " ")
Response.Write("random number: " & randNum & " ")
Response.Write(oRS("DATE_TEXT") & " ")
Response.Write(oRS("COMMENTS_TEXT") & " ")
Response.Write(" ")
Next

oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing

>> hi everyone. i have the following code that pulls a random record
>> from an access database:
[quoted text clipped - 26 lines]
>
> http://www.adopenstatic.com/faq/randomrecord.asp

Reply to this Message

Joe Reynolds - 26 Sep 2006 16:02 GMT

hey thanks.
im using that link you provided in conjunction with an AJAX function and its
working great.
although i would love to know why my original code wasnt working.

>> hi everyone. i have the following code that pulls a random record
>> from an access database:
[quoted text clipped - 26 lines]
>
> http://www.adopenstatic.com/faq/randomrecord.asp

Reply to this Message

Bob Barrows [MVP] - 26 Sep 2006 16:25 GMT

> hey thanks.
> im using that link you provided in conjunction with an AJAX function
> and its working great.
> although i would love to know why my original code wasnt working.

Frankly, I really don't care to spend the time to look at it. It's a
grossly inefficient way to accomplish the task.

Signature

Reply to this Message

Daniel Crichton - 26 Sep 2006 17:05 GMT

Joe wrote on Tue, 26 Sep 2006 11:02:56 -0400:

> hey thanks.
> im using that link you provided in conjunction with an AJAX function and
> its working great.
> although i would love to know why my original code wasnt working.

The Move method doesn't move to a particular row, it moves forward that many
rows. So, here's a simple example

rows = 10
rand = 6

.Move 6 will move to row 6

rand = 5

.Move 5 will move to row 11 (as you're already on row 6), hence the error
when you read the row details (as you're now past the last row, so EOF is
true)

You could have used the AbsolutePosition property if you didn't use the
default ForwardOnly cursor to go to a particular row (which is zero based so
you'd have to remember to subtract 1 from your random number), or use
MoveFirst between each Move method call to get back to the start (again
needing a non-ForwardOnly cursor), but it's still a poor way to pick random
rows from a recordset.

Dan

Reply to this Message

Joe Reynolds - 26 Sep 2006 17:25 GMT

thank you for explaining that. it makes perfect sense.

> Joe wrote on Tue, 26 Sep 2006 11:02:56 -0400:
>
[quoted text clipped - 25 lines]
>
> Dan

Reply to this Message

Webmaster Forum / ASP / Database Access / September 2006

cant get multiple random records (can get 1 ok)