Now let's take a look at a "generic" method that can display data from ANY field from ANY record source.

This is extremely useful in cases where the record source is initially unknown, and allows for setting different field headers and retrieving field values dynamically, without having to know and explicitly code each field name.

This is made possible by looping thru the Recordset object's Fields collection,

<% for each field in Recordset.Fields %>

and simply rendering column headers using the notation

<%= field.Name %>

and returning the data in each field using

<%= field.Value %>

The sample below also makes use of the Connection object's OpenSchema method to dynamically retrieve the names of the available tables and views in a given database. This method returns a special type of Recordset object that we use here to enable choosing which table or view to display.

Viewing the Recordset of any Data Source Run Sample | View Source

This example also illustrates how to:

• open multiple record sets using the same connection
• populate an HTML Select element with ADO data.

Now, let's explore the significant parts of that code.

• Populate the HTML Select element by looping thru the ADO Schema record set.

<form method="post">
Select Database Table 
   <select name="getTable">
   <% do until rsSchema.EOF %>
      <option><%= rsSchema( "TABLE_NAME" )%>
      <% rsSchema.MoveNext
   loop
   rsSchema.Close
   </select> <input type="submit" value="Show me">
   set rsSchema=Nothing %>
</form>

• Begin the HTML table, add a row, and do a loop FOR EACH field in the Recordset's FIELDS collection, marking-up a column header to contain the field's name. The number of header cells created will depend on the Recordset's fields count.

<table cellspacing=1 cellpadding=5 border=0>
<!-- begin column headers for selected table -->
<tr>
   <% for each field in rsTable.Fields %>
      <th><%= field.Name %></th>
   <% next %>
</tr>

• Then to retrieve each record's fields data, loop through the Recordset, add a row, and do a loop FOR EACH field in the Recordset's FIELDS collection, marking-up a data cell to contain the field's value.

<% ' cycle through the record set and display each row results
do until rsTable.EOF %>
   <tr>
      <% for each field in rsTable.Fields %>
         <td><%= field.Value %></td>
      <% next %>
   </tr>

   <% ' increment record position with MoveNext method
   rsTable.MoveNext
   rowCount=rowCount+1

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

• Close the Recordset and flush the object from memory

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

Listing the Recordset of any Data Source Run Sample | View Source

Using the same concept, and with just a slight change to the script, we could do it like this.

In this case, each of the fields' names and values are retrieved with each pass of the FOR EACH loop.

<% ' cycle through the record set and display each row results
do until rsTable.EOF %>
   <table width=90% cellspacing=1 cellpadding=5 border=0>
   <col width=35%>
      <% for each field in rsTable.Fields %>
      <tr>
         <td><%= field.Name %></td>
         <td><%= field.Value %></td>
      </tr>
      <% next %>
   </table>

   <p><hr width=90% size=1></p>

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

   <!-- next record=next table -->
loop

' close record set and flush object from memory
rsTable.Close
set rsTable=Nothing %>