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

Article translations Article translations
Article ID: 279761 - View products that this article applies to.
This article was previously published under Q279761
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

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("ADOCE.Connection.3.1")
Const strConnect = "Provider=microsoft.sqlserver.oledb.ce.1.0;Data Source=\ssce.sdf"
cn.Open strConnect

cn.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 in
rsCust.Index = "idx1"
a(0) = "72"
rsCust.Open "mytable", cn, adOpenStatic, adLockOptimistic, adCmdTableDirect
rsCust.Seek a(0), adSeekFirstEQ
MsgBox 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 = Nothing
End Sub
				

References

DataAccess 3.1 Books Online, topic: "Seek"

SQL Server CE Books Online, topic: "Seek"

Properties

Article ID: 279761 - Last Review: October 26, 2013 - Revision: 2.0
Applies to
  • Microsoft SQL Server 2000 Windows CE Edition
Keywords: 
kbnosurvey kbarchive kbhowto KB279761

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com