Microsoft KB Archive/191793

= How To Intercept UpdateBatch and Call a Stored Procedure =

Article ID: 191793

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.0
 * 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 Q191793



SUMMARY
It may be desirable to call a stored procedure to insert data rather than having the client-batch cursor do straight Inserts by means of the UpdateBatch method.

The WillChangeRecord event provides a place to intercept pending changes.



MORE INFORMATION
The following code demonstrates how to use the WillChangeRecord event to intercept pending batch updates and how to call a custom stored procedure instead.

Before running the code you need to create the stored procedure that is used in the code sample. You can do this by opening an ISQL/w or Query Analyzer session to your SQL Server, and choosing Pubs as the active database. Cut and paste the following code into the Query window and execute it: CREATE PROCEDURE UpdateAuthor

@id   id, @lname CHAR(40), @fname   CHAR(20) as

UPDATE Authors SET au_fname = @fname, au_lname = @lname WHERE au_id = @id go Use the following steps to run the Visual Basic code:  Open Visual Basic and create a Standard.exe. Make a Project Reference to the Microsoft ActiveX Data Objects Library.  In the code window for the default form, paste the following code, then modify the server name to your SQL Server:

Note You must change UID= and PWD= to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database. Dim WithEvents cnPubs As ADODB.Connection Dim WithEvents rsAuthors As ADODB.Recordset

Private Sub Form_Load Dim strConn As String Dim strSQL As String

' Please correct the strConn string to reflect your server name.

strConn = "Provider=MSDASQL" & _ ";Driver={SQL Server};" & _ "Server= " & _ ";Database=pubs" & _ ";UID= ;PWD= ;"

strSQL = "SELECT * FROM Authors"

Set cnPubs = New ADODB.Connection With cnPubs .CursorLocation = adUseClient .Open strConn End With

Set rsAuthors = New ADODB.Recordset With rsAuthors .Open strSQL, _ cnPubs, _ adOpenStatic, _ adLockBatchOptimistic, _ adCmdText

.MoveFirst Debug.Print "Values prior to editing." Debug.Print !Au_FName & " " & !Au_LName .MoveNext Debug.Print !Au_FName & " " & !Au_LName .MoveFirst

'Edit some rows to build some pending changes. !Au_LName = "White" !Au_FName = "Barry" .Update

.MoveNext

!Au_LName = "Green" !Au_FName = "Al" .Update

.MoveFirst Debug.Print "Values after editing and prior to UpdateBatch." Debug.Print !Au_FName & " " & !Au_LName .MoveNext Debug.Print !Au_FName & " " & !Au_LName .MoveFirst

.UpdateBatch

.MoveFirst Debug.Print "Values after UpdateBatch." Debug.Print !Au_FName & " " & !Au_LName .MoveNext Debug.Print !Au_FName & " " & !Au_LName

.Close End With End Sub

Private Sub rsAuthors_WillChangeRecord _ (ByVal adReason As ADODB.EventReasonEnum, _            ByVal cRecords As Long, _             adStatus As ADODB.EventStatusEnum, _             ByVal pRecordset As ADODB.Recordset)

Dim strSQL As String

'This event fires for every row of data with pending changes.

If adReason = adRsnUpdate Then With pRecordset

'Build SQL statement to call stored procedure with new values. 'The new values come to the event via the passed recordset. 'This recordset is a filtered and cloned (linked) copy of the 'the original.

strSQL = "{CALL UpdateAuthor " & _ "('" & !Au_ID & "', " & _                    "'" & !Au_LName & "' " & _                     ", '" & !Au_FName & "')}"

'Call the stored procedure

.ActiveConnection.Execute strSQL,, _ adCmdText + adExecuteNoRecords

'Do not set the status of event to Cancel, this generates 'an error. Instead, call CancelBatch for the current row. This 'reverts the users modifications, thus the resynch call. 'NOTE: both CancelBatch and Resynch fire events.

.CancelBatch adAffectCurrent

'This is call resynchs the row after the revert. 'Do not use the Requery method.

.Resync adAffectCurrent End With End If      End Sub 

