Microsoft Office Developer Forum - Access

September 25, 1998	Back to Microsoft Access Articles and Sample Apps

	ADO Programming By Paul Litwin This article originally appeared in the conference proceedings for the Microsoft Office and VBA Solutions Conference and Exposition, held in London, England, July 12-15, 1998, and is reprinted with permission of Informant Communications Group, Inc. Please visit their Web site at http://www.informant.com/mod/index.asp. Download the sample code discussed in this article. ADO is a lean, low-memory-footprint, data-access model (a programmatic language for interacting with databases) that’s optimised for access over the Internet and intranets. If you’re familiar with other Microsoft data models — that is, if you’ve used DAO in Access or RDO in Visual Basic — you’ll feel right at home with ActiveX Data Objects (ADO). Although you may find several differences between the data access models, you likely will find more similarities than differences. Using the Samples Requirements. All of the examples require ADO to be installed on your machine. You can acquire ADO by installing Active Server Pages, Visual InterDev, or the OLE DB SDK on your machine. In addition, all but a few of the examples require you to have SQL Server 6.5. The Access examples all use data stored in a SQL Server 6.5 database. The ASP examples require a Microsoft Web server with ASP installed. Some of the examples require access to SQL Server. Others use data stored in an Access database. The Excel and Visual Basic examples require SQL Server. Setting up the sample Access database. Most examples used throughout the talk involve a fictitious Internet meal delivery business called Web_Meals.Com. The Microsoft Access 97 version of this database is named Meals.mdb. Setting up the sample SQL Server database. I also created a Microsoft SQL Server 6.5 version of the Meals database. However, before you can use this version of the database, you must run a script that creates the database. Take the following steps to accomplish this: Start up SQL Enterprise Manager and log in. If you don't know how to do this step, you need to consult with your SQL Server administrator or the SQL Server documentation. Create a new database named Meals, consisting of at least 10MB, on the device of your choice. Again, if you don't know how to do this step, you need to consult with your SQL Server administrator or the SQL Server documentation. Start the SQL Query Tool by choosing Tools \| SQL Query Tool from the menu. Load the meals.sql script file. This script creates the schema (structure) of the Meals database. Execute the meals1.sql script. This script may take a few minutes to run. Load the meals2.sql script file. This script creates the records of the Meals database. Execute the meals2.sql script. This script may take several minutes to run. Be patient. Close the query tool when the script is done, and you're ready to use the database. Running the examples. There are four sets of examples. After you’ve installed the Meals databases, follow these instructions to use the examples. Access Examples All of the Access examples use ADO to access to the SQL Server Meals database. You must, of course, also own a copy of Access 97. You must create an ODBC data source named WebMealsSQL that points to the SQL Server version of the Meals database. Open the frmADOMenu switchboard form in the Access Meals database. ASP Examples All of the ASP examples require access to a Microsoft Web Server with ASP support installed. In addition, the examples require both the Access and SQL Server versions of the Meals databases. Create a new Web directory on your Web server, making sure you assign both Read and Execute permissions to the directory. Copy the ASP and HTML files to this directory. You must create an ODBC System data source named WebMealsSQL that points to the SQL Server version of the Meals database. You must also create an ODBC System data source named WebMeals that points to the Access version of the Meals database. Call up the switchboard HTML page in your Web browser using syntax like this: http://server_name/directory_name/adomenu.html where server_name is the name of your Web server and directory_name is the name of the Web directory you created. Excel Example The Excel example uses ADO to access to the SQL Server Meals database. You must own a copy of Excel 97. You must create an ODBC data source named WebMealsSQL that points to the SQL Server version of the Meals database. Open the OFC-05.xls spreadsheet file. Visual Basic 5 Example The VB 5 example uses ADO to access to the SQL Server Meals database. You must own a copy of VB 5. You must create an ODBC data source named WebMealsSQL that points to the SQL Server version of the Meals database. Open the OFC-05.vbp project file and select Run \| Start. Top ODBC, OLE DB and ADO ODBC and OLE DB are Windows application programming interfaces (APIs) for accessing data. The older ODBC specification provides data access to primarily relational SQL-based databases, and it does this well. OLE DB, Microsoft's next-generation data-access specification, allows data access to a much broader set of data providers. Those providers include non-relational database systems, e-mail systems, and CAD/CAM data stores, as well as the classic relational database system. OLE DB does not replace ODBC. In fact, OLE DB includes a data provider that allows you to use it with ODBC data sources. The important point to realise, however, is that Microsoft plans for OLE DB to go far beyond ODBC in providing universal access to data, no matter how or where it gets stored on the enterprise. Where does ADO fit into this picture? ADO is a high-level interface to OLE DB. For now, you can conduct business as usual because you can use ADO to access OBDC data sources through OLE DB's ODBC data provider. In the future, however, you will be able to use ADO to access your e-mail system or some other non-relational data provider. Microsoft has publicly stated that ADO eventually will replace the company's current myriad of data-access models, including DAO and RDO, so keep in mind that ADO is not just for Internet/intranet data access. ADO is the Microsoft data access model for the future! The ADO Object Model The ADO object model, shown in Figure 1, is much simpler than the DAO and RDO object models. Figure 1: The top-level object in ADO is the Connection object, which contains Command and Recordset objects and the Errors collection. As you can see in Figure 1, ADO, unlike DAO and RDO, lacks a collection of tables, or an environment, workspace, or engine type object. The major ADO objects and collections are: Connection object: You use the ADO Connection object to create a connection to a data provider. The ADO Connection object is similar to the DAO database object or the RDO rdoConnection object. Recordset object: You use the ADO Recordset object to create a set of records from a query. Like the DAO Recordset and the RDO rdoResultset, you can move forward and backward through ADO recordsets. Sometimes, ADO recordsets are called cursors. ADO recordset cursors are always built on the server. Command object: You use the ADO Command object to point to SQL strings, stored procedures, or action queries that you can execute. The Command object is similar to the DAO QueryDef object or the RDO rdoPreparedStatement. Errors collection: The ADO Errors collection allows you to loop through the collection of errors that may occur from a failed data-access attempt. Because one data-access statement may produce multiple errors, ADO defines the Errors collection rather than a single Error object. The ADO Errors collection is similar to the DAO collection of the same name and the rdoErrors collection. Accessing Data Using ADO The following ASP file (emp1.asp) uses ADO to grab a record from the Access 97 WebMeals.mdb database using the WebMeals ODBC system data source. Following is the complete emp1.asp page:


