How To Get a Record Count from a SQL Server CE Recordset


A common question and source of confusion in data access programming is "How do I get a record count?"

If you open a forward-only cursor on a SQL Server CE table and try to get the record count by using the following code, ADOCE returns -1 as the record count:

rs.Open "SELECT * FROM mytable", cn, adOpenForwardOnly,

More Information

This behavior is by design. In order to be fast, there is less functionality in the forward-only cursor. Following are some important points on how to get a record count:

  • The ability to get a record count is dependent on the cursor type. Some cursors are very "cheap" in that they are fast and/or use little memory. Other cursors are slower/use more memory but also provide more functionality, such as the ability to scroll backwards or get a record count.
  • A forward-only query result cursor against SQL Server CE doesn't support getting a record count. It doesn't precompute how many rows will be returned from the query. ADO returns -1 because it can't get to the interface that is used to get record counts.
  • Scrollable query cursors are the only kind of cursors that can return accurate record counts. These cursors are more expensive (in memory, mostly). Getting the record count can be an expensive operation.
  • Depending on your scenario, you may be able to do a " select count(*) from table .. " to get a number of records back before you start fetching.

Forward-Only Cursors

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:

NOTE: 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
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
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
MsgBox "Record count: " & str3

Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing
Set cn = Nothing

End Sub