Data Access and Transactions

References to Field Values

One of the more surprising consequences of using a loosely typed VBScript variable is that you sometimes don’t know exactly what you are referencing. In the following ASP example, the session variable strAuthor apparently loses its value once the Recordset is closed.

Set Session("strAuthor") = rstPublishers.Fields("Author")
rstPublishers.Close

And then later in the script:

Response.Write Session("strAuthor") 'Where'd it go?

The problem is that strAuthor is a Variant data type containing a reference to a Field object, not a string, as it appears to be. When the Recordset is closed, the Field object is no longer valid, and the variable appears empty. You can avoid the problem by qualifying the code with the Value property:

Session("strAuthor") = rstPublishers.Fields("Author").Value.

Rather than perform a lookup from the Recordset collection each time you need a field (which is what happens when you use the column name), you can use a reference to a Field object to keep track of the current value of a field.

<%
   Set rs = Server.CreateObject("ADODB.Recordset")
   rs.Open "Products", "DSN=AdvWorks",_
   adOpenForwardOnly, adLockReadOnly, adCmdTableDirect
   'Select fields now, using single lookups.
   Set fldProdId   = rs("ProductID")
   Set fldProdName = rs("ProductName")
   Set fldProdDesc = rs("ProductDescription")
   
   'Loop through records using field references.
   Do Until rs.EOF
   Response.Write fldProdId & ": <b>" & fldProdName &_
   "</b>: " & fldProdDesc & "<BR>"
   rs.MoveNext
   Loop
   rs.Close
%>

With this technique, it is possible to perform a query even without specifying a SQL command.