Selecting Random Records

Hi,

The following code should select the specified number of records randomly
from the database

<%
....

query = "SELECT [Titles.Index] FROM [Titles]"
Set RS = Server.CreateObject("ADODB.Recordset")

' Response.Write query
RS.Open query, objConn, 1, 2, 1

Randomize Timer ' Seed the rnd function
Response.Write RS.RecordCount & "
"

For c = 1 To numVals

intRnd = 0 ' Set to default value to allow the Do Until to check against
a value
Do
intRnd = (Int(Rnd * RS.RecordCount)) ' Pick a number between 0 and
Number of records - 1
Response.Write c & " " & intRnd & " " & retString & " " &
DontMatchWith & "
"
RS.Move intRnd ' Goto that record
Loop While InStr(DontMatchWith, Right("000000" & intRnd, 6))

retString = retString & Right("000000" & intRnd, 6)
DontMatchWith = DontMatchWith & Right("000000" & intRnd, 6)

Next

....

%>

The loop works a random number of times (somewhat appropriately :-)) before
crashing with the following error:

ADODB.Recordset error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested
operation requires a current record.

/Includes/QuizFunx.asp, line 31

where line 31 is RS.Move intRnd ' Goto that record

I've compacted the database and it does seem to be working as I wish right
to the point where it crashes.

The output of this function is a string that will be used to pull out random
titles from the table.

Can you suggest a solution to the problem, or another way of generating the
random numbers

TiA
--

John Fairhurst
http://www.johnsbooks.co.uk
john@johnsbooks.co.uk

Re: Selecting Random Records

"John Fairhurst" wrote in message
news:UgFuh.81915$HV6.48388@newsfe1-gui.ntli.net...
> Hi,
>
> The following code should select the specified number of records randomly
> from the database
>
> <%
> ...
>
> query = "SELECT [Titles.Index] FROM [Titles]"
> Set RS = Server.CreateObject("ADODB.Recordset")
>
> ' Response.Write query
> RS.Open query, objConn, 1, 2, 1
>
> Randomize Timer ' Seed the rnd function
> Response.Write RS.RecordCount & "
"
>
> For c = 1 To numVals
>
> intRnd = 0 ' Set to default value to allow the Do Until to check
> against a value
> Do
> intRnd = (Int(Rnd * RS.RecordCount)) ' Pick a number between 0 and
> Number of records - 1
> Response.Write c & " " & intRnd & " " & retString & " " &
> DontMatchWith & "
"
> RS.Move intRnd ' Goto that record
> Loop While InStr(DontMatchWith, Right("000000" & intRnd, 6))
>
> retString = retString & Right("000000" & intRnd, 6)
> DontMatchWith = DontMatchWith & Right("000000" & intRnd, 6)
>
> Next
>
> ...
>
> %>
>
> The loop works a random number of times (somewhat appropriately :-))
> before crashing with the following error:
>
> ADODB.Recordset error '800a0bcd'
>
> Either BOF or EOF is True, or the current record has been deleted.
> Requested operation requires a current record.
>
> /Includes/QuizFunx.asp, line 31
>
> where line 31 is RS.Move intRnd ' Goto that record
>
> I've compacted the database and it does seem to be working as I wish right
> to the point where it crashes.
>
> The output of this function is a string that will be used to pull out
> random titles from the table.
>
> Can you suggest a solution to the problem, or another way of generating
> the random numbers
>

For Access or SQL Server, look here:
http://www.adopenstatic.com/faq/randomrecord.asp

It's useful to mention the type and version of the database you are using
when asking a db related question.

--
Mike Brind

Re: Selecting Random Records

"Mike Brind" wrote in message
news:eT2eoWgQHHA.1036@TK2MSFTNGP03.phx.gbl...
>
> "John Fairhurst" wrote in message
> news:UgFuh.81915$HV6.48388@newsfe1-gui.ntli.net...
>> Hi,
>>
>> The following code should select the specified number of records randomly
>> from the database
>>
>> <%
>> ...
>>
>> query = "SELECT [Titles.Index] FROM [Titles]"
>> Set RS = Server.CreateObject("ADODB.Recordset")
>>
>> ' Response.Write query
>> RS.Open query, objConn, 1, 2, 1
>>
>> Randomize Timer ' Seed the rnd function
>> Response.Write RS.RecordCount & "
"
>>
>> For c = 1 To numVals
>>
>> intRnd = 0 ' Set to default value to allow the Do Until to check
>> against a value
>> Do
>> intRnd = (Int(Rnd * RS.RecordCount)) ' Pick a number between 0 and
>> Number of records - 1
>> Response.Write c & " " & intRnd & " " & retString & " " &
>> DontMatchWith & "
"
>> RS.Move intRnd ' Goto that record
>> Loop While InStr(DontMatchWith, Right("000000" & intRnd, 6))
>>
>> retString = retString & Right("000000" & intRnd, 6)
>> DontMatchWith = DontMatchWith & Right("000000" & intRnd, 6)
>>
>> Next
>>
>> ...
>>
>> %>
>>
>> The loop works a random number of times (somewhat appropriately :-))
>> before crashing with the following error:
>>
>> ADODB.Recordset error '800a0bcd'
>>
>> Either BOF or EOF is True, or the current record has been deleted.
>> Requested operation requires a current record.
>>
>> /Includes/QuizFunx.asp, line 31
>>
>> where line 31 is RS.Move intRnd ' Goto that record
>>
>> I've compacted the database and it does seem to be working as I wish
>> right to the point where it crashes.
>>
>> The output of this function is a string that will be used to pull out
>> random titles from the table.
>>
>> Can you suggest a solution to the problem, or another way of generating
>> the random numbers
>>
>
> For Access or SQL Server, look here:
> http://www.adopenstatic.com/faq/randomrecord.asp
>
> It's useful to mention the type and version of the database you are using
> when asking a db related question.
>
> --
> Mike Brind
>