<HTML>
<HEAD>
<TITLE>Emp1.asp Example</TITLE>
</HEAD>
<BODY>
<H1> Employee #1 </H1>
<%
' Declare the variables
Dim rst
Dim varSQL

' Create an empty recordset object
Set rst = Server.CreateObject("ADODB.Recordset")

' Create the SQL statement and stick it in varSQL
varSQL = "SELECT * FROM qryCurrentEmployees " & _
 "WHERE EmployeeID = 1"

' Fill the recordset with records based on the
' SQL statement in varSQL and the WebMeals
' data source
rst.Open varSQL, "WebMeals"

' Display the data
%>

<B>Name:</B> <%= rst("FirstName") %>
 &nbsp; <%= rst("LastName") %>
<BR><B>Address:</B> <%= rst("Address") %>
<BR><B>City/State/Zip:</B> <%= rst("City") %>,
 <%= rst("State") %>
 &nbsp; <%= rst("ZipCode") %>

<%
' Clean up the objects
rst.Close
%>
</BODY>
</HTML>

You can see the preceding code in action in Figure 2.

Figure 2: This page uses ADO code to extract a single record from the qryCurrentEmployees query and display it on the Web page.

Note: If you want to run this example, you need to create a System data source called WebMeals on your Web server machine that points to the WebMeals.mdb database.

Pure HTML. The emp1.asp file seems as if it contains a lot of code, but it’s actually quite simple if you break it down into several smaller parts. The first part of the ASP page is just pure HTML:

<HTML>
<HEAD>
<TITLE>Emp1.asp Example</TITLE>
</HEAD>
<BODY>
<H1> Employee #1 </H1>

Declaring variables. In this stretch of code, I declare two variables. Remember that all variables are variants in VBScript (the default ASP scripting language), so you don’t find As Datatype in the VBScript Dim statement.

<%
' Declare the variables
Dim rst
Dim varSQL

Creating the Recordset object. You create the recordset in several steps:

Create an empty Recordset object:
Set rst = Server.CreateObject("ADODB.Recordset")
If you’re used to DAO or RDO in VBA, this way of creating objects may seem strange. VBA allows you to early bind your objects — when you write your code, you can set a reference to the object model’s type library. (Sometimes, this reference gets created automatically.) You avoid having to use the CreateObject function. Unfortunately, because the VBScript language doesn’t support early binding, you must create all objects using the CreateObject function.
You create ASP objects using the Server object, which provides access to methods and properties of the Web server.

Add a SQL statement into the varSQL variable:

varSQL = "SELECT * FROM qryCurrentEmployees " & _ "WHERE EmployeeID = 1"
For Access data sources, you can refer to both tables and queries in your SQL statements.

Use the Recordset object’s Open method to fill it with the records returned by executing the SQL statement from varSQL.

The second argument, in this example "WebMeals", tells ADO the name of the data source to use:

rst.Open varSQL, "WebMeals" %>

Displaying the data. To display the data from the record returned by the recordset, you must use a mixture of HTML and VBScript. The HTML provides the labels for the data; the VBScript provides the values from the recordset using the names of the fields from the Access query:

<B>Name:</B> <%= rst("FirstName") %>
 &nbsp; <%= rst("LastName") %>
<BR><B>Address:</B> <%= rst("Address") %>
<BR><B>City/State/Zip:</B> <%= rst("City") %>,
 <%= rst("State") %>
 &nbsp; <%= rst("ZipCode") %>

You can insert a nonbreaking space into your HTML by using the   sequence of characters.

Clean-up time. The last section of code closes the Recordset object and ends the HTML:

<%
rst.Close
%>
</BODY>
</HTML>

Now that wasn’t so bad, was it?

Flattening the Object Model

In ADO, the object hierarchy has less emphasis. This means that, unlike DAO and RDO, you don’t have to work your way down the object hierarchy just to create an object. I took advantage of this fact in emp1.asp by directly creating the rst recordset without first creating a connection.

What’s the advantage to this flatter object model? You have fewer lines of code and less use of memory. I save a bit of memory because I avoid creating a Connection object. Memory use is especially critical if dozens or even hundreds of clients simultaneously use your Web page.

In many cases, however, you need to explicitly create a Connection object because it’s more efficient when you perform multiple recordset or command operations. Having the flexibility is nice, though.

Recordset options galore. The Open method of the Recordset object allows you to create different kinds of recordsets. The syntax of the Open method follows:

recordset.Open source, activeconnection, _
 cursortype, locktype, options

Source. The first argument of the Open method, Source, represents the source of the recordset. In this case, I used a SQL string in emp1.asp, but I also could have used the name of a table, or a server-stored procedure.

