SQL Server Magazine

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:\Info\Inventory.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.

   Prev. page   [1] 2     next page