473,287 Members | 1,960 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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 & "<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
Jan 27 '07 #1
3 3846

"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
Jan 27 '07 #2
"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
Jan 27 '07 #3

"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
Jan 27 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Keith Griffiths | last post by:
I'm trying to do a search under a set criteria followed by a selection of random entries meeting this criteria. But I don't seem to be able to achieve this. The idea being to search on say...
3
by: Wim Roffil | last post by:
Hi, When I do a select with a limit I get always the same records. Usually this is the desidered effect. But now I want to do a random select. So every time I do a select I should get a...
4
by: yf | last post by:
A KB article "http://support.microsoft.com/default.aspx?scid=kb;en-us;209599" tells that the maximum number of records that a table may hold if the PRIMARY key data type is set to AUTONUMBER is...
2
by: IceCube | last post by:
Hello, I would like to select/filter at random 30 records out of an Access-table of 1500 records. I know the option "Top" which gives me the possibility to see the 30 first records of the table...
4
by: Greg Strong | last post by:
Hello All, Is it possible to create multiple random numbers in a query where there are numerous records? I've created a custom function. When I use it in a query it creates the same random...
1
by: Ramesh | last post by:
hi, I am selecting fields from three table for manupulating data and i want to display total number of records selected. But i am always getting -1 value, eventhough 1000 of records are selected....
2
by: VB Programmer | last post by:
I am interested to hear your suggestions on this... I have a table full of survey questions. The questions are individually classified as priority 1, 2 or 3. (Priority 1 means the question...
8
by: Kari Lavikka | last post by:
Hi! I have to select a random row from a table where primary key isn't continuous (some rows have been deleted). Postgres just seems to do something strange with my method. -- -- Use the...
48
by: phillip.s.powell | last post by:
MySQL 3.23.58 - 4.0.17 (yep, several database server instances, don't ask) I have database Spring with table Students I have database Summer with table Students I am tasked to produce a...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.