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.