BUG: adFilterConflictingRecords Does Not Filter All Conflicting Records When You Connect Through SQL Server ODBC Driver

In Microsoft Visual Basic ActiveX Data Objects (ADO), when connecting through the SQL Server ODBC Driver, using adFilterConflictingRecords does not filter all the conflicting records as expected.

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

To work around this problem, use the Microsoft OLE DB Provider for SQL Server instead.

The following Visual Basic repro case opens a recordset on a connection through SQL Server ODBC Driver. Changes are made to the first three records. Before calling UpdateBatch, the same rows are also modified through the second connection; this is to generate conflict errors. Upon UpdateBatch, an appropriate conflict error appears: However, setting adFilterConflictingRecords shows only one record is filtered as conflicting. If connecting through SQL OLE DB Provider, this case works as expected, that is, all three conflicting records are filtered and all three are still in edit status pending (adEditInProgress).

Steps to Reproduce Behavior

1. Start a new project in Visual Basic and select "Standard EXE". Form1 is created by default.
2. In the Visual Basic project, add a reference the Microsoft ActiveX Data Objects 2.5 (or later) Library.
3. Double click Form1. Copy and paste the following code in the Form_Load() event:
   
   Note You must change User ID=<User ID> and password=<Strong Password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

       Dim cn1 As New adodb.Connection
       Dim cn2 As New adodb.Connection
       Dim rs As New adodb.Recordset
       Dim strTable As String
       Dim i As Integer
       
       strTable = "MyTestTable"
       cn1.CursorLocation = adUseClient
       cn1.Open "PROVIDER=MSDASQL;DRIVER=SQL Server;SERVER=MyServer;UID=<User ID>;PWD=<Strong Password>;DATABASE=Pubs"
   
      'The following connection via Microsoft OLE DB Provider
      'for SQL Server does not have the problem discussed here
      'cn1.Open "PROVIDER=SQLOLEDB;DATA SOURCE=MyServer;USER ID=<User ID>;PASSWORD=<Strong Password>;INITIAL CATALOG=Pubs"
   
       cn2.Open "PROVIDER=MSDASQL;DRIVER=SQL Server;SERVER=MyServer;UID=<User ID>;PWD=<Strong Password>;DATABASE=Pubs"
       
       On Error Resume Next
       cn1.Execute "Drop Table " & strTable
       On Error GoTo 0
       
       cn1.Execute "Create Table " & strTable & " (ID int Primary Key, Weight int)"
       For i% = 1 To 5
           cn1.Execute "Insert Into " & strTable & " Values (" & i% & "," & i% & ")"
       Next i%
       rs.Open strTable, cn1, adOpenStatic, adLockBatchOptimistic, adCmdTable
   
     ' Disconnect Recordset
       Set rs.ActiveConnection = Nothing
       rs!Weight = 10
       rs.MoveNext
       rs!Weight = 11
       rs.MoveNext
       rs!Weight = 12
   
       'Similate a change by another user by updating data through
       'the second connection
       cn2.Execute "Update " & strTable & " Set Weight = 100 Where ID < 4"
       
       Set rs.ActiveConnection = cn1
       rs.MoveFirst
       
       On Error GoTo ErrorInUpdateBatch
       rs.UpdateBatch adAffectAllChapters
       rs.Close
       Exit Sub
   ErrorInUpdateBatch:
       Debug.Print Err, Err.Description
       rs.Filter = adFilterConflictingRecords
       Debug.Print rs.RecordCount
       Do While Not rs.EOF
           Debug.Print rs.EditMode
           rs.MoveNext
       Loop