13.3.6- The Fields Collection

Created by Brendan Doss.
Last Updated by Jim Minatel.

Public Categorized as 13. Using Recordsets.

Not yet tagged

The Fields Collection

In the examples we've seen so far, we've always requested the value of a particular field by naming that field explicitly. For example, When requesting the value of the film title and director fields in the example above, we used the expressions objRS("Title") and objRS("Director") like this:

Response.Write "<TR><TD>" & objRS("Title") & "</TD>" & _

"<TD>" & objRS("Director") & "</TD></TR>"

But what if you want to use all the fields in a recordset, perhaps to build a table from the recordset? Well, we could refer to each field individually in the same way, but this becomes tedious after a while – especially if there are lots of fields in the recordset. For a start, the task of typing out these field names individually is labor-intensive; moreover, it makes for excessively long and potentially untidy code.

We all want an easy life. So, let's introduce the Fields collection, which can skim hours off our development time.

While using the Fields collection is fine, if we want to save coding time, you can't use aliases for the field names. If the field names are cryptic or written in shorthand notation, like many databases, then those exact names will be returned when the Fields collection is used.

Each Recordset object has its own Fields collection, which contains an entry for each field in the current record. That means that you can iterate through each field in the fieldset without knowing what the names of the fields are. So, for example, we can use this to display the value of each field with just a few short lines. Alternatively, we can actually use the Fields collection to find out the names of the fields that are contained in our recordset.

Aha, now we're talking. Let's use this to create a central reusable routine, which will generate an HTML table showing all the data contained in a recordset.

Try It Out – The Fields Collection

We're going to create a routine, called RecToTable(), which generates the HTML for a table containing all the data in a recordset. We'll store this routine in its own file, RecToTable.asp – then, we'll be able to use it within any ASP page that we write, simply by including RecToTable.asp as a server-side include (SSI). To demonstrate, we'll write a second page, called StaffTable.asp, which uses the RecToTable() routine to show the values of all the fields in all the records of an obscure little table called Staff.

1. We're going to use the Movies database and the strConnect connection string again, so you'll need to ensure they're set up as for previous examples in this chapter.

