Return a Group of Random Records

To return a group of Random Records from a database. For example, a group of random questions for a quiz/test.

Original Author: Larry Boggs

Code

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:






<%

 
<%
  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

About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 105 times

Comments

No comments have been added for this post.

You must be logged in to make a comment.