How can I convert a Recordset into an array? Conversely, how can I convert an array into a Recordset?

Imagine that you have the following SELECT SQL statement:

Select fName, lName, Address, City, State, Zip
FROM SomeTable
ORDER BY lName, fNam

and that you have already created and opened both Connection and Recordset objects. To read the contents of the Recordset into an array, use

MyArray = rsMyRecordSet.GetRows()

VBScript sets up MyArray(ColumnCount, RowCount) with ColumnCount being the number of fields selected - 1 (0 based) and RowCount being the number of records returned - 1 (0 based).

NOTE: If you want the array in any specific order, you can use the ORDER BY clause in your select. We can't recommend trying to sort the two-dimensional arrays that GetRows produces, as most any process you use to do so will be, at best, slow and tedious. The method described in this FAQ, for example, is inadequate and unusable with two-dimensional arrays.

In the above select statement, the data will be in the order of the columns in the SELECT clause. That is, the 0th column will contain the value of the current row's fName value; the 1st column will contain the value of the current row's lName value; etc.

To access the elements of an array use integer index values. To get the first column of the first row, use: MyArray(0,0). This approach, however, is very unreadable. To make your code more readable (thereby more maintainable), consider creating constants with values that represent the position in the array for the column and with names similar to the column's name. For example, it would be prudent to add:

Const MyFirstNameOrdinal = 0
Const MyLastNameOrdinal = 1
Const MyAddressOrdinal = 2
Const MyCityOrdinal = 3
Const MyZipOrdinal =

In this instance you have five columns in the array numbered 0 through 4.

Now that you know how the array is going to be laid out, you can display the array's contents using a 'For...Next' Loop construct. In order to use this construct, you must know what the loop bounds are. Clearly, you want to start from the 1st row and 1st column (array indexes 0,0). But how high do you have to go? That depends on how many columns and rows your SQL statement returns. Since you do not know the number of rows returned when writing the script, you'll have to use the VBScript UBound function, which returns the total number of elements in an array.

UBound is a trifle trickier to use when dealing with multi-dimensional arrays (you recall that GetRows() returns a two-dimensional array). When using UBound with multi-dimensional arrays, you must specify the dimension for the upperbound. Thus, if you want to get the total number of columns or rows, you have to use different UBound statements, as shown below

Ubound(MyArray,1) 'Returns the Number of Columns
Ubound(MyArray,2) 'Returns the Number of Row

For example, this is how to display the data in a predetermined format:

lName, fName : address : cit

The code to do this is a simple loop through each of the rows in the array; then, in the loop body, Response.Write statements will output the proper array values

For lnLoopCounter = 0 To Ubound(MyArray,2)
Response.Write MyArray(MyLastNameOrdinal, lnLoopCounter) _
& ", " _
& MyArray(MyFirstNameOrdinal, lnLoopCounter) _
& " : " _
& MyArray(MyAddressOrdinal, lnLoopCounter) _
& " : " _
& MyArray(MyCityOrdinal, lnLoopCounter) _
& "<BR>" & vbNewLine
Next

If you need to display the fields in the exact order as they were presented in your SELECT clause, you could add an inner loop for the columns. You can manually output the columns (as shown above) for simplicity, but either approach will work

For lnRowCounter = 0 To Ubound(MyArray,2)
For lnColumnCounter = 0 To Ubound(MyArray,1)
Response.Write MyArray(lnColumnCounter, lnRowCounter)
Next
Next