Microsoft KB Archive/251021

From BetaArchive Wiki
Knowledge Base


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.

  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
                        


Keywords: kbhowto KB251021