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.
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.
Dim oConnection, oRecordset
Dim sConnString, sSQL
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
'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
Set oRecordSet = Nothing
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
Next 'Move on to next row if there is one
Response.write("There are no records.")