Detecting and Resolving Conflicts

Detecting and Resolving Conflicts

If you are dealing with your Recordset in immediate mode, there is much less chance for concurrency problems to arise. On the other hand, if your application uses batch mode updating, there may be a good chance that one user will change a record before changes made by another user editing the same record are saved. In such a case, you will want your application to gracefully handle the conflict. It may be your wish that the last person to send an update to the server "wins." Or you may want to let the most recent user to decide which update should take precedence by providing him with a choice between the two conflicting values.

Whatever the case, ADO provides the UnderlyingValue and OriginalValue properties of the Field object in order to handle these types of conflicts. Use these properties in combination with the Resync method and Filter property of the Recordset.

Detecting Errors

When ADO encounters a conflict during a batch update, a warning will be placed in the Errors collection. Therefore, you should always check for errors immediately after calling BatchUpdate, and if you find them, begin testing the assumption that you have encountered a conflict. The first step is to set the Filter property on the Recordset equal to adFilterConflictingRecords (the Filter property is discussed in the preceding chapter). This limits the view on your Recordset to only those records that are in conflict. If the RecordCount property is equal to zero after this step, you know the error was raised by something other than a conflict.

When you call BatchUpdate, ADO and the provider are generating SQL statements to perform updates on the data source. Remember that certain data sources have limitations on which types of columns can be used in a WHERE clause.

Next, call the Resync method on the Recordset with the AffectRecords argument set equal to adAffectGroup and the ResyncValues argument set equal to adResyncUnderlyingValues. The Resync method refreshes the data in the current Recordset object from the underlying database. By using adAffectGroup, you are ensuring that only the records visible with the current filter setting, that is, only the conflicting records, are resynchronized with the database. This could make a significant performance difference if you are dealing with a large Recordset. By setting the ResyncValues argument to adResyncUnderlyingValues when calling Resync, you ensure that the UnderlyingValue property will contain the (conflicting) value from the database, that the Value property will maintain the value entered by the user, and that the OriginalValue property will hold the original value for the field (the value it had before the last successful UpdateBatch call was made). You can then use these values to resolve the conflict programmatically or require the user to choose the value that will be used.

This technique is shown in the code example below. The example artificially creates a conflict by using a separate Recordset to change a value in the underlying table before UpdateBatch is called.

'BeginConflicts
    On Error GoTo ErrHandler:
    
    Dim objRs1 As New ADODB.Recordset
    Dim objRs2 As New ADODB.Recordset
    Dim strSQL As String
    Dim strMsg As String
    
    strSQL = "SELECT * FROM Shippers WHERE ShipperID = 2"
                 
    'Open Rs and change a value
    objRs1.CursorLocation = adUseClient
    objRs1.Open strSQL, strConn, adOpenStatic, adLockBatchOptimistic, adCmdText
    objRs1("Phone") = "(111) 555-1111"
    
    'Introduce a conflict at the db...
    objRs2.Open strSQL, strConn, adOpenKeyset, adLockOptimistic, adCmdText
    objRs2("Phone") = "(999) 555-9999"
    objRs2.Update
    objRs2.Close
    Set objRs2 = Nothing
    
    On Error Resume Next
    objRs1.UpdateBatch
    
    If objRs1.ActiveConnection.Errors.Count <> 0 Then
        Dim intConflicts As Integer
        
        intConflicts = 0
        
        objRs1.Filter = adFilterConflictingRecords
        
        intConflicts = objRs1.RecordCount
        
        'Resync so we can see the UnderlyingValue and offer user a choice.
        'This sample only displays all three values and resets to original.
        objRs1.Resync adAffectGroup, adResyncUnderlyingValues
        
        If intConflicts > 0 Then
            strMsg = "A conflict occurred with updates for " & intConflicts & _
                     " record(s)." & vbCrLf & "The values will be restored" & _
                     " to their original values." & vbCrLf & vbCrLf
                     
            objRs1.MoveFirst
            While Not objRs1.EOF
                strMsg = strMsg & "SHIPPER = " & objRs1("CompanyName") & vbCrLf
                strMsg = strMsg & "Value = " & objRs1("Phone").Value & vbCrLf
                strMsg = strMsg & "UnderlyingValue = " & _
                                   objRs1("Phone").UnderlyingValue & vbCrLf
                strMsg = strMsg & "OriginalValue = " & _
                                   objRs1("Phone").OriginalValue & vbCrLf
                strMsg = strMsg & vbCrLf & "Original value has been restored."
                  
                MsgBox strMsg, vbOKOnly, _
                      "Conflict " & objRs1.AbsolutePosition & _
                      " of " & intConflicts
                  
                objRs1("Phone").Value = objRs1("Phone").OriginalValue
                objRs1.MoveNext
            Wend
            
            objRs1.UpdateBatch adAffectGroup
        Else
            'Other error occurred. Minimal handling in this example.
             strMsg = "Errors occurred during the update. " & _
                        objRs1.ActiveConnection.Errors(0).Number & " " & _
                        objRs1.ActiveConnection.Errors(0).Description
        End If
        
        On Error GoTo 0
    End If
    
    objRs1.MoveFirst
    
    'Clean up
    objRs1.Close
    Set objRs1 = Nothing
    Exit Sub
    
ErrHandler:
   
    If Not objRs1 Is Nothing Then
        If objRs1.State = adStateOpen Then objRs1.Close
        Set objRs1 = Nothing
    End If
    
    If Not objRs2 Is Nothing Then
        If objRs2.State = adStateOpen Then objRs2.Close
        Set objRs2 = Nothing
    End If
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
    
'EndConflicts

You can use the Status property of the current Record or of a specific Field to determine what kind of a conflict has occurred.

For more detailed information on error handling, see Chapter 6: Error Handling.