ActiveConnection. In the second argument, ActiveConnection, you can do one of the following:

Use a connection string, which creates a new connection for the sole purpose of creating the recordset.

Point to an active Connection object that you already created.

CursorType. You use the third argument, CursorType, to specify the type of recordset to create. A summary of the possible values of this argument appear in Figure 3.

Value	Constant	Description
0	adOpenForwardOnly	Forward-only recordset cursor (the default)
1	adOpenKeyset	Keyset recordset cursor
2	adOpenDynamic	Dynamic recordset cursor
3	adOpenStatic	Static recordset cursor

Figure 3: Possible cursor type values.

The default recordset cursor is a forward-only recordset. Unlike DAO recordsets, you can update all ADO recordsets, even the static and forward-only recordsets. The different types of recordsets vary in how your recordset reflects changes made by other users.

Changes to the records made by other users in static and forward-only recordset are invisible to you. That is, when you create a static or forward-only recordset, you are totally unaware of any changes made by other users. If you don’t need to move backward through the recordset, then use the forward-only recordset because it consumes less memory and is faster than the static cursor.

Forward-only recordsets don’t support methods such as MovePrevious, MoveFirst, or MoveLast. Also, forward-only recordsets don’t support the use of the RecordCount property.

The keyset recordset is very similar in behavior to the DAO dynaset. The number of records in a keyset recordset never changes. You never find out about additions that other users make, nor do records deleted by other users disappear from your recordset (although the deleted records become inaccessible). However, you do get notification when existing records get updated.

The dynamic recordset has no DAO counterpart. This type of recordset is totally dynamic. When you have a dynamic recordset open, you get notices of recordset additions, deletions, and updates. While the dynamic recordset is the most functional, be aware that it’s also the most expensive in terms of memory and speed.

LockType. The fourth argument specifies the type of locking to employ. A summary of the possible lock types appears in Figure 4.

Value	Constant	Description
1	adLockReadOnly	Read only
2	adLockPessimistic	Pessimistic locking
3	adLockOptimistic	Optimistic locking
4	adLockBatchOptimistic	Optimistic locking with batch updates

Figure 4: Possible LockType values.

Set LockType to 1, read only, if you aren’t updating records.

Generally, if you’re updating records, you should set LockType to 3, optimistic locking. All data providers support this type of locking. When you choose optimistic locking, the record locks only while ADO physically updates the record. In contrast, a Locktype of 2 provides pessimistic locking (if the data provider supports it). In this type of locking, the record locks while you edit the record. Some data providers may not offer pessimistic locking.

Setting the LockType argument to 4 tells ADO you want to employ batch updating. This form of updating allows you to download multiple records, update them locally, and submit them back to the data provider in a single batch. Many data providers do not support batch updating.

Options. You use the optional Options argument to indicate the type of Source argument you provided. You can set Options to any of the options in Figure 5.

Value	Constant	Description
1	adCmdText	Command text (SQL statement)
2	adCmdTable	Table, view, or saved select query
4	adCmdStoredProc	Stored procedure or saved action query
8	adCmdUnknown	Unknown (the default)

Figure 5: Possible options values.

If you submit a SQL string as the Source argument, then set Options to 1.

If you set the Source argument to the name of a table, select query or view, then you need to use a value of 2. Not all data providers allow you to use the adCmdTable option.

If you want to call a stored procedure or saved action query, then you need to set Options to 4.

By default, Options is set to a value of 8 (unknown). In this case, ADO attempts to infer the source type from its contents.

Constants — what constants? The constants listed in Figures 3, 4, and 5 are not predefined for you by VBScript as they are when you use ADO from VBA (with a reference to the ADO type library). You can use these constants in two ways in your code:

Define your own constants using these suggested names.

Include the file adovbs.inc "include" in your code. (If you use JavaScript, you can include the file adojavas.inc include instead).

You can find these include files in the Program Files\Common Files\System\Ado folder on your Web server. (Older versions of the Microsoft Web servers place these files in the \Inetpub\wwwroot\ASPSamp\Samples folder.)

To incorporate an include file in your ASP file, use a server-side include statement like the following:

<!--#include virtual="/ASPSAMP/SAMPLES/ADOVBS.INC"-->

You need to place this preceding statement outside of any script. For example, take a look at the next block of code:

<%
Dim con
Dim rst
Dim strSQL
%>
<!--#include virtual="/ASPSAMP/SAMPLES/ADOVBS.INC"-->
<%
Set con = Server.CreateObject("ADODB.Connection")
' ...

If you need only a few constants, however, using the entire advobs.inc include file is probably overkill and wastes a lot of memory.

Another Recordset example. The emp1.asp example displays a single record on the Web page. More typically, you want to display a group of records on a single page using an HTML table — as shown in the second example, cust1.asp. The second example draws its records from the SQL Server version of the Meals database.

Following is the complete ASP file:

<HTML>
<HEAD>
<TITLE>Cust1.asp Example</TITLE>
</HEAD>
<BODY>
<H1> Our Customers </H1>
<%
' Declare the variables
Dim con
Dim rst
Dim strSQL
' Declare the Open method constants
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = 1

' Create a Connection object and point it to
' the WebMealsSQL data source passing it
' a username and password
Set con = Server.CreateObject("ADODB.Connection")
con.Open "WebMealsSQL", "WebUser", ""

' Create the Recordset object and fill it with
' the records from the SQL statement, using the
' already established connection
' Since this page is used to create an HTML table,
' a static, read-only recordset is created
strSQL = "SELECT * FROM tblCustomers " & _
 "ORDER BY LastName, FirstName"
