article

Return a Group of Random Records

Email
Submitted on: 1/5/2015 7:21:00 AM
By: Larry Boggs (from psc cd)  
Level: Beginner
User Rating: By 6 Users
Compatibility: ASP (Active Server Pages)
Views: 588
 
     To return a group of Random Records from a database. For example, a group of random questions for a quiz/test.

 
				<--random.asp-->
<--Copyright (c) 1999 by Larry L. Boggs. All rights reserved.-->
<--Generate a random recordset from an Access database-->


Random Recordset







Return a Group of Random Records

While working on a web based competency-testing application I needed a way to return not just ONE random record but a group of random records. I searched the net high and low for a couple of months trying to find something that would allow me do this. I eventually hunkered down and came up with my own way of doing this.

First comes the SQL statement to return the set of records you will pick your Random records from:

<%
strConnection="driver={Microsoft Access Driver (*.mdb)};dbq=" & server.mappath("/testdb.mdb")
strSQL = "SELECT id FROM tblQuestions"
set objConn = Server.CreateObject("ADODB.Connection")
Set objRst = Server.CreateObject("ADODB.Recordset")
objConn.Open strConnection
set objRst.ActiveConnection = objConn
objRst.LockType = adLockOptimistic
objRst.CursorType = adOpenKeySet
objRst.Open strSQL
%>

Next, set the upper limit of the Randomize function by setting the variable rndMax equal to the RecordCount.

<%
objRst.MoveLast
cnt = objRst.RecordCount
cnt1 = cnt
rndMax = cnt
%>

Next, set the number of records returned to either the number of questions they asked for or equal to the RecordCount.

<%
If CInt(Request.Form("maxNumber")) < cnt Then
	cnt1 = CInt(Request.Form("maxNumber"))
End If
%>

Now we want to return a Random number. Check if the variable “str1” already contains that number. If so then that number is skipped and it loops again returning another Random record number. This ensures that NO values are repeated. If not then plug that number into the “str1” variable so we will know that that number has already been used the next time through the loop. If the random number is not contained within the “str1” variable then the value of the “ID” field is returned and plugged into the “str” variable. This loops until the appropriate number of values have been plugged into the “str” variable.

<%
str = ","
str1 = ","
	
Do Until cnt1 = 0
Randomize
RndNumber = Int(Rnd * rndMax)
If (InStr(1, str1, "," & RndNumber & "," ) = 0) Then
	str1 = str1 & RndNumber & ","
	cnt1 = cnt1 - 1
	objRst.MoveFirst
	objRst.Move RndNumber
str = str & objRst("id") & ","
	End If
Loop
%>

Now we have a variable, (str), that contains a comma-delimited list of values from the “ID” field. Now, just reference the comma- delimited string contained within the “str” variable in your SQL statement:

<%
 sql = "SELECT * FROM tblQuestions WHERE (((InStr(1,'" & str & "',(',' & [id] & ',')))<>0)) "
%>

This will return your Random set of records!

Here's the whole thing:

<%
<--Generate a random recordset from an Access database-->
<--#include virtual="/adovbs.inc"--> 
<%
Dim objConn
Dim objRst
Dim strSQL
Dim strConnection
Dim str
Dim str1
Dim cnt
Dim cnt1
Dim rndMax
Dim RndNumber
strConnection="driver={Microsoft Access Driver (*.mdb)};dbq=" & server.mappath("/testdb.mdb")
strSQL = "SELECT id FROM tblQuestions"
set objConn = Server.CreateObject("ADODB.Connection")
Set objRst = Server.CreateObject("ADODB.Recordset")
objConn.Open strConnection
set objRst.ActiveConnection = objConn
objRst.LockType = adLockOptimistic
objRst.CursorType = adOpenKeySet
objRst.Open strSQL
objRst.MoveLast
cnt = objRst.RecordCount
cnt1 = cnt
rndMax = cnt
If CInt(Request.Form("maxNumber")) < cnt Then
	cnt1 = CInt(Request.Form("maxNumber"))
End If
str = ","
str1 = ","
	
Do Until cnt1 = 0
Randomize
RndNumber = Int(Rnd * rndMax)
If (InStr(1, str1, "," & RndNumber & "," ) = 0) Then
	str1 = str1 & RndNumber & ","
	cnt1 = cnt1 - 1
	objRst.MoveFirst
	objRst.Move RndNumber
str = str & objRst("id") & ","
	End If
Loop
objRst.Close
Set objRst = Nothing
sql = "SELECT * FROM tblQuestions WHERE (((InStr(1,'" & str & "',(',' & [id] & ',')))<>0)) "
Set objRst = Server.CreateObject("ADODB.Recordset")
set objRst.ActiveConnection = objConn
objRst.LockType = adLockOptimistic
objRst.CursorType = adOpenKeySet
objRst.Open sql
%>
...DISPLAY THE RECORDS RETURNED...
<%
objRst.Close
Set objRst = Nothing
objConn.Close
Set objConn = Nothing
%>

I'd be interested in hearing from anyone that builds upon this and/or how they put it to use!

See Ya!
Larry Boggs


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Beginner category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.