SQL Serv 2000 + ReSync Method

[JMvVliet]

Hello all,

Do you have had the same problem with resynchronising a row in an ADO recordset with SQL Server 2000, especially when your recordset is build from more than one table? Here is how to do it:

Create two stored procedures in SQL Server:

CREATE PROCEDURE pointiest AS
SELECT Orders.*, Customers.CompanyName
FROM Orders INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID

and the second:

CREATE PROCEDURE spJoinResync
@OID Int
AS
SELECT Orders.*, Customers.CompanyName
FROM Orders INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
WHERE Orders.OrderID = @OID

Then, have a form with a commandbutton named 'Command1', and paste the following code in your project:

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

When running the project you will not see the updates the resync should have done. Uncomment the rs.Properties lines, and you'll see the resync will work correctly. With uncommented lines, the AddNew method can fail.

Enjoy!!!