Forward-only cursors in SQL Server CE cannot determine how many records are going to be returned. To provide the fastest, least memory-intensive cursor, the query processor is actually giving back the rows as it determines them. There is no buffering step where a count of rows is predetermined.
Scrollable query cursors in SQL Server CE, on the other hand, buffer rows as they are fetched. If the user requests a row count, the rows are buffered so that they can be counted. Because you can scroll backwards over the result set, however, it is then possible to re-read the values of the rows after they have been buffered for counting.
The following code demonstrates how to use base table, forward-only, and scrollable cursors to open recordsets by using ADOCE and eVB. Only scrollable cursors give an accurate record count:
: For SQL Server CE 2.0 to work with the sample code, you must change the connection string from
"Provider=microsoft.sqlserver.oledb.ce.1.0;Data Source=\ssce.sdf"
"Provider=microsoft.sqlserver.oledb.ce.2.0;Data Source=\ssce.sdf"
Dim cn As ADOCE.Connection
Dim rs1 As ADOCE.Recordset
Dim rs2 As ADOCE.Recordset
Dim rs3 As ADOCE.Recordset
Dim catalog
Private Sub Form_Load()
Dim str1 As String
Dim str2 As String
Dim str3 As String
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)"
' For Base table cursor, just supply table name as source, DO NOT WRITE A SELECT * statement.
' Should use adOpenDynamic and adLockOptimistic with adCmdTableDirect flag.
Set rs1 = CreateObject("ADOCE.Recordset.3.1")
rs1.Open "mytable", cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
str1 = rs1.RecordCount
rs1.Close
MsgBox "Record count: " & str1
' Forward-only cursor does not support record count.
' Forward-only cursors should use adOpenForwardOnly and adLockOptimistic.
' The adCmdTableDirect flag should not be used.
Set rs2 = CreateObject("ADOCE.Recordset.3.1")
rs2.Open "Select * from mytable", cn, adOpenForwardOnly, adLockOptimistic
str2 = rs2.RecordCount
rs2.Close
MsgBox "Record count: " & str2
' Scrollable cursors can use either adOpenStatic or adOpenKeyset.
' These cursors should use adLockReadOnly and not specify the adCmdTableDirect flag.
Set rs3 = CreateObject("ADOCE.Recordset.3.1")
rs3.Open "Select * from mytable", cn, adOpenStatic, adLockReadOnly
str3 = rs3.RecordCount
rs3.Close
MsgBox "Record count: " & str3
cn.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing
Set cn = Nothing
End Sub