How To Update Stored Procedures with Joined Tables Using ADO (251021)

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.
2. 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.

1. 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
   					

2. Using Visual Basic 5.0 or 6.0, create a Standard EXE project.
3. From the Project menu, select References, and then add a reference to the Microsoft ActiveX Data Objects 2.x Library.
4. 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
   					

5. Change the connect string to match your environment.
6. 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.
7. Reset the project and uncomment the Unique Table and Resync Command lines in the code.
8. Re-run the project and note the following output:

   Alfreds Futterkiste
   Wolski  Zajazd
   Blauer See Delikatessen