Codefixer - Recordset GetRows Method

The GetRows method of the Recordset object lets you retrieve the resultset and place into a two-dimensional array. The code below then loops through that 2 dimensional array 'ArrResults' and displays the records. (You can read more on multi-dimensional arrays.)

The standard way to retrieve and display records has been to create a recordset object and loop through the records, though with the GetRows method there is only one call to the database making for more efficient code.

We use the UBound function which returns the index of the highest element in the array for both dimensions, this basically lets us know how many times to loop through both the rows and columns.

<%
'Declare Variables
Dim oConnection, oRecordset
Dim sConnString, sSQL
Dim arrResults
Dim iRowNumber, iColumnNumber
Dim RowCounter, ColumnCounter

'define the connection string, specify database
'driver and the location of database
sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("Users.mdb")

'Create our connection objection
Set oConnection = Server.CreateObject("ADODB.Connection")
'Open the connection to the database
oConnection.Open(sConnString)

'declare SQL statement that will query the database
sSQL = "SELECT * FROM users_tbl"

'create and fill our recordset object
Set oRecordSet = oConnection.Execute(sSQL)

If Not oRecordSet.EOF Then
'Get all the records and assign to arrResults variable
arrResults = oRecordSet.GetRows()

'Close the connection and recordset objects
oRecordset.Close
Set oRecordSet = Nothing
oConnection.Close
Set oConnection = Nothing

' Retrieve the total no. of rows (second dimension of the array)
iRowNumber = ubound(arrResults,2)
'Retrive the total no. of columns/fields (first dimension of the array)
iColumnNumber = ubound(arrResults,1)

'Loop through the array holding the resultset and display records
'Loop through rows as the outer loop
For RowCounter=0 to iRowNumber
   'Loop through columns/fields as inner loop
   For ColumnCounter= 0 to iColumnNumber
   Response.Write(arrResults(ColumnCounter,RowCounter)) & " "
   Next 'Move on to next column/field is there is one
   Response.write("<br />")
Next 'Move on to next row if there is one

Else
Response.write("There are no records.")
End If
%>

If you have any code snippets to share with full credit given then send an email to Codesnippets - You'll receive full credit and a link back to your site.