When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Friday, October 29, 1999

Returning a Random Number of Database Records
By Larry Boggs


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.

- continued -

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 str1 variable. This loops until the appropriate number of values have been plugged into the str1 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 str1 variable in your SQL statement:

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

This will return your Random set of records!

  • Try out the demo!

    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


    Attachments:

  • View the code in text format


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article