Selecting Random Records

[John Fairhurst]

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 & "<br>"

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 & "<br>"
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
jo**@johnsbooks.co.uk

[Mike Brind]

"John Fairhurst" <jo**@johnsbooks.co.ukwrote in message
news:Ug*******************@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 & "<br>"

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 & "<br>"
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

[John Fairhurst]

"Mike Brind" <pa*******@hotmail.comwrote in message
news:eT**************@TK2MSFTNGP03.phx.gbl...

>
"John Fairhurst" <jo**@johnsbooks.co.ukwrote in message
news:Ug*******************@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 & "<br>"

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 & "<br>"
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
jo**@johnsbooks.co.uk

[Mike Brind]

"John Fairhurst" <jo**@johnsbooks.co.ukwrote in message
news:S4*******************@newsfe3-gui.ntli.net...

"Mike Brind" <pa*******@hotmail.comwrote in message
news:eT**************@TK2MSFTNGP03.phx.gbl...

>>
"John Fairhurst" <jo**@johnsbooks.co.ukwrote in message
news:Ug*******************@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 & "<br>"

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 & "<br>"
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