MDAC 2.5 SDK - ADO

UpdateBatch and CancelBatch Methods Example (VB)

See Also

This example demonstrates the UpdateBatch method in conjunction with the CancelBatch method.

Public Sub UpdateBatchX()

   Dim rstTitles As ADODB.Recordset
   Dim strCnn As String
   Dim strTitle As String
   Dim strMessage As String

   ' Assign connection string to variable.
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "

   Set rstTitles = New ADODB.Recordset
   rstTitles.CursorType = adOpenKeyset
   rstTitles.LockType = adLockBatchOptimistic
   rstTitles.Open "Titles", strCnn, , , adCmdTable
   
   rstTitles.MoveFirst

   ' Loop through recordset and ask user if she wants 
   ' to change the type for a specified title.
   Do Until rstTitles.EOF
      If Trim(rstTitles!Type) = "psychology" Then
         strTitle = rstTitles!Title
         strMessage = "Title: " & strTitle & vbCr & _
            "Change type to self help?"

         If MsgBox(strMessage, vbYesNo) = vbYes Then
            rstTitles!Type = "self_help"
         End If
      End If

      rstTitles.MoveNext
   Loop

   ' Ask the user if she wants to commit to all the 
   ' changes made above.
   If MsgBox("Save all changes?", vbYesNo) = vbYes Then
      rstTitles.UpdateBatch
   Else
      rstTitles.CancelBatch
   End If

   ' Print current data in recordset.
   rstTitles.Requery
   rstTitles.MoveFirst
   Do While Not rstTitles.EOF
      Debug.Print rstTitles!Title & " - " & rstTitles!Type
      rstTitles.MoveNext
   Loop

   ' Restore original values because this is a demonstration.
   rstTitles.MoveFirst
   Do Until rstTitles.EOF
      If Trim(rstTitles!Type) = "self_help" Then
         rstTitles!Type = "psychology"
      End If
      rstTitles.MoveNext
   Loop
   rstTitles.UpdateBatch

   rstTitles.Close

End Sub