Data Manipulation with ADO

ActiveX Data Objects (ADO) is a set of COM objects that provide an interface between VBScript (as well as other programming languages) and the OLE DB provider specific to Microsoft SQL Server or Microsoft Access. In "Accessing Database Data with ADO" (InstantDoc ID 99419), I provided an overview of the ADO object model and explained how to use the ADO objects to retrieve data. In this article, I describe how to use ADO to manipulate SQL Server and Access data. You’ll learn how to run action queries such as INSERT, UPDATE, and DELETE statements, call stored procedures, and use ADO recordsets to modify data. You’ll also learn how to insert XML and binary data into SQL Server and Access databases, handle ADO errors, and define transactions.

You should be knowledgeable about VBScript and familiar with general database concepts. In addition, if you don’t know how to use ADO to retrieve data, be sure to read "Accessing Database Data with ADO" first. You can also refer to the ADO API Reference at MSDN for specific details about the ADO objects and their methods and properties.

Note that for many of the examples in this article, I use the Production.Inventory table, which I created in the AdventureWorks sample database. Callout A of Listing 1 shows the Transact-SQL statement I used to create this table.

Using Action Queries to Update Data
When using ADO to modify data, you can take several different approaches. The first method is the simplest: passing an action query to the database.

An easy way to run an action query is to create a Connection object and use that object’s Execute method to run the SQL statement against the database. In Listing 2, I first create and open a Connection object. (If you run this script or any of the other example scripts, be sure to change the data source name to the correct server where you've installed SQL Server.) After I define the Connection object, I define the command text and assign it to the ctInventory variable. In this case, the command text is a parameterized Update statement in which I use the ProdID and ProdSold variables to pass in values. I assigned default values of 1 to each variable to make testing the script simpler.

After I define the command text, I call the Connection object’s Execute method. The Execute method is similar to calling the Open method of a Recordset object.) I include three arguments with the Execute method. The first argument is the connection string (csSqlServer). The second is a variable that will hold the number of rows updated after the SQL statement runs. The third argument is a combination of two options connected with a plus (+) sign. The first option, adCmdExecuteNoRecords, tells ADO to run the SQL statement without returning a recordset (which it does by default, even if no results are returned). The second option, adCmdText, indicates that command text will be used for the command and not something such as a stored procedure or table name. After you call the Execute method, you can then use the RowsUpdated variable to display the number of rows that have been updated. If you plan to run this script, it's always a good idea to check the value in the table both before and after you run the script to verify the change. You can use SQL Server Management Studio to quickly verify that the change has been made correctly.

That’s all there is to running an action query against the SQL Server database, and the procedure is no different to run an action query against an Access database. The primary difference is in how you define the connection string that you pass to the Connection object. Listing 3 shows a script I created to update data in an Access database. Notice that it's nearly identical to Listing 2. The only difference besides the connection string is the connection text—the name of the table in the Access database is Inventory, not Production.Inventory.

Because the methods you use to update Access data are the same as those to update SQL Server data, the remaining examples modify only SQL Server data. If you want to try them on Access, simply modify the connection string and the command text, if necessary, and you’ll be ready to go. However, note that for this example, I connected to an Access database that was in the pre–Access 2007 format (.mdb extension). If you want to connect to a database in the Access 2007 format (.accdb extension), you need to modify the connection string as follows:

' Define connection string
 csAccess = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
 "Data Source=C:InfoInventory.accdb;" 

Now let’s take a look at how you call a stored procedure.

Using Stored Procedures to Update Data
One method you can use to call a stored procedure is to take the same approach you take to running an action query. However, instead of using an UPDATE, DELETE, or INSERT statement in the command text, you use an EXECUTE statement, as Listing 4 shows. In this case, I call the DecreaseInventory stored procedure; see Callout B of Listing 1 for the stored procedure definition. Except for the command text, the script in Listing 4 is the same as in Listing 2.

The approach is a little different for an Access database. Instead of calling a stored procedure, you call a stored query. However, the ADO connection text and Execute method in VBScript are the same. The difference is in Access itself. Figure 1 shows the DecreaseInventory query in Access as it appears in design view. When you call this query, it decreases the inventory amount just like the DecreaseInventory stored procedure in SQL Server. Notice in the query shown in Figure 1 that the Quantity field shows the [Quantity]-[Amount] expression in the Update To row, which indicates that the value in the Quantity column will be deleted by the value specified in Amount. And the Criteria row in the ProductID field includes the expression [ProdID], which means that the ProductID value must match the value in ProdID. For information about creating a query in Access 2007, see the Access Help topic “Create a simple select query” or go to the Office website.

