This article is the second of a series on how to retrieve and update information stored in databases. It is organized into the following references:

• Viewing information from a database
• Dynamically retrieving a Recordset's data
  • Creating a generic Table View
  • Creating a generic List View
• Selecting records from a database
  • Creating a custom query
  • Dynamic queries
  • Filtering the Recordset
• Retrieving properties of a database
  • Checking the name, type, and columns of each table in a database
  • Checking the fields structure of a given table

ASP provides the output directive

<%= expression %>

to evaluate and display the value of an expression.

FIELD data for an ADO Recordset object may be referenced as

recsetObj.Fields ( "fieldName" )

or using a "shortcut" instead

recsetObj ( "fieldName" )

So to output the value of a given ADO Field object in ASP

<%= recsetObj ( "fieldName" ) %>

Note though, that the shortcut method requires that "fieldName" is unique, meaning there must be NO other object of any type using the same name in the entire collection of the Recordset's stored objects.

Viewing a Recordset Run Sample | View Source

Now with that in mind, let's see some of the ways we can view the records and fields in our data source. The sample below demonstrates a simple way to populate an HTML Table with data from a Recordset object.

The main parts of the code are as follows.

• Create and open the Recordset using ADO's connection defaults.

<% 'define the provider
strProvider="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=dbtutor.mdb"

'create and open the recordset
set rsEmps=Server.CreateObject( "ADODB.Recordset" )
rsEmps.Open "Employees", strProvider, , , adCmdTable %>

• Begin the HTML table, add a row, and markup the Recordset's column headers: one header cell ( TH ) to contain the header label for EACH field you intend to display.

<table width=90% cellspacing=1 cellpadding=5 border=0>
<!-- begin column headers for Employees table -->
<tr>
   <th>Employee ID</th>
   <th>Name</th>
   <th>Title</th>
   <th>WorkPhone</th>
</tr>

• Then for the field data, loop through the Recordset, in effect adding a row for each record in the set, with one data cell ( TD ) to contain the value for EACH chosen field.

<% ' cycle through the record set and display each row results
do until rsEmps.EOF %>
  <tr>
    <td><%= rsEmps( "EmployeeID" )%></td>
    <td><%= rsEmps( "LastName" )%>, <%= rsEmps( "FirstName" )%></td>
    <td><%= rsEmps( "Title" )%></td>
    <td><%= rsEmps( "Workphone" )%></td>
  </tr>

  <% ' increment record position with MoveNext method
  rsEmps.MoveNext

  <!-- next row=next record -->

loop

Note that the second data cell actually contains the values from two fields ( LastName and FirstName ), with a comma and space separating the values.

<%=rsEmps( "LastName" )%>, <%=rsEmps( "FirstName" )%>

• Close the Recordset and flush the object from memory

rsEmps.Close
set rsEmps=Nothing %>
</table>

One important snippet to note in there ( and in most of the other samples in this series as well ), is the <% do until Recordset.EOF . . . %> loop, which cycles through the entire record set from the top, each pass displaying the given ADO data for the current record, then moves on to the next record, and does the process over UNTIL the Recordset reaches the end-of-file ( EOF ) marker.

The sample above is useful for a given set of fields from a given data source. It assumes that the record source ( the database table ) and its field structure are known beforehand.