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
"Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0; data source=\ssce.sdf"
to:
"Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0; data source=\ssce.sdf"
Private Sub Command1_Click()
Dim str_Connection As String
Dim cn As ADOCE.Connection
Dim rs As ADOCE.Recordset
str_Connection = "Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0; data source=\ssce.sdf"
Set cn = CreateObject("ADOCE.Connection.3.1")
cn.Open str_Connection
cn.Execute "Create table mytable(col1 int NOT NULL)"
cn.Execute "CREATE UNIQUE INDEX idx1 ON mytable(col1)"
cn.Execute "insert mytable values (71)"
cn.Execute "insert mytable values (72)"
cn.Execute "insert mytable values (73)"
cn.Execute "insert mytable values (74)"
Set rs = CreateObject("ADOCE.Recordset.3.1")
rs.Open "mytable", 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 "filtered"
MsgBox rs.Fields("col1").Value
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
REFERENCES
ADOCE 3.1 Books Online; topic: "Filter"
Keywords: kbbug kbnofix KB280459
