Microsoft KB Archive/245494

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

Article ID: 245494

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.1 Service Pack 1
 * Microsoft Data Access Components 2.1 Service Pack 2
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q245494



SYMPTOMS
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.



STATUS
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.



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
 Start a new project in Visual Basic and select "Standard EXE". Form1 is created by default. In the Visual Basic project, add a reference the Microsoft ActiveX Data Objects 2.5 (or later) Library.  Double click Form1. Copy and paste the following code in the Form_Load event:

Note You must change User ID= and 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=;PWD=;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=;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 </li></ol>

Keywords: kbbug kbcodesnippet kbpending KB245494

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.