Set rst = Server.CreateObject("ADODB.Recordset")
rst.Open strSQL, con, adOpenStatic, _
 adLockReadOnly, adCmdText
' Only display table if there are records
' in the recordset
If Not rst.EOF Then
  ' Display the header
%>
  <TABLE BORDER>
  <TR>
    <TD><B>Firstname</B></TD>
    <TD><B>Lastname</B></TD>
    <TD><B>City</B></TD>
  </TR>

<%
  ' Loop through the recordset until there
  ' aren't anymore records
  Do While Not rst.EOF
    ' Display the record values
%>
    <TR>
      <TD><%= rst("FirstName") %></TD>
      <TD><%= rst("LastName") %></TD>
      <TD><%= rst("City") %></TD>
    </TR>
<%
    ' If you forget this next statement,
    ' you'll keep printing the same record
    ' and create a very, very large table!
    rst.MoveNext
  Loop

Else
%>
  <B>No records in recordset!</B>
<%
End If

' Clean up time
rst.Close
con.Close
%>
</TABLE>
</BODY>
</HTML>

You can see the preceding block of code in Figure 6 as it appears in Internet Explorer 4.0.

Figure 6: This page uses ADO code to fill an HTML table with records from the tblCustomers table.

Note: If you want to run this example, you need to create a System data source called WebMealsSQL on your Web server machine that points to the WebMeals SQL Server database. (Alternately, if you don’t have access to SQL Server you could point this data source to the Access version of the Meals database instead.)

In the next few sections, I highlight some of the key pieces of code in cust1.asp.

Declaring variables and constants. After the requisite HTML header, the VBScript code in cust1.asp declares its variables and several constants that the Open method uses:

<%
Dim con
Dim rst
Dim strSQL
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = 1

Using constants in your VBScript code instead of arbitrary numbers is always a good idea. This practice makes your code more readable.

Opening a connection. The following code from cust1.asp creates a connection object and opens it:

Set con = Server.CreateObject("ADODB.Connection")
con.Open "WebMealsSQL", "WebUser", ""

