Microsoft KB Archive/251021

= How To Update Stored Procedures with Joined Tables Using ADO =

Article ID: 251021

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft Visual Basic 5.0 Professional Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q251021



SUMMARY
When you use client-side cursors and a command with joined tables, the foreign table fields do not get updated after you change the foreign key value. This article demonstrates how to use the UNIQUE TABLE and RESYNC COMMAND properties to achieve the desired results when the command is a stored procedure.



MORE INFORMATION
NOTE: The behavior discussed below does not apply to using server-side cursors.

When using client-side cursors, the default behavior of the Resync method is to generate a SELECT statement against each of the base tables in a query in order to select the current values. Microsoft ActiveX Data Objects (ADO) caches the primary key value of the record from each of the base tables, so if you change the foreign key of the main table, ADO still fetches data from the old foreign table record instead of the record that the new foreign key points to.

ADO 2.1 introduces two properties that you can use to modify the default behavior. With a straight SELECT statement, you can use the UNIQUE TABLE property to indicate which table is the main table. When you change the foreign key fields in this table, ADO knows to fetch data using the original command and the primary key value of the UNIQUE TABLE instead of using the cached primary key values for each individual table.

The RESYNC COMMAND property allows you to override the dynamically generated SQL statements that ADO uses to update the current row. This is especially important if your policy is to make all data requests through stored procedures or views and not allow direct table queries.

The sample code below uses the Microsoft SQL Server Northwind database to illustrate the use of these properties. You should modify the code to suit your back-end database.

The sample uses two stored procedures:
 * 1) spJoinTest This stored procedure is used by the Recordset to select records from two tables using a Join.

spJoinResync This stored procedure is used by the Recordset for purposes of fetching the current record values when the Resync method is issued. It accepts a parameter for the primary key value of the UNIQUE TABLE property.
 * 1) spJoinResync This stored procedure is used by the Recordset for purposes of fetching the current record values when the Resync method is issued. It accepts a parameter for the primary key value of the UNIQUE TABLE property.

The stored procedures return records from the Orders and Customers tables. Normally, updating the CustomerID in the Orders table does not cause the Customers table fields, such as CompanyName, to be updated when the Resync method is issued. By using the UNIQUE TABLEand RESYNC COMMAND properties, the fields for the new company are fetched properly.   Use the Microsoft SQL Server Enterprise Manager or other tool to add the following stored procedures to the Northwind sample database: CREATE PROCEDURE pointiest AS SELECT Orders.*, Customers.CompanyName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID

CREATE PROCEDURE spJoinResync @OID Int AS SELECT Orders.*, Customers.CompanyName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Orders.OrderID = @OID  Using Visual Basic 5.0 or 6.0, create a Standard EXE project. From the Project menu, select References, and then add a reference to the Microsoft ActiveX Data Objects 2.x Library.  Add a Command button (Command1) and the following code to the default form: Option Explicit

Private Sub Command1_Click Dim cn As ADODB.Connection, rs As ADODB.Recordset Set cn = New ADODB.Connection Set rs = New ADODB.Recordset cn.Open "Provider=SQLOLEDB;Server=xxx;uid=sa;pwd=;database=Northwind" rs.CursorLocation = adUseClient rs.Open "spJoinTest", cn, adOpenStatic, adLockOptimistic, adCmdStoredProc ' rs.Properties("Unique Table") = "Orders" ' rs.Properties("Resync Command") = "EXEC spJoinResync ?" rs!CustomerID = "ALFKI" rs.Update rs.Resync adAffectCurrent Debug.Print rs!CompanyName rs!CustomerID = "WOLZA" rs.Update rs.Resync adAffectCurrent Debug.Print rs!CompanyName rs.AddNew rs!EmployeeID = 1 rs!CustomerID = "BLAUS" rs.Update rs.Resync Debug.Print rs!CompanyName rs.Close cn.Close End Sub  Change the connect string to match your environment. Run the application and click the Command button. Note that the same CompanyName value gets printed for both Updates. The Resync method also generates the following error after the AddNew:

Run-time error '-2147217885 (80040e23)'

Key value for this row was changed or deleted at the data store. The local row is now deleted.

 Reset the project and uncomment the Unique Table and Resync Command lines in the code.</li>  Re-run the project and note the following output: Alfreds Futterkiste Wolski Zajazd Blauer See Delikatessen </li></ol>

Keywords: kbhowto KB251021

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.