2. The first thing to do is write the RecToTable()routine. Create a new file called RecToTable.asp, and add the following code to it (don't worry about all the new stuff; we'll look at it after we've seen it working):

Function RecToTable (objRS)

Dim strT ' table html string

Dim fldF ' current field object

strT = "<TABLE BORDER=1><TR ALIGN=CENTER>" ' build the table header

For Each fldF In objRS.Fields ' each field as a table column name

strT = strT & "<TD>" & fldF.Name & "</TD>"

strT = strT & "</TR>"

While Not objRS.EOF ' now build the rows

strT = strT & "<TR ALIGN=CENTER>"

For Each fldF In objRS.Fields ' loop through the fields

strT = strT & "<TD>" & fldF.Value & "</TD>"

strT = strT & "</TR>"

objRS.MoveNext

Wend

strT = strT & "</TABLE>"

RecToTable = strT ' and finally return the table

End Function

3. Save the RecToTable.asp file in the \inetpub\wwwroot\BegASPFiles directory.

4. Now create another new file, called StaffTable.asp, and enter the following code into it:

Option Explicit

Dim strConnect

<!-- METADATA TYPE="typelib"

FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->

<HTML>

<HEAD>

<TITLE>ADO Fields Collection</TITLE>

</HEAD>

<BODY>

Dim objRS

Set objRS = Server.CreateObject ("ADODB.Recordset")

objRS.Open "Staff", strConnect, adOpenStatic, adLockReadOnly, adCmdTable

Response.Write RecToTable(objRS) ' pass the recordset to the table function

objRS.Close

Set objRS = Nothing

</BODY>

</HTML>

5. Save the StaffTable.asp file into the \inetpub\wwwroot\BegASPFiles directory.

6. Start up your browser, and view the page StaffTable.asp.

Chapter13_image011

It's amazing, don't you think? Remember that this is actually produced by an SSI file, so you can include this in any ASP file where you need a table like this. Let's look at how it works.

How It Works

Let's look at RecToTable.asp first, since it's the one that does the work.

First we declare our function. The RecToTable() function will take a single argument, namely the recordset from which it will build the table. It needs to be a function because after it has generated a string containing all the HTML tags and text that make up the table, it will return that string to the calling routine:

Function RecToTable (objRS)

Next we declare a couple of variables. The first will hold the HTML tags that will make up the table, and the second will hold an ADO Field object:

Dim strT ' table html string

Dim fldF ' current field object

Now we can really start. We'll begin the HTML string by writing the <TABLE> tag, and the first <TR> tag for the table header:

strT = "<TABLE BORDER=1><TR ALIGN=CENTER>" ' build the table header

Now we need to build the header line:

For Each fldF In objRS.Fields ' each field as a table column name

strT = strT & "<TD>" & fldF.Name & "</TD>"

Remember how, in Chapter 12 , we used a For Each … Next loop to iterate through all the properties in the Properties collection? Well, this is the same. We are looping through the Fields collection, and using For Each … Next will set fldF to a different Field object each time we go round the loop. The expression fldF.Name refers to the name of the field. So this code creates one table cell for each field in the Fields collection, surrounding each field name with the table cell start and end tags, like so:

<TD>MovieID</TD><TD>StaffName</TD><TD>StaffRole</TD><TD>Note</TD>

In a moment we'll use the expression fldF.Value to refer to the value contain in that field, for the current record.

Once that loop has finished, we can terminate the table header:

strT = strT & "</TR>"

Now we've finished the header line we can start on the rows of data. For each record, we need to iterate through each Field object in the Fields collection, capture the value of that field (using fldF.Value) and adding this value to the body of the HTML table. We're going to iterate through all the records in the recordset, and for each record we'll iterate through the Fields collection – so this involves a nested loop.

We can start looping through the records, using a While … Wend loop as we've used before, checking for EOF:

While Not objRS.EOF ' now build the rows

For each record, we need a row in the table, so we add the row tag to the output string:

strT = strT & "<TR ALIGN=CENTER>"

Now we iterate through the fields, adding each field's value (for this record) as a cell in the table. This is where we use the fldF.Value expression that we mentioned above – the Value property of the Field object is what holds the value of this field for this record:

For Each fldF In objRS.Fields ' loop through the fields

strT = strT & "<TD>" & fldF.Value & "</TD>"

That takes care of all the fields for one record, so we add the row terminator tag, and move onto the next record:

strT = strT & "</TR>"

objRS.MoveNext

Wend

And when the While … Wend loop has ended we can terminate the table and return the string-of-tags-and-text bag to our calling routine:

strT = strT & "</TABLE>"

RecToTable = strT ' and finally return the table

End Function

So this is actually quite simple. We just loop through the records, and for each record we loop through the fields. Now let's look at how we test this out in the calling routine, StaffTable.asp.

We have the usual #INCLUDE and METADATA commands, but this time we also have a new #INCLUDE directive for the RecToTable.asp SSI file that we've just created:

The creation of the recordset is much the same as we've seen before:

Dim objRS

Set objRS = Server.CreateObject ("ADODB.Recordset")

objRS.Open "Staff", strConnect, adOpenStatic, adLockReadOnly, adCmdTable

The recordset, objRS that we've just created contains the data from the Staff table of the movies database. Now here's the new bit: we use Response.Write to write the contents of the generated string to the HTML stream:

Response.Write RecToTable(objRS) ' pass the recordset to the table function

When we call RecToTable(), we pass the recordset objRS that we've just created as a parameter to the function. As we know, RecToTable() uses this recordset to generate a character string containing all the HTML for the table, and returns this character string on its completion. The returned character string is then sent straight to the HTML stream (and hence to the browser) via the Response.Write method.

Then we close the recordset and tidy up:

objRS.Close

Set objRS = Nothing

That's it. You can include RecToTable.asp in any file and use it to create a table.

The sample files (available for download from the WROX website ) contain an extra file, RecToTableFormat.asp; this file is much the same the same as RecToTable.asp, but has the added bonus that it formats the values according to their types. So a currency value, for example, is formatted with the currency sign, etc.

<< 13.3.5- Filtering Records

Chapter13

13.3.7- Arrays of Rows >>