||This FAQ describes the process of displaying data on a Web page through arrays instead of the traditional Recordset method. The way we've all likely used to display arrays involves opening a Recordset object and then using a |
Do While Not objRS.EOF ... Loop, iterating through each row in the Recordset. While this approach is fine and good, there are advantages to using a special Recordset method called
GetRows(), which reads the contents of the Recordset into a two-dimensional array; then, to display the Recordset data, you can simply iterate through this array.
While the syntax involved in using the array-based approach is a bit more confusing that simply looping through the Recordset via a
Do While Not objRS.EOF ... Loop, the main motivation behind using
GetRows() is performance. For a good read on the performance advantages be sure to read Why
GetRows() Is Best to Fetch Data.
This builds on previous FAQ's in treating Recordsets as arrays, and vice versa. Read the FAQ below as well as the documentation on
GetRows if you need to brush up.
FAQ: How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset?
Before we get started there are three important things to remember when working with
GetRows; remembering these three things will save you many headaches and hours debugging, trust me! They are:
1. VBScript arrays start at 0 and go to n-1, where n is the count of the records (or count of columns) in the recordset. If you have 30 records, you will have rows numbered from 0 to 29; if you have 5 columns, you will have columns numbered from 0 to 4. In other words, just as if you had done
Dim rows(4,29) to declare the array size yourself.
2. VBScript arrays are arranged by
MyArray(ColumnElement, RowNumber) instead of
3. The order of field selection in your SQL statement determines the column subscript used to access that field. If you have 5 fields you will have 0 to 4 column elements.
Imagine that you have the following SELECT SQL statement:
Select fName, lName, Address, City, State, Zip
ORDER BY lName, fName
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 completely 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 we need to use integer index values. To get the first column of the first row, we'd use:
MyArray(0,0). This approach, however, is very unreadable. To make your code more readable (and hence more maintainable), consider creating constants whose values represent the position in the array for the column and whose name is 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 = 4
In this instance you will have five columns in the array numbered 0 through 4.
Now that we know how our array is going to be laid out we can display the array's contents using a
For...Next loop construct. In order to use this construct we must know what the loop bounds are. Clearly we want to start from the 1st row and 1st column (array indexes 0,0). But how high do we have to go? That depends on how many columns and rows our SQL statement returns. Since we do not know the number of rows returned when writing our script, we'll have to use the VBScript
UBound function, which returns the total number of elements in an array. (An example of using
UBound can be seen in this FAQ.)
UBound is a trifle trickier to use when dealing with multi-dimensional arrays (recall that
GetRows() returns a two-dimensional array). When using
UBound with multi-dimensional arrays, you must specify the dimension you wish to get the upperbound for. So, if we want to get the total number of columns or rows, we have to use different
UBound statements, as shown below:
Ubound(MyArray,1) 'Returns the Number of Columns
Ubound(MyArray,2) 'Returns the Number of Rows
For our first example I will show how to display the data in some predetermined format, like:
lName, fName : address : city
The code to do this would be a simple loop through each of the rows in the array; then, in the loop body,
Response.Write statements would 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
If you were wanting 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. I usually just 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)
Happy Programming !!!
... By KissLizzyCooper