SQL Serv 2000 + ReSync Method
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:
VB Code:
CREATE PROCEDURE pointiest AS
SELECT Orders.*, Customers.CompanyName
FROM Orders INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
and the second:
VB Code:
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:
VB Code:
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!!!