Tel 07 5547 8216

56 Grandview Road
Jimboomba, QLD 4280
NOW Technology Systems Pty Ltd
Trading as Site DesignNOW.com

 

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.

<%
'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
%>