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


The information in this article applies to:

  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.5 SP1
  • Microsoft Data Access Components 2.5 SP2
  • 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.

MORE INFORMATION

Steps to Reproduce Behavior

  1. 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
  2. Create a new Standard EXE project in Visual Basic. Form1 is created by default.
  3. From the Project menu, click References, and then select the Microsoft ActiveX Data Objects 2.x Library check box.
  4. Add a CommandButton control to Form1.
  5. 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 = "Provider=SQLOLEDB.1;User ID=username;Password=password;" & _
            "Initial Catalog=pubs;Data Source=ServerName"
    oConn.Open sConn
    oRecordset.Open "SELECT * FROM table1", oConn, adOpenKeyset, adLockOptimistic

    oRecordset.Filter = "running = 'false'"
    While Not (oRecordset.EOF)
        Debug.Print "Before Update ID: " & oRecordset.Fields("id")
        oRecordset.Fields("running") = True
        oRecordset.Update
        Debug.Print "updated: " & oRecordset.Fields("id")
        Debug.Print "After Update ID: " & oRecordset.Fields("id")
        oRecordset.MoveNext
    Wend
    oRecordset.Close
    oConn.Close
End Sub
  6. Modify the ADO connection string in the sConn variable as appropriate for your environment.
  7. Run the project. The results are displayed in the Debug window.
Modification Type:MajorLast Reviewed:10/17/2003
Keywords:kbbug kbnofix KB301628
©2003 Microsoft Corporation. All rights reserved.