Published: Tuesday, July 27, 1999
Choosing a Random Record from a Recordset
Have you ever wanted to choose a random record from a set of many records? Perhaps you have
a table of HTML links, and you want to randomly select one row from this table. Well, it's not too
difficult to do this, in fact, this very article will show you how to do this!
We are going to employ a five step method to accomplish this task, so let's get started!
1.) Create a recordset that allows the use of the .RecordCount method (i.e. adOpenStatic).
(See Recordset Cursors: Choose the Right Cursor for the Right Job
for more information on using the .RecordCount method and what cursor you'll need to do that.)
2.) Open that Recordset for all current articles and obtain the .RecordCount:
Dim strSQL
strSQL = "SELECT * FROM MyTable"
objRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly
Dim rndMax
rndMax = CInt(objRS.RecordCount)
objRS.MoveFirst
|
3.) Pick a random number between 0 and rndMax.
Randomize Timer
Dim rndNumber
rndNumber = Int(RND * rndMax)
|
4.) Use the .Move method to move to rndNumber records over:
5.) The current record is your "random" record! :)
(OK, so step five really wasn't needed, we could have gotten away with four steps, but it's always good to have your steps in intervals of prime numbers.)
It's a fairly simple technique, really. For those of you who want to see the full code, I'll share that with you. You can view it below, or view it in text format.
<% Option Explicit %>
<!-- #include virtual="/adovbs.inc"-->
<%
<![CDATA[
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DRIVER=SQL Server;SERVER=OFFICEDNS;" & _
"UID=SSDB;PWD=;DATABASE=SSDB;"
objConn.Open
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "SELECT * FROM tblItem WHERE ParentID IS NULL", objConn, _
adOpenStatic, adLockReadOnly
Dim rndMax
rndMax = CInt(objRS.RecordCount)
objRS.MoveFirst
Do While Not objRS.EOF
Response.Write objRS("ID") & "<BR>"
objRS.MoveNext
LOOP
objRS.MoveFirst
Dim rndNumber
Randomize Timer
rndNumber = Int(RND * rndMax)
objRS.Move rndNumber
Response.Write objRS("ID")
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
]]>
%>
|
If you are unfamiliar with ADOVBS.inc, be sure to check out
ADOVBS.inc... use it!
| A More Efficient Approach... |
|---|
|
While the method for selecting a random record presented in this article works well for small tables or
Access databases, a much more efficient method is available for those using SQL Servers - selecting a
random record through a stored procedure. The reason the method described in this article is so inefficient is
that the entire table must be fetched and then a random record selected... with the stored procedure
approach, a single, random record is taken from the table and sent to the ASP page. For more information on
this approach be sure to read: Getting a Random Record Using a Stored Procedure!
|
Happy Programming!
Attachments:
View the code in text format
