Microsoft KB Archive/279761

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

Article ID: 279761

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Windows CE Edition

-



This article was previously published under Q279761



SUMMARY
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 seek expression 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.Connection Dim rsCust As ADOCE.Recordset

Set cn = CreateObject(&quot;ADOCE.Connection.3.1&quot;) Const strConnect = &quot;Provider=microsoft.sqlserver.oledb.ce.1.0;Data Source=\ssce.sdf&quot; cn.Open strConnect

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 (70)&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;

Set rsCust = CreateObject(&quot;ADOCE.Recordset.3.1&quot;) Dim a(1) ' Set the index to seek in rsCust.Index = &quot;idx1&quot; a(0) = &quot;72&quot; rsCust.Open &quot;mytable&quot;, cn, adOpenStatic, adLockOptimistic, adCmdTableDirect rsCust.Seek a(0), adSeekFirstEQ MsgBox rsCust.Fields(&quot;Col1&quot;) MsgBox &quot;Done&quot; 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(&quot;ADOCE.Connection.3.1&quot;) Set rs = CreateObject(&quot;ADOCE.Recordset.3.1&quot;) cn.Open &quot;Provider=Microsoft.SQLSERVER.OLEDB.CE.1.0;data source=\ssce.sdf;&quot; On Error Resume Next cn.Execute &quot;drop table testtbl;&quot; On Error GoTo 0 cn.Execute &quot;create table testtbl (col1 int, col2 int, col3 int);&quot; cn.Execute &quot;insert into testtbl (col1, col2, col3) values (3, 3, 1);&quot; cn.Execute &quot;insert into testtbl (col1, col2, col3) values (3, 3, 2);&quot; cn.Execute &quot;insert into testtbl (col1, col2, col3) values (4, 3, 3);&quot; cn.Execute &quot;create index testtblindex on testtbl (col1, col2 DESC);&quot; rs.Index = &quot;testtblindex&quot; rs.Open &quot;testtbl&quot;, cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect rs.Seek Array(3, 3), adSeekFirstEQ MsgBox &quot;FirstEQ &quot; & rs.Fields(2)   ' 1 rs.Seek Array(3, 3), adSeekLastEQ MsgBox &quot;LastEQ &quot; & rs.Fields(2)    ' 2 rs.Seek Array(3, 3), adSeekBeforeEQ MsgBox &quot;BeforeEQ &quot; & rs.Fields(2)  ' 1 rs.Seek Array(3, 3), adSeekAfterEQ MsgBox &quot;AfterEQ &quot; & rs.Fields(2)   ' 1

rs.Seek Array(4, 3), adSeekFirstEQ MsgBox &quot;FirstEQ &quot; & rs.Fields(2)   ' 3 rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub

