How To Use the ADOCE 3.1 Seek Method with SQL Server CE

This article was previously published under Q279761
This article has been archived. It is offered "as is" and will no longer be updated.
This article describes how to use the Microsoft ADO for Windows CE SDK (ADOCE) 3.1 Seek method with SQL Server 2000 Windows CE Edition.

One of the most widely used methods by clients on SQL Server CE indexes is Seek. The Seek method provides the ability to find rows on a cursor very quickly. For the seek to work properly, Seek requires that an index be defined on the columns in the search key.
More information
To use the Seek method you must have an index to match the seekexpression on the base table and your recordset Open method must use the adCmdTableDirect flag.

The Seek method locates a record in an indexed recordset and makes that record the current record.

Seek takes an array of Variant values as its first parameter. Each element of the array must contain a value to compare against each corresponding column in the index.For example:

Dim cn As ADOCE.ConnectionDim rsCust As ADOCE.RecordsetSet cn = CreateObject("ADOCE.Connection.3.1")Const strConnect = "Provider=microsoft.sqlserver.oledb.ce.1.0;Data Source=\ssce.sdf"cn.Open strConnectcn.Execute "Create Table mytable (col1 int NOT NULL)"cn.Execute "Create Unique Index idx1 On mytable (col1)"cn.Execute "Insert mytable values (70)"cn.Execute "Insert mytable values (71)"cn.Execute "Insert mytable values (72)"cn.Execute "Insert mytable values (73)"Set rsCust = CreateObject("ADOCE.Recordset.3.1")Dim a(1)' Set the index to seek inrsCust.Index = "idx1"a(0) = "72"rsCust.Open "mytable", cn, adOpenStatic, adLockOptimistic, adCmdTableDirectrsCust.Seek a(0), adSeekFirstEQMsgBox rsCust.Fields("Col1")MsgBox "Done"				
The following code demonstrates how to do a multi-column Seek on a SQL Server CE table:
Private Sub Seek_Test()    Dim cn As Connection    Dim rs As Recordset        Set cn = CreateObject("ADOCE.Connection.3.1")    Set rs = CreateObject("ADOCE.Recordset.3.1")    cn.Open "Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0;data source=\ssce.sdf;"    On Error Resume Next    cn.Execute "drop table testtbl;"    On Error GoTo 0    cn.Execute "create table testtbl (col1 int, col2 int, col3 int);"    cn.Execute "insert into testtbl (col1, col2, col3) values (3, 3, 1);"    cn.Execute "insert into testtbl (col1, col2, col3) values (3, 3, 2);"    cn.Execute "insert into testtbl (col1, col2, col3) values (4, 3, 3);"    cn.Execute "create index testtblindex on testtbl (col1, col2 DESC);"        rs.Index = "testtblindex"    rs.Open "testtbl", cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect        rs.Seek Array(3, 3), adSeekFirstEQ    MsgBox "FirstEQ " & rs.Fields(2)    ' 1    rs.Seek Array(3, 3), adSeekLastEQ    MsgBox "LastEQ " & rs.Fields(2)     ' 2    rs.Seek Array(3, 3), adSeekBeforeEQ    MsgBox "BeforeEQ " & rs.Fields(2)   ' 1    rs.Seek Array(3, 3), adSeekAfterEQ    MsgBox "AfterEQ " & rs.Fields(2)    ' 1    rs.Seek Array(4, 3), adSeekFirstEQ    MsgBox "FirstEQ " & rs.Fields(2)    ' 3        rs.Close    Set rs = Nothing    cn.Close    Set cn = NothingEnd Sub				
DataAccess 3.1 Books Online, topic: "Seek"

SQL Server CE Books Online, topic: "Seek"
kbSSCE2000 seek adoce

Article ID: 279761 - Last Review: 10/26/2013 13:03:00 - Revision: 2.0

  • Microsoft SQL Server 2000 Windows CE Edition
  • kbnosurvey kbarchive kbhowto KB279761