You can also use a Command object to run a SQL Server stored procedure (or an Access stored query) without needing to define variables or an EXECUTE statement. If you refer to Listing 5, you’ll see that you first create and define a Connection object and create a Command object. Next, you define the Command object properties. The first property is ActiveConnection, which you set to the Connection object. For the CommandText property, specify only the name of the stored procedure (or stored query), rather than the entire SQL statement. Next, set the CommandType property to adCmdStoredProc, which tells ADO to treat the command text as the name of a stored procedure. ADO automatically generates the necessary T-SQL command.

If your stored procedure requires parameter values, you can create Parameter objects to pass those values into the stored procedure. To create a parameter, use the Append method of the Command object’s Parameters collection. As an argument to the Append method, specify the Command object’s CreateParameter method and include the parameter name (preceded by the @ symbol) and the data type, which in this case is adInteger. Repeat this process for each parameter. You can then use the Parameters collection to assign a default value to the parameters. In this example, I assign 1 to each parameter.

After you’ve defined the properties for the Command object, you can use the object’s Execute method to run the stored procedure. If the stored procedure doesn't return a recordset, as in the case of the DecreaseInventory stored procedure, you should include two arguments with the Execute method. The first argument is the RowsUpdated variable, which holds the number of rows returned, followed by the adCmdExecuteNoRecords option, which indicates that ADO should not return a Recordset object. This option is actually the third argument of the Execute method, which is why you need to include the extra comma when you call the method.

Using the Recordset Object to Update Data
When you use an action query or stored procedure to modify data, you update the database as soon as you execute the command text. However, there’s another approach you can take to updating data: modifying the recordset.

When you modify data through the recordset, you first retrieve a recordset, then modify the data in the recordset, and finally propagate those changes back to the database. Listing 6 shows you how this can be done. The first thing to note is that the command text simply retrieves data. It doesn't modify data. For this example, I plan to update the Quantity column, so that’s one of the columns I retrieve. I also retrieve the ProductID column because that column uniquely identifies each row, and I'll use that value to identify which record to update.

After you define your command text and create your Recordset object, set your CursorLocation property to adUseClient. Although you can use server-side cursors, client-side cursors scale better and let you take better advantage of properties and methods such as Sort, Filter, and Find.

Next, open the Recordset object. When you call the Open method, specify the command, connection, and cursor type, as you’ve seen in previous examples.

After you specify the cursor type, specify the lock type, which in this case is adLockOptimistic. When you specify adLockOptimistic, the data source isn't locked until you actually perform the update. If two users try to update the same data simultaneously, the first one to commit the changes will succeed and the second one will fail. Be sure that your code includes the logic necessary to handle failed modification attempts.

Now you’re ready to actually modify the data. To do this, use the Recordset object’s Find method to locate the record you want to update, then use the object’s Fields collection to set the values of specific fields for that record. For example, in Listing 6, I subtract the amount passed through the ProdSold variable from the Quantity field. After I set all the fields I want to set, I use the Recordset object’s Update method to propagate the modified field information to the database.

By using the Recordset object to update data, you don’t have to write as much SQL code as you do with action queries, and you don’t have to maintain both client-side and server-side code as you do with stored procedures. On the other hand, some systems require that all updates be done through stored procedures, and in some cases, a simple action query is all you need to get the job done. As a result, the decision to use action queries, stored procedures, or recordset updates depends on the type of application you’re building and the business rules that govern that application.

Modifying Large Text and Binary Fields
You can use a Stream object to retrieve data from columns that hold large amounts of data, such as XML and varbinary columns. Now let’s take a look at a couple examples that demonstrate how to insert data into those columns. For these examples, I used the SQL statement in Callout C of Listing 1 to add XML and varbinary columns to the Production.Inventory table.

The code in Listing 7 uses a Stream object to insert data into the Model column, which is defined with an XML data type. The source data is the Road250Model.xml file, which I generated by extracting data from the Instructions column of the Production.ProductModel table. If you run this script, be sure to change the path and file name to the correct XML file. For this script, I first define the connection string and then the command text. Next, I create a Recordset object, set the cursor location, and use the object’s Open method to populate the recordset. When I call the method, I specify the same options that I used in Listing 6, except that I reference the connection string, rather than a connection object.

