To read the article online, visit

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)

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="/"--> <% 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, be sure to check out 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!


  • View the code in text format

  • Article Information
    Article Title: Choosing a Random Record from a Recordset
    Article Author: Scott Mitchell
    Published Date: Tuesday, July 27, 1999
    Article URL:

    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers