UpdateBatch and CancelBatch Methods Example (VB)

UpdateBatch and CancelBatch Methods Example (VB)

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