cant get multiple random records (can get 1 ok)

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?

Re: cant get multiple random records (can get 1 ok)

Joe Reynolds wrote:
> 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?

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

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

Re: cant get multiple random records (can get 1 ok)

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








"Bob Barrows [MVP]" wrote in message
news:ezNZFJX4GHA.3452@TK2MSFTNGP05.phx.gbl...
> Joe Reynolds wrote:
>> 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?
>
> 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
>
> --
> 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.
>
>

Re: cant get multiple random records (can get 1 ok)

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.




"Bob Barrows [MVP]" wrote in message
news:ezNZFJX4GHA.3452@TK2MSFTNGP05.phx.gbl...
> Joe Reynolds wrote:
>> 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?
>
> 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
>
> --
> 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.
>
>

Re: cant get multiple random records (can get 1 ok)

Joe Reynolds wrote:
> 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.
--
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.

Re: cant get multiple random records (can get 1 ok)

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

Re: cant get multiple random records (can get 1 ok)

thank you for explaining that. it makes perfect sense.


"Daniel Crichton" wrote in message
news:eyABZWY4GHA.2596@TK2MSFTNGP06.phx.gbl...
> 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
>