ASPNL - The premier Dutch resource on ASP

Thursday, November 20, 2008

Display a recordset

There are many ways for dispaying the content of a recordset. The easiest and most used way is by looping through the recordset and writing the records one by one to the browser. Because of the way ADO handles databases, this isn't the most effective method. A for ASP better (faster) method is by writing an array with an especially for that purpose created function: GetRows. Because it's important to learn the right method straight away, we give an sample of it. If you want more information, read Retrieving data with GetRows.

In the sample below we open a database, retrieve the data with GetRows and display the data in a HTML-table.

select.asp
<%
Option Explicit

Dim objConn
Dim objRs
Dim arrRs
Dim strSQL
Dim lngFields
Dim lngRecords
Dim i, j

'Create SQL statement
strSQL = "strSQL = "SELECT CompanyName, Address, PostalCode, City, Country FROM Customers""

'Create Connection object
Set objConn = Server.CreateObject("ADODB.Connection")

'Open database
objConn.Open Application("ConnString")

'Create Recordset object
Set objRs = Server.CreateObject("ADODB.Recordset")

'Open recordset
objRs.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly

'If there are records, save them in an array
If Not objRs.EOF Then
   arrRs = objRs.GetRows()
   lngFields = UBound(arrRs)
   lngRecords = UBound(arrRs, 2)
Else
   lngRecords = -1
End If

'Close Recordset
objRs.Close

'Release Recordset object
Set objRs = Nothing

'Close database
objConn.Close

'Release Connection object
Set objConn = Nothing
%>
<HTML>
<BODY>
<TABLE BORDER="1">
<%
For i = 0 To lngRecords
   Response.Write "<TR>"
   For j = 0 To lngfields
      Response.Write "<TD>" & arrRs(j, i) & "</TD>"
   Next
   Response.Write "</TR>"
Next
%>
</TABLE>
</BODY>
</HTML>

<< previous | ^ to top | index | next >>