Microsoft KB Archive/301628

= BUG: Update on Filter Column Row Pointer Changes from MDAC 2.1 to 2.5 and 2.6 =

Article ID: 301628

Article Last Modified on 10/17/2003

-

APPLIES TO


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

-



This article was previously published under Q301628



SYMPTOMS
After you update the filtered column in an ActiveX Data Objects (ADO) recordset, the row pointer position changes behavior from Microsoft Data Access Components (MDAC) 2.1 to MDAC 2.5 and 2.6. This problem only occurs when you use a server-side keyset cursor.



CAUSE
In MDAC 2.1, the row pointer is set to the current location after the update. Therefore, a MoveNext call moves the row pointer to the next row in the recordset that meets the filter criteria. For example, before the update, the row pointer position appears as follows:

ID Filter

== 1  true   <-- 2  true 3  true

After the update and MoveNext, the row pointer position appears as follows:

ID Filter

== 1  false 2  true   <-- 3  true

In MDAC 2.5 and 2.6, the row pointer is set to the next row that meets the filter criteria. Thus, a MoveNext call moves the row pointer to the second row that meets the filter criteria and breaks applications that are built according to the behavior in MDAC 2.1. For example, before the update, the row pointer position appears as follows:

ID Filter

== 1  true   <-- 2  true 3  true

After the update and MoveNext, the row pointer position appears as follows:

ID Filter

== 1  false 2  true 3  true   <--



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce Behavior
  Run the following queries in SQL Server Query Analyzer: Use Pubs GO

If exists (Select * from SysObjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) Drop table [dbo].[Table1] GO

CREATE TABLE [dbo].[Table1] (   [id] [int] NOT NULL,    [Running] [bit] NULL ) ON [PRIMARY] GO

ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD CONSTRAINT [PK_Table1] PRIMARY KEY NONCLUSTERED (       [id]    )  ON [PRIMARY] GO

INSERT INTO Table1 values(1,0) GO INSERT INTO Table1 values(2,0) GO INSERT INTO Table1 values(3,0) GO

select * from Table1 GO  Create a new Standard EXE project in Visual Basic. Form1 is created by default. From the Project menu, click References, and then select the Microsoft ActiveX Data Objects 2.x Library check box. Add a CommandButton control to Form1.</li>  Paste the following code into the code window of Form1: Dim oConn As New ADODB.Connection Dim oRecordset As New ADODB.Recordset Dim sConn As String

sConn = &quot;Provider=SQLOLEDB.1;User ID=username;Password=password;&quot; & _ &quot;Initial Catalog=pubs;Data Source=ServerName&quot; oConn.Open sConn oRecordset.Open &quot;SELECT * FROM table1&quot;, oConn, adOpenKeyset, adLockOptimistic

oRecordset.Filter = &quot;running = 'false'&quot; While Not (oRecordset.EOF) Debug.Print &quot;Before Update ID: &quot; & oRecordset.Fields(&quot;id&quot;) oRecordset.Fields(&quot;running&quot;) = True oRecordset.Update Debug.Print &quot;updated: &quot; & oRecordset.Fields(&quot;id&quot;) Debug.Print &quot;After Update ID: &quot; & oRecordset.Fields(&quot;id&quot;) oRecordset.MoveNext Wend oRecordset.Close oConn.Close End Sub </li> Modify the ADO connection string in the sConn variable as appropriate for your environment.</li> Run the project. The results are displayed in the Debug window.</li></ol>

Additional query words: filter

Keywords: kbbug kbnofix KB301628

-

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

© Microsoft Corporation. All rights reserved.