The next step is to create the Stream object and set the object’s Type property. For XML data and other types of text data, use the adTypeText constant. After you set the type, open the Stream object and then call the LoadFromFile method and specify the path name of the source file. Next, set the value of the Model column by calling the ReadText method, which updates the value within the recordset. To propagate that value to the database, call the Update method.

The process of inserting binary data is nearly identical to inserting XML data. The primary difference, shown in Listing 8, is that you must set the Stream object’s Type property to adTypeBinary, rather than adTypeText. And of course, you must also provide the correct file name. For this example, I used Road250Photo.gif. However, if you run this script, be sure to change the path and file name to the correct .gif file. But these are the only differences. (If you’re inserting XML and binary data into an Access database, the column types will be Memo and OLE Object, respectively.)

Handling ADO Errors
The ADO Connection object supports the Errors collection, which is made up of a set of Error objects that each return information about a specific error, if an error is generated. You can use the Errors collection and an Error object to verify whether an error has been generated and, if so, access the error information. Let’s take a look at an example to demonstrate how this works.

In Listing 9, I create a Connection object and define an action query to update the Inventory table. To capture any errors that might be generated by this update, I also create an Error object and assign it to the errADO variable. I also include an On Error statement so I can catch any errors that might occur.

After I call the Connection object’s Execute method, I use an If statement to test whether the Errors collection contains any Error objects—in other words, I test whether any errors have been generated. I do this by using the Errors collection’s Count property and check whether the value is greater than 0. I access the Errors collection by calling the Connection object (conSqlServer.Errors.Count).

If the Count property is greater than 0, I display a message box for each Error object (errADO) in the Errors collection. In the example shown in Listing 9, I display the Error object’s Description and Number properties. If the Count property isn't greater than 0, I display the number of rows updated.

That’s all there is to capturing ADO errors. To test the script, I dropped the Inventory table, ran the script, and received the expected error. Then I re-created the table and tested the script again, and this time received no error. ADO error messages are sometimes not particularly informative. However, they will at least usually point you in the right direction if you’re trying to track down a problem.

Implementing Transactions
In addition to supporting the Errors collection, the Connection object provides several methods that let you implement transactions within your script. You can use the BeginTrans method to start a transaction, the Rollback method to roll back a transaction, and the CommitTrans method to commit a transaction. Listing 10 shows how all three of these methods can be used.

For this example, I created a second table—Production.Inventory2—that's identical to the Production.Inventory table, except for the name. To create this table, all you have to do is add the number 2 to the table name in the table definition in Listing 1. The script attempts to update the Inventory and Inventory2 tables. I did this so that I could test various scenarios in order to verify that the transaction would roll back correctly. For example, I deleted the Inventory table and then ran the script. Next, I re-created the table, deleted the Inventory2 table, and reran the script. In each case, the transaction rolled back as expected, and when I re-created the Inventory2 table, the transaction committed as expected. When the transaction committed, the ProdSold value (the default value of 1) was deducted from the Quantity column in each table. Now let’s take a look at the script itself.

In this script, I want to ensure that either both tables are updated or neither table is updated. To achieve this, I enclose both update attempts within a single transaction. To implement the transaction, I first call the Connection object’s BeginTrans method. You must start the transaction before you execute your command text. After I start the transaction, I define the connection text necessary to update the Inventory and Inventory2 tables and assign the text to the ctInventory and ctInventory2 variables, respectively. I then call the Connection object’s Execute method to run the connection text that updates the Inventory table. Next, I use an If statement to check for errors (as you saw in Listing 9). If an error has occurred, I call the RollbackTrans method to roll back the transaction, and I display the error messages. If there are no errors updating the Inventory table, I execute the ctInventory2 command text and run a second If statementto again check for errors. If an error occurs, I roll back the transaction and display the error messages. If no error occurs, I call the CommitTrans method to commit the updates to both databases and display the number of rows updated in both tables. As you can see, by including both updates within a single transaction, no changes are committed to the database unless both update attempts are successful. In other words, I'm ensuring that either both tables are updated or neither table is updated.

What Next?
After reading this article and "Accessing Database Data with ADO," you should have a good foundation in how to use ADO to retrieve and modify SQL Server and Access data. Again, be sure to refer to the ADO API Reference at MSDN for additional information about ADO. The reference also includes code samples that demonstrate how to implement the ADO objects. The more you use ADO, the better you’ll be able to build on what you’ve learned in these articles as well as take advantage of other ADO features, particularly the rich set of methods and properties that each object supports.