Active Server Pages (ASP) provides server-side scripting for Microsoft Internet Information Server (IIS) Web servers. Microsoft introduced ASP in IIS 3.0 and carried it forward into IIS 4.0. ASP lets you use any scripting language that follows the ActiveX scripting standard, and it enables flexible and dynamic Web page creation. Typically, ASP combines HTML and embedded VBScript. IIS includes an OLE automation server that executes the VBScript and sends the script's output in HTML form to a browser-based client. ASP scripts execute on the server, so they work with any Web browser because the browser receives only a stream of HTML. Understanding how ADO objects work with ASP, and specifically, how to use ADO objects to retrieve and modify data, can make creating dynamic Web pages a breeze.
How ASP Works
Figure 1 illustrates how ASP combines HTML and ActiveX script to produce dynamic HTML. As you can see, ASP scripting is different from browser-based scripting. With traditional browser-based scripting, the Web server sends an HTML page containing the ActiveX script to the client's browser, which is responsible for executing the script. Client-based scripting places an increased burden on the client and can cause problems if a browser client can't execute a script. An ASP page, conversely, executes on the IIS Web server. While executing the page, the server directly passes the client any HTML or client scripts the ASP page contains. When the server encounters an ASP server script, it executes the script and sends to the client any output the script generates, in HTML form. The browser-based client sees no difference between the HTML stream that an ASP script creates and the HTML stream that a static Web page sends. Thus, ASP's server-side scripting essentially produces Web pages as the scripts execute. (See "ASP Scripting Basics" in the September article index at http://www .sqlmag.com.) Because ASP generates a pure HTML stream, it provides browser independence. And because IIS interprets ASP on the fly, ASP is an ideal mechanism for incorporating the results of interactive database queries in Web pages. Accessing SQL Server via ADO from ASP provides this functionality.
Web clients use HTTP and TCP/IP to communicate with the IIS Web server, either across the Internet or on a local intranet. When the Web client requests an ASP page, the IIS Web server executes the page's ASP scripts. To access a SQL Server database, the ASP script opens a connection to SQL Server with a Connection, Command, or Recordset object, then uses that object to send a data-access request to the server. SQL Server can be running on the same system as the IIS Web server. However, because usually several applications use SQL Server at once, it's sometimes more convenient to install SQL Server on a separate system and make the connection across the local network. After the SQL Server system finishes processing the requests, it sends the results back to the ADO object in the ASP script. IIS then processes the ASP script and sends an HTML stream back to the client. Therefore, a network connection must exist between the IIS Web server and the SQL Server database system. In addition, the Web server must have an OLE DB provider and the ADO runtime DLLs installed.
Using ADO Objects with ASP
Using ADO, your application's first action is to use the Connection, Command, or Recordset object to open a connection to SQL Server. You can use the Connection object to explicitly establish ADO connections, or you can use the Command or Recordset object to make ADO connections dynamically. After establishing the connection, your ASP application can issue the same sort of ADO commands that a standard Visual Basic (VB) application can perform. These commands include executing stored procedures, opening and traversing a recordset, and inserting, updating, and deleting data.
To connect to SQL Server from an ASP page, ADO can use the OLE DB provider for either ODBC or SQL Server. The OLE DB provider for ODBC lets you use the ADO object framework with most existing ODBC drivers. You can use the OLE DB provider for SQL Server only to connect to a SQL Server database. But you can use either of these OLE DB providers with the ADO Connection, Command, and Recordset objects. Listing 1 (written in VBScript) shows how to use the OLE DB provider for ODBC to establish a connection with SQL Server.
The first action in Listing 1 is a declaration of three variables that will contain the SQL Server authentication information and the name of the SQL Server system. Next, the script declares the cn variable, which it will use for the ADO Connection object. After the script declares the working variables, the ASP Request object's Form method assigns values to those variables.
Next, the ASP Server object's CreateObject method creates a new ADO Connection object and assigns that object to the cn variable that was created earlier. The CreateObject method can instantiate COM objects. This example illustrates how to create an instance of an ADODB.Connection object, but you can also use it with other COM object frameworks such as SQL-DMO or Active Directory. (For more information about ASP objects, see the sidebar "The ASP Object Model" on page 36.)
The script then assigns the cn Connection object's ConnectionString property an OLE DB connection string, which lets you establish a connection without a Data Source Name (DSN). Because you didn't specify an OLE DB provider with the PROVIDER keyword, the provider defaults to the OLE DB provider for ODBC. The DRIVER keyword identifies the driver to be used. The SERVER keyword specifies the name of the SQL Server system to connect to. The UID and PWD keywords provide the login information, and the DATABASE keyword sets the default database as Pubs. After you've assigned a connection string to the ConnectionString property, the Connection object's Open method opens a connection to the SQL Server system that the script identifies.
Retrieving Data with the ADO Recordset
You can use ADO to retrieve data with the Recordset object or the Command object. Both these objects can work with an active Connection object, or they can open separate connections. Each time an ADO Recordset or Command object establishes a connection, a new communication session with SQL Server starts. If your application needs to perform multiple operations, it's usually more efficient to use a Connection object to open the connection and then use that Connection object with your other Recordset and Command objects.
Listing 2 illustrates using the ADO Recordset object in an ASP page. The first part of this script begins much like the simple connection example in Listing 1. The script declares, then assigns, the working variables. Then the script creates an ADO Connection object, followed by an ADO Recordset object. Next, the script assigns the connection string to the ADO Connection object and calls the Open method to start the connection to SQL Server. The script sets the ActiveConnection property of the rs Recordset object to the active connection object, cn, and the Recordset object's Open method executes. The first parameter of the Open method contains a simple SQL statement that selects all the columns and rows from the stores table in the Pubs database.
After the script returns the query results, the ASP page creates an HTML table and sizes it to contain six columns. Then the script assigns the column headings. The script uses standard HTML to build all the column headings. Inside the HTML table body, at Callout A in Listing 2, a section of VBScript sets up a Do Until loop. This loop processes the contents of the rs Recordset object. When the script reaches the end of the Recordset object, the rs.EOF property becomes true, and the loop ends.
An embedded ASP script assigns the value of each column based on the column's name in the ADO Recordset object. In this example, the column name that originates from the Stores table of the Pubs database identifies each item in the Fields collection. The next portion of VBScript code performs a MoveNext method to move to the next row in the Recordset object. Then the Loop statement transfers control back to the top of the Do Until loop. When the loop has read the last row in the Recordset, the loop ends, and the rs Recordset and the cn Connection object close. You can see the results of this ASP page in Screen 1.
Modifying Data with ADO
Besides dynamically outputting Web pages, you can also use ASP and ADO to create data-entry Web pages. This capability lets you create Web-based database applications that support the same range of database capabilities that standard client/server database applications have. ADO Recordset objects that ASP pages access have the same capabilities as those that VB applications open. You can use them to insert data, as the following example demonstrates. You can also use them to update and delete data. All the other ADO capabilities, such as the ability to use prepared SQL and to execute stored procedures, are also present.
You can use the combination of ASP and ADO in several ways to modify data. For example, you can build ASP pages that support updateable ADO Recordset objects, which can use the AddNew, Update, and Delete methods to modify the data in SQL Server databases. Also, you can use ADO to perform both dynamic and prepared SQL data update operations. The code in Listing 3, page 35, illustrates how you can add rows to a Recordset object that you created using a Keyset cursor.
Listing 3 introduces additional ASP Web-building techniques. The first line is the VBScript Option Explicit statement, which ensures that you explicitly declare all the variables in the VBScript code before you use them. Like standard VB, VBScript can automatically use new variables without first declaring them. At first, this feature may seem handy, but it is really an effective way of introducing insidious and difficult-to-find bugs into your ASP applications.
Next, an #include statement includes a file named adovbs.inc. Using #include is a convenient way of copying commonly used constants into the ASP pages. In this case, the adovbs.inc file includes all the constants that the ADO object framework commonly uses. This include file lets you use constants such as adChar and adKeyset in your ADO method calls, rather than the values that these constants represent. Using constants makes your code much more readable and easier to maintain. Although the include file is good for generic development tools, if you're using Visual InterDev (VID) to develop your ASP applications, you can add a reference to the ADO Object Library in the VID development environment. This addition eliminates the need to include the adovbs.inc file in your ASP applications. You can add the ADO reference to a VID project by selecting Project, Project References from the menu and selecting the Microsoft ActiveX Data Library 2.0 entry from the list of available references.
The VBScript in Listing 3 drops, then recreates, a table in the Pubs database, inserts 50 rows into the table, and displays the contents on the Web page. But before all that, at Callout A in Listing 3, is the initialization of the VBScript error handler. In this example, I used the On Error statement to let the script bypass any errors that might result, for instance, from attempting to drop the table if it doesn't exist in the target database. I present more detailed information about using the error handler in the next section.
Next, the script assigns the ADO Connection object to the local variable, cn. The Connection object's Execute method then executes two dynamic SQL statements. The first drops the table named Department and the second creates the Department table. After creating the Department table, the script sets the ActiveConnection property of the rs Recordset object to the Connection object. Then the Recordset object's Open method creates an updateable Recordset. The constant adOpenKeyset specifies that this Recordset object will be an updateable Keyset type, and the adLockOptimistic constant determines the use of optimistic record locking.
A For Next loop inserts 50 rows into the Department table. In the For Next loop, the AddNew method creates a buffer to hold the new row, then the loop assigns the values of the ADO Field objects. Each object in the Fields collection is identified by column name. The loop sets the Dep_ID column to the value of the loop counter and assigns the Dep_Name column the literal Department combined with the string representation of the loop counter. The loop inserts the new row into the base table when the Recordset object's Update method executes.
After the script has inserted 50 rows into the Department table, the MoveFirst method repositions the cursor to the beginning of the Recordset object. Then the contents of the Recordset object display in an HTML table, using the same technique as in the earlier query examples. Screen 2, page 35, shows the Web page that running this ASP page creates.
ASP and ADO Error Handling
Trapping runtime errors is important: If your Web application generates an untrapped error, the Web application will give an error message and cease to function. You use the On Error statement to implement ASP error handling in VBScript scripts. Unfortunately, the On Error statement isn't as robust as the standard VB error handler, which lets you branch to a specific error-handling code section. The VBScript On Error statement lets you only Resume Next or disable error handling; it doesn't support branching to different sections of code. The On Error statement uses the ADO object framework to place runtime errors that your ASP Web application encounters into the ADO Errors collection. You can process the ADO Errors collection to gather additional information about errors that your application encounters. The code in Listing 4, page 36, illustrates how to use the VBScript Error handler and get information from the ADO Errors object.
The On Error statement in Listing 4 enables error handling. Next, the script uses the ADO Connection object that was stored in the ASP Session object to create a local ADO Connection object. Then the script uses the ADO Command object's Execute method with an invalid table name. Because error handling is enabled, the program flow continues to the next statement.
You can find ADO errors by checking the Count property in the Errors collection. If the count is greater than zero, the ADO object framework encountered some type of runtime error. Your application can extract the ADO error information by iterating through the Error collection. The example in Listing 4 uses a For Each loop to process all the elements in the Errors collection in the ADO Connection object. The Number, Source, and Description properties then display as HTML text output. The resulting page appears in Screen 3. (Note that this example was for demonstration only. In a production application, you'll handle error conditions in your application code and avoid displaying them in the client browser.)
For Web pages that require only static data, you can use SQL Server Web Assistant Wizard to quickly and easily copy and format SQL Server database information to an HTML Web page. For truly interactive applications that can dynamically list and update data, you can use the combination of ASP and ADO to connect your SQL Server database to the Web. With ADO and ASP, you can create Web-based applications that have the same database access capabilities as their traditional client/server counterparts.
This article was adapted from the SQL Server 7 Developer's Guide by Michael Otey and Paul Conte.