In Part 2 we looked at how to order a single-dimension array using an array of indexes. In Part 1 we examined a very quick and dirty way to reorder an array. In this final part, we'll look at a more real-world application of reordering arrays - the ability to display Recordsets in random order!

Displaying a Recordset in Random Order
You may be questioning the usefulness of randomly ordering an array, and for good reason. At first glance, there appears to be just a few cases where randomly ordering an array may seem useful. Upon closer inspection, however, one realizes that he or she can use random array reordering to very efficiently display the contents of a Recordset in random order!

To do this, we must first get the contents of a Recordset into an array. Thankfully, ADO provides this functionality with the GetRows method. GetRows returns a two dimensional array... the first dimension contains the various columns while the second dimension contains the various rows. (To learn more about GetRows check out: Using GetRows to Speed Up Displaying Skinny Tables!) Due to the fact that we need to reorder a two-dimensional array, we will need to tweak the above redordering function (ReOrderArrayIndexed). The tweaked function is available below:

Function ReOrderArray2D(ByVal aArray) 'Get the upper and lower bounds from the second 'dimension (the row count)... also, create a temporary 'array, aTmpArray, to hold the reordered values Dim iCount, aTmpArray iCount = UBound(aArray, 2) ReDim aTmpArray(UBound(aArray, 1), UBound(aArray, 2)) Dim iLoop, strIndex, iUpper, iLower iLower = LBound(aArray, 2) For iLoop = iLower to iCount strIndex = strIndex & CStr(iLoop) If iLoop < iCount then strIndex = strIndex & "," Next 'Choose a Random Index Randomize Timer Dim iRnd, aTmp, iTmpUpper, strNewIndex, iInnerLoop Redim aTmp(iCount) 'Loop through the array For iLoop = iLower to iCount iTmpUpper = iCount - iLoop 'Rebuild aTmp, the array of indexes ReDim Preserve aTmp(iTmpUpper) aTmp = split(strIndex, ",") 'Add an element to the temp array... iRnd = Int(Rnd * (iTmpUpper + 1)) For iInnerLoop = LBound(aArray,1) to UBound(aArray,1) aTmpArray(iInnerLoop, iLoop) = aArray(iInnerLoop, aTmp(iRnd)) Next 'Compact aTmp, the array of indexes aTmp(iRnd) = aTmp(iTmpUpper) ReDim Preserve aTmp(iTmpUpper - 1) strIndex = join(aTmp, ",") Next ReOrderArray2D = aTmpArray End Function

Before we call ReOrderArray2D, we need to use the GetRows method to convert a Recordset into an array. This is quite simple, and can be seen with the following code:

'Open up connection to database... Dim objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "DSN=Portfolio" 'Create a recordset object and grab a query Dim objRS Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open "SELECT * FROM Portfolio", objConn 'Create an Array variable and use GetRows Dim aRows aRows = objRS.GetRows() 'Clean up... objRS.Close Set objRS = Nothing objConn.Close Set objConn = Nothing

[View a live demo!]

Anytime after this code, we can randomly reorder the array aRows using the function we examined above (ReOrderArray2D). (View a live demo!) Personally, I find the above code to be a very neat way to quickly and efficiently to display an array in random order. There are other ways to randomly jumble up an array... be sure to check out Returning Rows in Random Order for information on alternative techniques!

In this article we examined a few techniques for randomly ordering the contents of an array. We also looked at how to use this "technique" for a very real-world, everyday situation - displaying the contents of a Recordset in random order.

Happy Programming!