Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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

View products that this article applies to.

This article was previously published under Q245494

↑ Back to the top


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.

↑ Back to the top


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.

↑ Back to the top

More information

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:

Run-time error: -2147217864 (80040e38):

The specified row could not be located for updating. Some values may have been changed since it was last read.
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
        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!Weight = 11
        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
        On Error GoTo ErrorInUpdateBatch
        rs.UpdateBatch adAffectAllChapters
        Exit Sub
        Debug.Print Err, Err.Description
        rs.Filter = adFilterConflictingRecords
        Debug.Print rs.RecordCount
        Do While Not rs.EOF
            Debug.Print rs.EditMode

↑ Back to the top

Keywords: KB245494, kbpending, kbcodesnippet, kbbug

↑ Back to the top

Article Info
Article ID : 245494
Revision : 4
Created on : 11/5/2003
Published on : 11/5/2003
Exists online : False
Views : 368