HOWTO: Intercept UpdateBatch and Call a Stored Procedure

• ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2

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 session and connect to the Pubs database on your SQL Server. 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 

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:




      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=sa;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 

REFERENCES

For additional information about Updating the WillChangeRecord, please see the following article in the Microsoft Knowledge Base:

Q189365 PRB: WillChangeRecord Event Does Not Fire on Update

Additional query words:

Keywords : kbADO kbADO200 kbDatabase kbSQLServ kbStoredProc kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC210SP2
Version : WINDOWS:2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbhowto