Running an Optimistic Batch Query

See Also

RDO

The following code demonstrates a query that can be used to drive a subsequent "optimistic batch update" operation. In this case, you fetch a resultset using the ClientBatch cursor library and save the bookmarks for each row fetched. When the user chooses a row in the grid (where the rows are displayed), the code prompts the user for a new value and writes it to the resultset. The changes are not made to the data, however, until you perform the BatchUpdate method.

Private Sub BatchOpsButton_Click()
   Dim rs As rdoResultset
   sql = "Select * from Authors where year_born is null"
   rdoEnvironment.CursorDriver = rdUseClientBatch
   cnB.QueryTimeout = 45
   Set rs = cnB.OpenResultset(sql, rdOpenStatic, rdConcurBatch)
   rs.MoveLast: rs.MoveFirst
   ReDim bms(rs.RowCount + 1) As Variant
   Do Until rs.EOF
      bms(i) = rs.Bookmark
      i = i + 1
      rs.MoveNext
   Loop
   rs.MoveFirst
   rdoGrid1.ShowData rs
End Sub

Performing an Update Operation Based on User Input

The following code demonstrates how to gather user input (in this case, author age), and then use this information to update the database in a batch update operation:

Private Sub rdoGrid1_Click()
   Dim rs As rdoResultset
   Dim NewValue As Integer
   NewValue = InputBox("Enter new age -- 1900 to 1997", "Author Age", _
    "1960")
   rs.Bookmark = bms(rdoGrid1.Row)
   rs.Edit
   rs!Year_Born = NewValue
   rs.Update
   Changes = Changes + 1
   i = MsgBox("Commit all " & Changes & " changes?", vbYesNoCancel)
   Select Case i
      Case vbYes
         rs.BatchUpdate
         Changes = 0
      Case vbNo
         Exit Sub
      Case vbCancel
         Changes = 0
         i = MsgBox("Cancel just this change (Yes) or all " & Changes & _
          " made so far (No)?", vbYesNo)
         If i = vbYes Then
            rs.CancelBatch (True)
         Else
            rs.CancelBatch
         End If
   End Select
End Sub

ADO

In this batch operation, note that the routine used to change the chosen row in the R/W resultset doesn't require starting an "Edit" session. To achieve the same effect as the previous RDO example, simply change the contents of a field and use the Update method to make the changes to the database.

Private Sub BatchOpsButton_Click()
   sql = "Select * from Authors where year_born is null"
   rs.Open sql, cnB, adOpenStatic, adLockBatchOptimistic
   rs.MoveLast: rs.MoveFirst
   ReDim bms(rs.RecordCount + 1) As Variant
   Do Until rs.EOF
      bms(i) = rs.Bookmark
      i = i + 1
      rs.MoveNext
   Loop
   rs.MoveFirst
   ADOGrid1.ShowData rs
   rs.Close
End Sub

Private Sub ADOGrid1_Click()
   Dim NewValue As Integer
   NewValue = InputBox("Enter new age -- 1900 to 1997", "Author Age", _
    "1960")
   rs.Bookmark = bms(ADOGrid1.Row)
   rs!Year_Born = NewValue
   rs.Update
   Changes = Changes + 1
   i = MsgBox("Commit all " & Changes & " changes?", vbYesNoCancel)
   Select Case i
      Case vbYes
         rs.UpdateBatch
         Changes = 0
      Case vbNo
         Exit Sub
      Case vbCancel
         Changes = 0
         i = MsgBox("Cancel just this change (Yes) or all " & Changes & _
          " made so far (No)?", vbYesNo)
         If i = vbYes Then
            rs.CancelBatch (True)
         Else
            rs.CancelBatch
         End If
   End Select
End Sub

Note   In ADO2, you can use a batch filter to see rows that conflicted with an update rather than iterating through an array of conflicted rows, as was required in RDO2.