Because the WebMealsSQL data source points to a SQL Server database, you must provide username and password arguments. For Access databases, these arguments are optional. (If you’re using Access instead of SQL Server, you’ll need to change the last line to:

con.Open "WebMealsSQL"

The first argument of the Connection object’s Open method, ConnectionString, may refer to either an ODBC data source name (DSN) or a detailed ADO connection string. All the examples in this paper use the former, but in some cases you may need to provide further information. You need a detailed ADO connection string when you’re using a non-ODBC data provider.

Creating the Recordset. This section of code creates the recordset using a SQL statement and links it up to the existing Connection object:

strSQL = "SELECT * FROM tblCustomers " & _
 "ORDER BY LastName, FirstName"
Set rst = Server.CreateObject("ADODB.Recordset")
rst.Open strSQL, con, adOpenStatic, _
 adLockReadOnly, adCmdText

What if there aren’t any records? Cust1.asp includes the following If..Then statement to handle the case where the query returns no records:

If Not rst.EOF Then
 ' ...create the table ...
Else
%>
  <B>No records in recordset!</B>
<%
End If

The preceding code uses the EOF property of the recordset to check if any records are present. If the query returns an empty recordset, ADO sets the EOF property to True, otherwise it sets EOF to False.

Displaying the table. If you have records in the recordset (rst.EOF = True), then you create the table header with this code:

%>
  <TABLE BORDER>
  <TR>
    <TD><B>Firstname</B></TD>
    <TD><B>Lastname</B></TD>
    <TD><B>City</B></TD>
  </TR>

After you create the header, you need to loop through each of the records and plug the FirstName, LastName, and City values into the rows of the HTML table. Do this by using a Do While loop and the MoveNext method of the recordset:

<%
  Do While Not rst.EOF
%>
    <TR>
      <TD><%= rst("FirstName") %></TD>
      <TD><%= rst("LastName") %></TD>
      <TD><%= rst("City") %></TD>
    </TR>
<%
    rst.MoveNext
  Loop

Don’t forget the MoveNext method. (Does this sound like a voice of experience speaking?) If you do, then the Do While loop prints the same record in an infinite loop, producing a Web page of infinite length — probably not what you intended.

Updating Data

Using ADO, you can update records, add new records, and delete records. The following sections demonstrate how to accomplish these feats with several examples.

Adding records to a Recordset. To add a new record to an ADO recordset, you use the AddNew and Update methods of a Recordset object. First, though, you need to create a Recordset object. After you create a non-read-only recordset, you’re ready to add a new record. The code to add a record should look something like this:

rst.AddNew
  rst("field1") = value1
  rst("field2") = value2
  ' ...
rst.Update

Adding a new record is always a three-step process:

Call the AddNew method to tell ADO you want to add a new blank record.

Set the values of one or more fields for the new record.

Call the Update method to save the new record to the database.

The next example consists of two files: an HTML file that captures a new record using an HTML form (addcust1.html); and an ASP file (addcust1.asp) that adds the new record to the tblCustomers table using the AddNew and Update methods. Following is the addcust1.html file:

<HTML>
<HEAD>
<TITLE>Addcust1.html Example</TITLE>
</HEAD>
<BODY>
<H1> Adding A New Customer </H1>

<FORM METHOD="POST" ACTION="Addcust1.ASP">
<TABLE>
  <TR><TD>First Name:</TD>
      <TD><INPUT NAME="FirstName" SIZE=20></TD></TR>
  <TR><TD>Last Name:</TD>
      <TD><INPUT NAME="LastName" SIZE=20></TD></TR>
  <TR><TD>Address:</TD>
      <TD><INPUT NAME="Address" SIZE=40></TD></TR>
  <TR><TD>City:</TD>
      <TD><INPUT NAME="City" SIZE=20></TD></TR>
  <TR><TD>State:</TD>
      <TD><INPUT NAME="State" SIZE=6></TD></TR>
  <TR><TD>ZipCode:</TD>
      <TD><INPUT NAME="ZipCode" SIZE=10></TD></TR>
</TABLE>
<BR><INPUT TYPE="SUBMIT" VALUE="Save Record">
<INPUT TYPE="RESET" VALUE="Reset Fields">
<BR><BR><A HREF="adomenu.html">Abandon edits
and return to home page</A>
</FORM>
</BODY>
</HTML>

The addcust1.html page appears in Figure 7.

Figure 7: Adding a new record to the tblCustomers table.

And following is the addcust1.asp file that addcust1.html posts its data to:

<HTML>
<HEAD>
<TITLE>Addcust1.asp Example</TITLE>
</HEAD>
<BODY>
<H1> Adding A New Customer </H1>
<%

Dim con
Dim rst
Const adOpenKeyset = 1
Const adLockOptimistic = 3

' Create the objects
Set con = Server.CreateObject("ADODB.Connection")
Set rst = Server.CreateObject("ADODB.Recordset")

' Open the connection to the Meals database
con.Open "WebMealsSQL", "WebUser", ""

' Create a keyset recordset based on the
' tblCustomers table with optimistic locking
rst.Open "SELECT * FROM tblCustomers", con, _
 adOpenKeyset, adLockOptimistic

' Create a new blank record
rst.AddNew

  ' Set the fields to the user-entered values
  ' from the html form used to collect the data
  rst("FirstName")= Request.Form("FirstName")
  rst("LastName")= Request.Form("LastName")
  rst("Address")= Request.Form("Address")
  rst("City")= Request.Form("City")
  rst("State")= Request.Form("State")
  rst("ZipCode")= Request.Form("ZipCode")

' Save the new record to the database
rst.Update

Response.Write "<B>Thank you!</B>"

rst.Close
con.Close
%>
<BR><A HREF="adomenu.html">Return to home page</A>
</BODY>
</HTML>

Because addcust1.asp needs to update data, I created a keyset recordset with optimistic locking enabled:

rst.Open "SELECT * FROM tblCustomers", con, _
 adOpenKeyset, adLockOptimistic

Values from the calling HTML form get sucked into the code using the Response object, as in:

  rst("FirstName")= Request.Form("FirstName")

When you successfully submit the new record, a confirmation page, addcust1.asp, is displayed.

What about handling errors? The code in update1.asp assumes that the new record is always successfully added to the tblCustomers table. Unfortunately, the fact that the record was added at this point is not a foregone conclusion. Several things can and will (from time to time) go wrong. For example, the update may fail for the following reasons.

You lack the correct permissions to update the records

A referential integrity or index error

A field that doesn’t accept nulls is left blank

You saved a string value to a numeric field

You attempt to save a value that is too large for a field

A check constraint (SQL Server) or a validation rule (Access) fails

Always remember the one rule in computing: If something can go wrong, it will go wrong — especially when you’re on vacation!

So what happens if you encounter an error while saving the record? The ASP scripting engine prints an error to the page, as shown in Figure 8. The message varies, depending on the type of error. No matter what the error, the result isn’t very professional looking.

Figure 8: An error message as displayed by default.

VBScript error handling. Unlike its cousin VBA, VBScript has limited custom error-handling capabilities. VBScript doesn’t support the use of procedure-level error handlers. The error-handling capabilities of VBScript consist of the ability to turn off the default error handling (which allows you to intercept the errors and handle them yourself) with:

On Error Resume Next

and the ability to turn default error handling back on with

On Error GoTo 0

In addition, when you turn off the default error handling, you can use the VBScript Err object to get information about the error. For example, you can check if an error has occurred with an If..Then statement like the following:

If Err.Number <> 0 Then
  ' You know an error has occurred.
End If

Whenever you check its value, Err.Number equals:

0 if no error has occurred, or

a long integer containing the error number of the error that has occurred

You also have access to the error message that VBScript would have displayed. You can find this message stored in the Description property of the Err object. You can display the message with the following code:

Response.Write Err.Description

You must test the value of Err.Number immediately because Err.Number resets after each statement and thus contains only information regarding the most recent error.

The ADO errors collection. In addition to the VBScript Err object, the ADO Connection object has an Errors collection that may contain multiple entries regarding the last ADO error. The Errors collection’s Count property tells you how many errors, if any, are in the collection. Thus, with code like this you can display all the errors that may have occurred from an ADO operation. (In the following example, con points to an active ADO Connection object):

varErrorCount = con.Errors.Count
If varErrorCount > 0 Then
    For varI = 0 To varErrorCount - 1
      Response.Write "<BR><I>" & _
       con.Errors(varI).Description & "</I>"
    Next
  End If

Which one should I use? Why use the ADO Errors collection when you can use the simpler VBScript Err object? A particular ADO operation sometimes may generate a series of errors. In these cases, the ADO Errors collection provides additional information on the problem. In most cases, however, the VBScript Err object and the ADO Errors collection return the same information, so you’re fine, in most cases, if you use the simpler Err object.

No matter which form of error reporting you decide to use, you must use the VBScript On Error Resume Next statement to tell VBScript you want to intercept the errors that VBScript normally handles.

An error-handling example. If you decide to implement custom error handling in your ASP pages, be prepared for a bunch of extra code. Because VBScript lacks any way to define a procedure-level error handler, you need to check for the occurrence of an error after every VBScript statement — or at least after every VBScript statement that you think may cause an error.

One way to lessen the error-handling load is to create a function to take care of some of the tedium of handling errors. I create such a function in a modification to the addcust1.asp file named addcust2.asp. The complete code for addcust2.asp is on the CD. Here, I highlight just the key parts of the ASP page.

The HandleErrors function takes care of the error-handling in the following code:

Function HandleErrors(varNum, varDesc)
  On Error Resume Next
  If varNum <> 0 Then
    Response.Write "<BR><B>The following " & _
     "error occurred:</B><BR>"
    Response.Write varDesc
    HandleErrors = 1
  Else
    HandleErrors = 0
  End If
End Function

The main code from addcust2.asp calls HandleErrors immediately after every statement that may cause an error. For example, the code calls HandleErrors after the Update method:

rst.Update
varErrors = varErrors + _
 HandleErrors(Err.Number, Err.Description)

The main code passes HandleErrors the error number and the description. I define a variable, varErrors, to keep track of the number of errors that occurred. This approach works because HandleErrors returns a 0 if no error occurs or a 1 if an error occurs. At the end of the main code, I know whether the record addition succeeded and I can display the status of the record addition to the user.

Response.Write "<BR><BR>"
If varErrors = 0 Then
  Response.Write "<B>Record added!</B>"
Else
  Response.Write "<B>Record could not be " & _
   "added because of errors!</B>"
End If

The same error that occurs in Figure 8 appears in Figure 9 using a custom error-handling routine.

Figure 9: This error message is more professional looking and makes it clearer that the record didn’t get added.

Going to the trouble of handling errors has two advantages:

You can display better, more user-friendly information to the user

You can react to the error and branch in your code appropriately

You don’t have to display the VBScript or ADO error message to the user. You can always check to see which error has occurred (using Err.Number) and then display more user-friendly versions of some of the most common errors.

Top

Updating a Record in a Recordset

Unlike DAO and RDO, there is no Edit method of a recordset in ADO to indicate when you want to begin editing a record. The code to edit a record should look something like the following:

rst("field1") = value1
rst("field2") = value2
' ...
rst.Update

Editing a record is a three-step process:

Move to the record you want to edit.

Set the values of one or more fields for the record.

Call the Update method to save the changes to the database.

In fact, even Step 3 is optional. If you forget to use the Update method to save your changes and move to another record, ADO automatically saves your edits for you. Wow!

How then do you abandon your edits? Fortunately, ADO recordsets have a method, CancelUpdate, which you can use to throw away your edits. Using the CancelUpdate method can’t be easier:

rst.CancelUpdate

I’ve created an example that you can use to update records in the tblCustomer table of the SQL Server Meals database. This example uses three files:

Update.asp: This page displays a list of customers and lets you click on a customer to edit the associated record.

UpdRec.asp: This page displays the current data from the customer record using an HTML form and includes buttons to edit or delete the record.

UpdAct.asp: This page either saves the changes or deletes the record.

In this section, I discuss the updating portion of the example files. Later in this paper, I discuss the delete functionality of the example files.

Update.asp. Much of the code in Update.asp is identical to the code from cust1.asp. You can use the following section of code to display the values in a standard HTML table:

<%
  ' Loop through the recordset
  Do While Not rst.EOF
    ' Display the record values
%>
    <TR>
      <!--Create a hyperlink in the left column
          that passes the CustomerId to the
          UpdRec.asp page                      -->
      <TD><A HREF="UpdRec.asp?CustomerId=
          <%= rst("CustomerId") %>">
          <%= rst("Name") %></A></TD>
      <TD><%= rst("City") %></TD>
      <TD><%= rst("State") %></TD>
    </TR>
<%
    ' Move to the next record
    rst.MoveNext
  Loop

The only way the preceding code differs from the code in cust1.asp is the part that creates the first column of the table. The preceding example uses the <A> tag to create a hyperlink that displays the customer’s name from the Name field, but sets up a hyperlink to UpdRec.asp, passing this page the string "CustomerId=" and the value of the CustomerId for that record.

This sends HTML to the browser that looks like the following (for the record with a CustomerId = 7):

<A HREF="UpdRec.asp?CustomerId=7">Alicia Brown</A>

Take a look at Figure 10 to see how update.asp appears in Netscape Navigator.

Figure 10: The Update.asp page lets you select any customer for updating.

UpdRec.asp. The code behind UpdRec.asp is responsible for displaying the values from the requested record, as well as two submit buttons that determine the action to take on the record.

Following is the complete UpdRec.asp file:

<HTML>
<HEAD>
<TITLE>UpdRec.asp Example</TITLE>
</HEAD>
<BODY>
<H1> Customer Record Update Page </H1>
<%
' Declare the variables
Dim con
Dim rst
Dim strSQL
' Declare the Open method constants
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = 1

' Create a Connection object and point it to
' the WebMealsSQL data source
Set con = Server.CreateObject("ADODB.Connection")
con.Open "WebMealsSQL", "WebUser", ""

' Create a recordset with one record in it
' based on the CustomerId passed to this page
' from the update.asp page
strSQL = "SELECT * FROM tblCustomers " & _
 "WHERE CustomerId =" & Request("CustomerID")
Set rst = Server.CreateObject("ADODB.Recordset")
rst.Open strSQL, con, adOpenStatic, _
 adLockReadOnly, adCmdText

' Only display form if there is a matching
' record
If Not rst.EOF Then
%>
<FORM METHOD="POST" ACTION="UpdAct.ASP">
  <INPUT Type="HIDDEN" NAME="CustomerId"
  Value="<% = rst("CustomerId") %>">
<TABLE>

  <TR><TD>CustomerId:</TD><TD>
  <B><I><% = rst("CustomerId") %></I></B>
  </TD></TR>

  <TR><TD>First Name:</TD><TD>
  <INPUT NAME="FirstName"
  Size=20 Value="<% = rst("FirstName") %>">
  </TD></TR>

  <TR><TD>Last Name:</TD><TD>
  <INPUT NAME="LastName"
  Size =20 Value="<% = rst("LastName") %>">
  </TD></TR>

  <TR><TD>Address:</TD><TD>
  <INPUT NAME="Address"
  Size=40 Value="<% = rst("Address") %>">
  </TD></TR>

  <TR><TD>City:</TD><TD>
  <INPUT NAME="City"
  Size =20 Value="<% = rst("City") %>">
  </TD></TR>

  <TR><TD>State:</TD><TD>
  <INPUT NAME="State"
  Size =6 Value="<% = rst("State") %>">
  </TD></TR>

  <TR><TD>ZipCode:</TD><TD>
  <INPUT NAME="ZipCode"
  Size =10 Value="<% = rst("ZipCode") %>">
  </TD></TR>
</TABLE>
<BR><INPUT TYPE="SUBMIT" Name="Submit"
     VALUE="Save Changes">
    <INPUT TYPE="SUBMIT" Name="Submit"
     VALUE="Delete Record">
</FORM>

<%
Else
%>
  <B>No record was found matching this CustomerID!</B>
<%
End If

' Clean up time
rst.Close
con.Close
%>

<A HREF="update.asp">[Abandon edits and select another customer]</A>
&nbsp;&nbsp;
<A HREF="adomenu.html">[Return to home page]</A>
</BODY>
</HTML>

The preceding page creates a recordset based on the CustomerId value passed to the page by the update.asp page. Following is the SQL for the recordset:

strSQL = "SELECT * FROM tblCustomers " & _
 "WHERE CustomerId =" & Request("CustomerID")

The following statement sets up the form to post its data to the UpdAct.asp page when the user clicks the Submit button:

<FORM METHOD="POST" ACTION="UpdAct.ASP">

The form displays the CustomerId value as regular HTML text rather than as a form field to prevent the user from changing it. I also include, however, a hidden control on the form that contains the CustomerId value so it can be passed along to the UpdAct.asp page:

  <INPUT Type="HIDDEN" NAME="CustomerId"
  Value="<% = rst("CustomerId") %>">

A good way to pass along information between pages is to store it in hidden controls.

The UpdRec.asp page contains two Submit buttons. Because I include Name subtags for both Submit buttons and give them the same name — in this example Submit (but there’s nothing special about this name) — the name and value pair post to UpdAct.asp (see Figure 11). Thus, the code in UpdAct.asp can tell which button the user clicks. This trick doesn’t work unless you include the Name subtag in the Submit button tags.

Figure 11: You can edit a customer record using the UpdRec.asp page.

UpdAct.asp. The UpdAct.asp page completes the update operation by opening a recordset based on the edited record from UpdRec.asp and saving the updated values back to the record. Much of this code is similar to the two other pages, so in this section I highlight only key portions of the code.

In this portion of code from UpdAct.asp, the LockType argument of the rst.Open method is set to "adLockOptimistic" to allow for editing:

rst.Open strSQL, con, adOpenStatic, _
 adLockOptimistic, adCmdText

The following If..Then statement determines which of the Submit buttons on UpdRec.asp the user clicked:

If Request("Submit")= "Save Changes" Then

The updated values carry over from UpdRec.asp, and the Update method saves the changes to the database in this stretch of code:

  rst("LastName") = Request("LastName")
  rst("FirstName") = Request("FirstName")
  rst("Address") = Request("Address")
  rst("City") = Request("City")
  rst("State") = Request("State")
  rst("ZipCode") = Request("ZipCode")
  rst.Update

Top

Deleting a Record from a Recordset

Deleting a record in ADO is pretty simple. You use the Delete method of the Recordset object to delete the current record. The code looks like the following:

rst.Delete

Deleting a record is a two-step process:

Move to the record you want to delete.

Call the Delete method to delete the record from the database.

Following is the code in UpdAct.asp that deletes the record:

If Request("Submit")= "Save Changes" Then
  '...
Else
  ' Delete record
  rst.Delete
  '...
End If

Top

Executing Queries and Stored Procedures

The ADO Command object makes it easy for you to execute Access action queries or server-stored procedures. You can use the Command object in one of two ways:

With stored procedures that don’t return records or Access action queries

With stored procedures that return records

To execute a Command object on a stored procedure that doesn’t return records or an Access action query (which never returns records), you use the following syntax:

cmd.Execute RecordsAffected, Parameters, Options

When you need to create a recordset for a stored procedure, you use this syntax instead:

Set rst = cmd.Execute(RecordsAffected, Parameters, Options)

If you pass a variable to the Execute method’s RecordsAffected argument, the argument returns the number of records affected by the action query or stored procedure. This tactic is a good way to determine if the action query or stored procedure succeeded.

You can use the Parameters argument to pass an array of parameters to the Execute method. In most cases, setting the parameter values prior to using the Execute method is better.

You can use the Options argument to tell the Execute method the type of command you are executing. The possible values are the same as for the Recordset object’s Open method, which I list in Table 3. You can also use the CommandType property of the Command object to set this value prior to using the Execute method.

All of the Execute arguments are optional.

A stored procedure example. The UpdAct.asp file creates a recordset to delete a record. In most cases, using an action query or stored procedure to perform this sort of activity is much more efficient. Another example on the CD-ROM shows you how to do this using a stored procedure. The stored procedure example on the CD-ROM uses two files:

updatesp.asp. This page displays a list of customers and lets you click on a customer to edit the associated record.

sp.asp. This page executes a stored procedure using a Command object.

Because the updatesp.asp file is almost identical to the update.asp file discussed in a previous section, I don’t present it here (but you can find the file on the CD-ROM). The only real difference in the file is that it calls sp.asp instead of updrec.asp with the following code:

<TD><A HREF="Sp.asp?CustomerId=<%= rst("CustomerId") %>">
    <%= rst("Name") %></A></TD>

The code in the sp.asp file deletes the requested record by calling the procDeleteCustomer stored procedure in the SQL Server version of the Meals database. This stored procedure accepts a single parameter, the CustomerId of the record to delete.

Following is the complete sp.asp file:

<HTML>
<HEAD>
<TITLE>Sp.asp Example</TITLE>
</HEAD>
<BODY>
<H2> Customer Record Delete </H2>
<H3> (Using a SQL Server Stored Procedure) </H3>
<%
Dim con
Dim varRecords
Const adCmdStoredProc = 4
Const adInteger = 3

' Create a Connection object and point it to
' the WebMealsSQL data source
Set con = Server.CreateObject("ADODB.Connection")
con.Open "WebMealsSQL", "WebUser", ""

' Create a Command object and link it to the
' Connection object already established
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = con

' Point the Command object to the stored
' procedure and pass the single parameter
cmd.CommandText = "procDeleteCustomer"
cmd.CommandType = adCmdStoredProc
cmd.Parameters(1) = Request("CustomerId")

' Execute the stored procedure
cmd.Execute varRecords

' If varRecords is 0 then the stored
' procedure was not executed successfully.
If varRecords >= 1 Then
  Response.Write "Record deleted!"
Else
  Response.Write "Record delete failed!"
End If

' Clean up time
con.Close
%>
<BR><A HREF="adomenu.html">Return to home page</A>
</BODY>
</HTML>

Create the Command object with this code from sp.asp:

Set cmd = Server.CreateObject("ADODB.Command")

After you create the Command object, you need to link it to a previously created Connection object with code like the following:

Set cmd.ActiveConnection = con

The next two statements identify the stored procedure:

cmd.CommandText = "procDeleteCustomer"
cmd.CommandType = adCmdStoredProc

If the stored procedure has any parameters, you can set the values of the stored procedures using the Command object’s Parameters collection. Although, the Parameters collection begins numbering with 0, SQL Server reserves the first parameter, parameter 0, for the return value of the stored procedure, even if you haven’t explicitly defined a return value. Thus, the first input parameter is parameter 1. Following is the code in sp.asp used to pass the CustomerId (from the updatesp.asp page) to the stored procedure.

cmd.Parameters(1) = Request("CustomerId")

This next statement executes the stored procedure, sticking the number of records affected by the stored procedure into the varRecords variable:

cmd.Execute varRecords

To determine whether the stored procedure worked, all you need to do is check the value of varRecords like this:

If varRecords >= 1 Then
  Response.Write "Record deleted!"
Else
  Response.Write "Record delete failed!"
End If

An action query example. Executing an Access action query in ADO is very similar to executing a stored procedure. In fact, the code is virtually identical. The updateaq.asp and aquery.asp files demonstrate how to execute an Access delete query using an ADO Command object.

The only significant difference to executing an Access action query is in how the parameters are numbered. Because Access action queries lack any return value, the first input parameter in Access queries is parameter 0, not parameter 1 as with SQL Server stored procedures.

Following is the code from aquery.asp that executes an Access delete query named qryDeleteCustomer. The query contains a single parameter.

Set con = Server.CreateObject("ADODB.Connection")
con.Open "WebMeals"

Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = con
cmd.CommandText = "qryDeleteCustomer"
cmd.CommandType = adCmdStoredProc
cmd.Parameters(0) = Request("CustomerId")
cmd.Execute varRecords

You’re done!

Paul Litwin is a senior consultant with MCW Technologies focusing on application development employing Access, Visual Basic, Visual InterDev, Microsoft Office, SQL Server, and related technologies. MCW Technologies wrote the wizards that are part of Visual InterDev. Paul has written numerous articles for publications including Smart Access, Visual Basic Programmer's Journal, and PC World. He has also written several books including the Access 97 Developer's Handbook (and two earlier editions of this book), Intranet & Web Databases for Dummies, VBA for Dummies Quick Reference, and Microsoft Access 95 How-To. Paul is currently at work on Visual InterDev Developer's Handbook. He trains developers for Application Developers Training Company and has spoken at a variety of U.S. and international conferences. You can reach Paul at plitwin@mcwtech.com.

Top


	Write us at MSOffDev@microsoft.com.
	© 1998 Microsoft Corporation. All rights reserved. Legal Notices. Last Updated: June 26, 1998
	Best experienced with Click here to start.

ADO Programming

Using the Samples

Access Examples

ASP Examples

Excel Example

Visual Basic 5 Example

ODBC, OLE DB and ADO

The ADO Object Model

Accessing Data Using ADO

Flattening the Object Model

Updating a Record in a Recordset

Deleting a Record from a Recordset

Executing Queries and Stored Procedures