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: 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!

- continued -

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:

objRS.Move rndNumber

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


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