ASP 101 - Active Server Pages 101 - Web03
The Place ASP Developers Go!

Please visit our partners

Windows Technology Windows Technology
15 Seconds
ASP 101
ASP Wire
VB Forums
VB Wire
internet.commerce internet.commerce
Partners & Affiliates

ASP 101 is an site
ASP 101 is an site
Internet News
Small Business
Personal Technology

Corporate Info
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash

 Top ASP 101 Stories Top ASP 101 Stories
Migrating to ASP.NET
Getting Scripts to Run on a Schedule
The Top 10 ASP Links @

Close and free up object references
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates

How to Randomize Practically Anything

by John Peterson


Last week someone asked me how to display some links in a random sequence. The links were always the same, but the order they were to be shown in couldn't always be the same. Sure it sounds easy enough, but was it really?

Looking back over our random samples and the other things we've published about randomizing items, I realized that this isn't as easy as it seems. They didn't want anything repeated... so I started thinking about all the pain of storing the id of the items I've already shown so I didn't accidentally show them again. Then I found out that the items were coming to me in the form of an ADO recordset. Not knowing what type of cursor was being used, I didn't really want to recommend that they keep moving back and forth through the recordset since this can cause errors with certain cursor types and if the cache size of the cursor is too small it could also hurt performance noticably (probably scalability more then performance... but whatever!).

The solution... don't randomize the items in question... randomize something else which you can then use to select items in a random sequence. This has the benefit of the fact that you don't need to move the items around. As long as they are addressable in some fixed order, by generating an array of random index positions you can bypass manipulating the items directly and simply request the items in the proper sequence. This should help speed as well as make it fairly simple to implement whatever the items are stored in.

The Code

Prior to my involvement the code looked something like this:
' Create Objects
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open SQLDSN, SQLUsername, SQLPassword
' Simplified for illustration
sql="SELECT Id, Name, Image, url FROM tblGroup"
Set RS = conn.Execute(sql)
	strId    = RS("Id")
	strName  = RS("Name")
	strImage = RS("Image")
	strUrl   = Trim(RS("url"))
	' Do whatever they were doing using the data
	' values we just retrieved from the RS.  I've
	' removed the code because it really has no
	' bearing on the article.
Set RS = Nothing
Set conn = Nothing

I've obviously simplified things a little, but this is basically what we started with. It's a very simple loop through a recordset that we've all written a hundred times. The question was how to leave the basic structure alone (so I didn't have to dig into the junk I replaced with the comment) and still get the items to appear in random order.

The answer looks like this (with the changes in red):

' Create Objects
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open SQLDSN, SQLUsername, SQLPassword
' Simplified for illustration
sql="SELECT Id, Name, Image, url FROM tblGroup"
Set RS = conn.Execute(sql)
Dim arrData       ' Array to Store Data
Dim arrSequencer  ' Array to Hold Random Sequence
Dim iArrayLooper  ' Integer for Looping
Dim iArraySize    ' Size of Data Array
' First array index is field:
' 0 = id, 1 = name, 2 = image, 3 = url
' Second array index = record number
arrData = RS.GetRows
' Moved up to before the loop
Set RS = Nothing
Set conn = Nothing
' Determine the size of the data array's 2nd (data) dimension.
iArraySize = (Ubound(arrData, 2) - LBound(arrData, 2)) + 1
' Get an array of numbers 0 to array size randomly sequenced.
arrSequencer = GetRandomizedSequencerArray(iArraySize)
For iArrayLooper = LBound(arrSequencer) To UBound(arrSequencer)
	strId    = arrData(0, arrSequencer(iarrayLooper))
	strName  = arrData(1, arrSequencer(iarrayLooper))
	strImage = arrData(2, arrSequencer(iarrayLooper))
	strUrl   = Trim(arrData(3, arrSequencer(iarrayLooper)))
	' Do whatever they were doing using the data
	' values we just retrieved from the RS.  I've
	' removed the code because it really has no
	' bearing on the article.
'##### End Of Run Time Code - Functions Follow #####
Function GetRandomizedSequencerArray(iArraySize)
	Dim arrTemp()
	Dim I
	Dim iLowerBound, iUpperBound
	Dim iRndNumber
	Dim iTemp
	' Set array size
	ReDim arrTemp(iArraySize - 1)
	' Init randomizer
	' Get bounds into local vars for speed
	iLowerBound = LBound(arrTemp)
	iUpperBound = UBound(arrTemp)
	' Insert initial values
	For I = iLowerBound To iUpperBound
		arrTemp(I) = I
	' Loop through the array once, swapping each value
	' with another in a random location within the array.
	For I = iLowerBound to iUpperBound
		' Generate random # in range
		iRndNumber = Int(Rnd * (iUpperBound - iLowerBound + 1))
		' Swap Ith element with iRndNumberth element
		iTemp = arrTemp(I)
		arrTemp(I) = arrTemp(iRndNumber)
		arrTemp(iRndNumber) = iTemp
	Next 'I
	' Return our array
	GetRandomizedSequencerArray = arrTemp
End Function

Since I didn't need the functionality of the recordset object, the first this I did was to get all the data and place it into an array. This allowed me to close the ADO objects before the loop and dispose of them earlier. (Generally freeing objects ASAP helps performance.) As I mentioned eariler it also frees me from worrying about the cursor type in use.

I then determined the size of the resulting array and passed it to the brains behind this script: the GetRandomizedSequencerArray function. All it does is take a number indicating the number of items you need randomized and returns an array of that size filled with numbers from 0 to one less then the array size. This is helpful because these numbers are returned in a random sequence inside the array. By using these numbers as indecies to the original array (or recordset if we hadn't switched), you can retreive the items in a random sequence without having to worry about repeating an item or resorting the original set of data.

The function itself is pretty simple so the inline comments should just about cover it. Don't let this simplicity fool you... it's exactly what makes it the routine so useful since it's generic enough to be used almost anywhere. Oh and I'm sorry about the name I picked for it... I was having a hard time coming up with anything better.


Often the first approach you think of isn't the best. When I first heard the problem, I started thinking of reordering the recordset or generating random numbers with each pass and selecting that record (after checking we hadn't already used it). After thinking about it a little, we came up with this better routine and I'm glad we did. Not only is it simpler, but it allowed us to make the switch quickly without having to even figure out what was going on inside the loop.

Follow Up

I've gotten a lot of feedback about using different SQL methods to accomplish this. For example:

SELECT Id, Name, Image, url FROM tblGroup ORDER BY NewId()

works fine in SQL Server, but not so well in Access or if the data comes from some other source. I thought that I should mention it because if you're using SQL Server or another situation in which something like this works for you then it can make things easier and might be a better approach then the one outlined above. This was meant as an introduction to a generic approach to authoring an algorithm to randomly sequence a series of items regardless of their source and it may not necessarily be the best approach for your specific scenario. I used it because I was given no information as to the data source in use and never saw the connection strings to determine if the database would support any specific SQL functions.

Related Links

  • Randomizing a Recordset - 15 Seconds publishes an alternative to our code for those of you who are running on a SQL Server database.

Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback
The Network for Technology Professionals



Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers