Introduction
A Recordset consists of zero to many rows, each row having zero to many columns, just like a two-dimensional array. Specifically, each cell of a Recordset is a Field object, storing information like the name of the column, the value of the cell, the data type, the max length, the precision, whether or not the cell can contain a NULL value.
Paragraph Heading 1
The GetRows() method of the Recordset object we can turn the contents of a Recordset into a two-dimensional array. Understand that this array contains less information that the Recordset. Only the values of each of the cells in the Recordset are stored in the array, information like the column's name, the data type, etc., are not stored in this array. You may be wondering why, exactly, one would want to use GetRows() as opposed to just looping through a Recordset. For a good read on the performance advantages of using GetRows(), be sure to read Why GetRows() is Best to Fetch Data.
That's how we call get row funtion
'Assumes there is an open Connection object, objConn
'Create a Recordset
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "SELECT * FROM Table1", objConn
'Now, read the Recordset into a 2d array
Dim aTable1Values
aTable1Values = objRS.GetRows()
Paragraph Heading N
At this point, aTable1Values contains a row for each row in the Recordset objRS and a column for each of the Recordset's columns. At this point, we can close our Recordset and Connection objects and still work with the data in the array.
To display this array data, we need to use two nested For loops. The first loop needs to loop through each row. The total number of rows can be found by examining the upper bound of the second dimension of the array (UBound(ArrayName, 2)). Next, an inner loop needs to step through each column of the current row. This can be done via a For loop as well; to find the total number of columns, retrieve the upper-bound of the first dimension of the array (UBound(ArrayName, 1)).
So, our code to loop through the two-dimensional array would look like:
That's all there is to it! At this point, aTable1Values contains a row for each row in the Recordset objRS and a column for each of the Recordset's columns. At this point, we can close our Recordset and Connection objects and still work with the data in the array.
To display this array data, we need to use two nested For loops. The first loop needs to loop through each row. The total number of rows can be found by examining the upper bound of the second dimension of the array (UBound(ArrayName, 2)). Next, an inner loop needs to step through each column of the current row. This can be done via a For loop as well; to find the total number of columns, retrieve the upper-bound of the first dimension of the array (UBound(ArrayName, 1)).
So, our code to loop through the two-dimensional array would look like:
Dim iRowLoop, iColLoop
For iRowLoop = 0 to UBound(aTable1Values, 2)
For iColLoop = 0 to UBound(aTable1Values, 1)
Response.Write(aTable1Values(iColLoop, iRowLoop) & "
")
Next 'iColLoop
Response.Write("")
Next 'iRowLoop
Summary
GetRows() also can accept up to three optional parameters. Let's take a close look at the third optional parameter, which allows us to only bring back certain columns of the Recordset into the array.
This third, optional parameter can specifies what columns to bring back. If you don't provide this parameter as we did not provide it in our earlier example, all of the columns will be returned. If, however, you only want a subset of columns back, you can specify those column names you'd want back using an array. For example:
ArrayName = objRS.GetRows(, , Array("ColumnName1", "ColumnName2", ...))
|
No responses found. Be the first to respond and make money from revenue sharing program.
|