How To Intercept UpdateBatch and Call a Stored Procedure

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:

1. Open Visual Basic and create a Standard.exe.
2. Make a Project Reference to the Microsoft ActiveX Data Objects Library.
3. 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=<username> and PWD=<strong password> 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=<your server>" & _
                   ";Database=pubs" & _
                   ";UID=<user name>;PWD=<strong password>;"
   
         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