naivad
asked on
Returning Random Random Records via Access to ASP
Hello All,
I have read many posts on retrieving random records and I do not have a problem creating a query that will return a set number of random records. My problem is that when I call that query via ASP, I get the same "random" records in the same "random" order every time the query runs. If I close the web page and rerun the query I get a new resultset in Access, but I go back to the web page and I still get the same random order. Anyone seen this before?
I know I can just bring in the entire recordset and write a sub that will give me a few random records, but that isn't what I am looking for.
Here are things I have tried:
Disabling recordset cache on the server side script
Writing the SQL statement instead of calling a query using a server side rnd() following a Randomize() statement
Writing a Delete then an append query that will create a table based on the random records that is executed each time the page loads
Any Ideas?
I have read many posts on retrieving random records and I do not have a problem creating a query that will return a set number of random records. My problem is that when I call that query via ASP, I get the same "random" records in the same "random" order every time the query runs. If I close the web page and rerun the query I get a new resultset in Access, but I go back to the web page and I still get the same random order. Anyone seen this before?
I know I can just bring in the entire recordset and write a sub that will give me a few random records, but that isn't what I am looking for.
Here are things I have tried:
Disabling recordset cache on the server side script
Writing the SQL statement instead of calling a query using a server side rnd() following a Randomize() statement
Writing a Delete then an append query that will create a table based on the random records that is executed each time the page loads
Any Ideas?
ASKER
Unfortunately 1William, this is pretty much what I was using.
I tried using this in ASP
dim adoConn
dim adoRs
dim strSQL
set adoConn = Server.CreateObject("ADODB
set adoRs = Server.CreateObject("ADODB
adoConn.open "DSN=yourDsn"
strSQL = "SELECT TOP 5 * FROM tblQuestions ORDER BY rnd([QuestionID])"; 'the nz function doesn't seem to work in vbscript
adoRs.Open strSQL, adoConn
'this recordset opens with the same 'random' records every time (i.e question 8, then 2, then 4, then 6, then 1).
'if I run this query from access it works great, but if I call it from IIS using an ADO recordset from VBScript it will return the same recordset every time.
I tried using this in ASP
dim adoConn
dim adoRs
dim strSQL
set adoConn = Server.CreateObject("ADODB
set adoRs = Server.CreateObject("ADODB
adoConn.open "DSN=yourDsn"
strSQL = "SELECT TOP 5 * FROM tblQuestions ORDER BY rnd([QuestionID])"; 'the nz function doesn't seem to work in vbscript
adoRs.Open strSQL, adoConn
'this recordset opens with the same 'random' records every time (i.e question 8, then 2, then 4, then 6, then 1).
'if I run this query from access it works great, but if I call it from IIS using an ADO recordset from VBScript it will return the same recordset every time.
Well, you got me stumped. did you post this in the ASP area?
ASKER
Just did, thanks
Already answered elsewhere, if you need help let me know
http://search.experts-exchange.com/questions/20659866/Random-product-details.html
http://search.experts-exchange.com/questions/20659866/Random-product-details.html
Got to initialize the randomizer!
The following example illustrates use of the Randomize statement:
Dim MyValue, Response
Randomize '<---- Initialize random-number generator.
Do Until Response = vbNo
MyValue = Int((6 * Rnd) + 1) ' Generate random value between 1 and 6.
MsgBox MyValue
Response = MsgBox ("Roll again? ", vbYesNo)
Loop
The following example illustrates use of the Randomize statement:
Dim MyValue, Response
Randomize '<---- Initialize random-number generator.
Do Until Response = vbNo
MyValue = Int((6 * Rnd) + 1) ' Generate random value between 1 and 6.
MsgBox MyValue
Response = MsgBox ("Roll again? ", vbYesNo)
Loop
Wow, strange... GaryC123's post was not showing... and I typed mine in taking no more than 2 minutes...
lol gotta stop this
ASKER
Guys, I appreciate the help, but it isn't what I am looking for.
SquareHead...I am not using VBScript's rnd function so there is nothing to initialize (although I did try it) I am using a SQL statement against an access table.
This SQL statement works GREAT in access, it works GOOD from ASP, it just returns the same random recordset every time (not so good).
Gary...In my first post I wrote "I know I can just bring in the entire recordset and write a sub that will give me a few random records, but that isn't what I am looking for. " This seems to be what the post you referred to was doing. I really want access to do the randomizing for me so I can "SELECT TOP 30 * FROM tblQuestions"
Why is it that in Access the query returns a new resultset every time, but calling adoRs.open "qryRandomQuestions", adoConn will give me the same results every time?
SquareHead...I am not using VBScript's rnd function so there is nothing to initialize (although I did try it) I am using a SQL statement against an access table.
This SQL statement works GREAT in access, it works GOOD from ASP, it just returns the same random recordset every time (not so good).
Gary...In my first post I wrote "I know I can just bring in the entire recordset and write a sub that will give me a few random records, but that isn't what I am looking for. " This seems to be what the post you referred to was doing. I really want access to do the randomizing for me so I can "SELECT TOP 30 * FROM tblQuestions"
Why is it that in Access the query returns a new resultset every time, but calling adoRs.open "qryRandomQuestions", adoConn will give me the same results every time?
I think Squarehead is on the right track..........
Try it like this
SELECT *
FROM tblDrivers
ORDER BY Rnd((1000*driverID)*Now())
strSQL = "SELECT TOP 5 * FROM tblQuestions ORDER BY rnd(-(1000*QuestionID)*Now
SELECT *
FROM tblDrivers
ORDER BY Rnd((1000*driverID)*Now())
strSQL = "SELECT TOP 5 * FROM tblQuestions ORDER BY rnd(-(1000*QuestionID)*Now
Ok that should just be
strSQL = "SELECT TOP 5 * FROM tblQuestions ORDER BY rnd(-(1000*QuestionID)*Now
strSQL = "SELECT TOP 5 * FROM tblQuestions ORDER BY rnd(-(1000*QuestionID)*Now
This should work:
SELECT TOP 5 * FROM tblQuestions ORDER BY (rnd([QuestionID]) + Rnd(Now())) / Rnd(Now())
SELECT TOP 5 * FROM tblQuestions ORDER BY (rnd([QuestionID]) + Rnd(Now())) / Rnd(Now())
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or this
strSQL = "SELECT TOP 5 * FROM tblQuestions ORDER BY rnd(datediff(s,convert(dat
strSQL = "SELECT TOP 5 * FROM tblQuestions ORDER BY rnd(datediff(s,convert(dat
ASKER
Gary....You Nailed it. I swear I tried something similar. Great Job and Thank you...please post a comment in this artical for the extra 100 points https://www.experts-exchange.com/questions/20674939/Getting-Random-Records-From-Access-600-PT-Question-500-from-access-group-100-here.html
Zam, convert was not a recognized function in ASP and in Access.
Zam, convert was not a recognized function in ASP and in Access.
sorry, that was in SQL Server Syntax
Gary just out of interest, why do you use such a big numbers?
rnd * 1e6 and on top of that 1000 * QuestionID
rnd * 1e6 and on top of that 1000 * QuestionID
Well the sql could be cut down to
rs.open "SELECT top 5 * FROM table1 ORDER BY rnd(-(th *" & R & "))",conn1
I was working on it from the original code
and the Random number could be reduced
R=clng(100*rnd)
but the randomness is less likely
rs.open "SELECT top 5 * FROM table1 ORDER BY rnd(-(th *" & R & "))",conn1
I was working on it from the original code
and the Random number could be reduced
R=clng(100*rnd)
but the randomness is less likely
https://www.experts-exchange.com/questions/20548602/Questions-And-Answers-Database.html