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.
End of Article
Prev. page
1
[2]
next page -->