It's an Access database.

Thanks for the link but I think I've sorted it myself eventually.

The problem with my code was the line with
RS.Move intRnd
which works, but moves the record pointer intRnd records forward from the
current record, eventually pushing it to a record number that doesn't
actually exist. What I had to do was add in an additional property to the
Move method so it looks like
RS.Move intRnd, acBookmarkFirst
so the current record is reset to the first record before the move command.

--

John Fairhurst
http://www.johnsbooks.co.uk
john@johnsbooks.co.uk

Re: Selecting Random Records

"John Fairhurst" wrote in message
news:S4Huh.81214$z01.26046@newsfe3-gui.ntli.net...
> "Mike Brind" wrote in message
> news:eT2eoWgQHHA.1036@TK2MSFTNGP03.phx.gbl...
>>
>> "John Fairhurst" wrote in message
>> news:UgFuh.81915$HV6.48388@newsfe1-gui.ntli.net...
>>> Hi,
>>>
>>> The following code should select the specified number of records
>>> randomly from the database
>>>
>>> <%
>>> ...
>>>
>>> query = "SELECT [Titles.Index] FROM [Titles]"
>>> Set RS = Server.CreateObject("ADODB.Recordset")
>>>
>>> ' Response.Write query
>>> RS.Open query, objConn, 1, 2, 1
>>>
>>> Randomize Timer ' Seed the rnd function
>>> Response.Write RS.RecordCount & "
"
>>>
>>> For c = 1 To numVals
>>>
>>> intRnd = 0 ' Set to default value to allow the Do Until to check
>>> against a value
>>> Do
>>> intRnd = (Int(Rnd * RS.RecordCount)) ' Pick a number between 0 and
>>> Number of records - 1
>>> Response.Write c & " " & intRnd & " " & retString & " " &
>>> DontMatchWith & "
"
>>> RS.Move intRnd ' Goto that record
>>> Loop While InStr(DontMatchWith, Right("000000" & intRnd, 6))
>>>
>>> retString = retString & Right("000000" & intRnd, 6)
>>> DontMatchWith = DontMatchWith & Right("000000" & intRnd, 6)
>>>
>>> Next
>>>
>>> ...
>>>
>>> %>
>>>
>>> The loop works a random number of times (somewhat appropriately :-))
>>> before crashing with the following error:
>>>
>>> ADODB.Recordset error '800a0bcd'
>>>
>>> Either BOF or EOF is True, or the current record has been deleted.
>>> Requested operation requires a current record.
>>>
>>> /Includes/QuizFunx.asp, line 31
>>>
>>> where line 31 is RS.Move intRnd ' Goto that record
>>>
>>> I've compacted the database and it does seem to be working as I wish
>>> right to the point where it crashes.
>>>
>>> The output of this function is a string that will be used to pull out
>>> random titles from the table.
>>>
>>> Can you suggest a solution to the problem, or another way of generating
>>> the random numbers
>>>
>>
>> For Access or SQL Server, look here:
>> http://www.adopenstatic.com/faq/randomrecord.asp
>>
>> It's useful to mention the type and version of the database you are using
>> when asking a db related question.
>>
>> --
>> Mike Brind
>>
>
> It's an Access database.
>
> Thanks for the link but I think I've sorted it myself eventually.
>
> The problem with my code was the line with
> RS.Move intRnd
> which works, but moves the record pointer intRnd records forward from the
> current record, eventually pushing it to a record number that doesn't
> actually exist. What I had to do was add in an additional property to the
> Move method so it looks like
> RS.Move intRnd, acBookmarkFirst
> so the current record is reset to the first record before the move
> command.
>

You might still like to have a look at the link I offered. Your current
method has to make 2 calls to the database - one to get the title field for
every row in your database, and another to get just the records your
computations result in. The method I recommend makes just one call to the
database and returns just the records you are ultimately interested in
using. Ideally, your page should contain the barest minimum interactions
with the database as possible, and for as brief a point of time as possible.

--
Mike Brind