Microsoft KB Archive/280459

= BUG: SQL Server CE Filtered Recordset Permits Navigation Outside Filter =

Article ID: 280459

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Encarta Interactive World Atlas 2001
 * Microsoft SQL Server 2000 Windows CE Edition 1.1
 * Microsoft SQL Server 2000 Windows CE Edition 2.0

-



This article was previously published under Q280459



SYMPTOMS
A Microsoft SQL Server 2000 Windows CE Edition (SQL Server CE) filtered recordset permits navigation to the records outside the filter.



CAUSE
The Microsoft ADO for Windows CE (ADOCE) implementation of filters does not use the SetRange or Seek method. ADOCE uses table scans instead.



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



MORE INFORMATION
The Filter topic in ADOCE 3.1 Books Online states:

Once a filter is set, only rows that match the filter requirements appear in the recordset. The Move methods and BOF and EOF are available to navigate through the filtered recordset.

However, a SQL Server CE filtered recordset permits navigation to the records outside the filter.

To reproduce the behavior, run the following code:

NOTE: To reproduce the behavior for SQL Server CE 2.0, change the connection string from &quot;Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0; data source=\ssce.sdf&quot; to: &quot;Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0; data source=\ssce.sdf&quot;

Private Sub Command1_Click

Dim str_Connection As String Dim cn As ADOCE.Connection Dim rs As ADOCE.Recordset

str_Connection = &quot;Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0; data source=\ssce.sdf&quot; Set cn = CreateObject(&quot;ADOCE.Connection.3.1&quot;) cn.Open str_Connection cn.Execute &quot;Create table mytable(col1 int NOT NULL)&quot; cn.Execute &quot;CREATE UNIQUE INDEX idx1 ON mytable(col1)&quot; cn.Execute &quot;insert mytable values (71)&quot; cn.Execute &quot;insert mytable values (72)&quot; cn.Execute &quot;insert mytable values (73)&quot; cn.Execute &quot;insert mytable values (74)&quot;

Set rs = CreateObject(&quot;ADOCE.Recordset.3.1&quot;) rs.Open &quot;mytable&quot;, cn, adOpenKeyset, adLockReadOnly

MsgBox rs.RecordCount Dim x, y x = rs.Bookmark rs.MoveNext y = rs.Bookmark

rs.Filter = Array(x, y) MsgBox rs.RecordCount ' This returns two, which is correct Do While Not rs.EOF ' This loops through all of the records, not just the two that were &quot;filtered&quot; MsgBox rs.Fields(&quot;col1&quot;).Value rs.MoveNext Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub

