You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q272067
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,adLockReadOnly				
This behavior is by design. In order to be fast, there is lessfunctionality 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 morefunctionality, such as the ability to scroll backwards or get a recordcount.
  • 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 youstart 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.ConnectionDim rs1 As ADOCE.RecordsetDim rs2 As ADOCE.RecordsetDim rs3 As ADOCE.RecordsetDim catalogPrivate Sub Form_Load()Dim str1 As StringDim str2 As StringDim str3 As StringSet 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)"' 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, adCmdTableDirectstr1 = rs1.RecordCountrs1.CloseMsgBox "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, adLockOptimisticstr2 = rs2.RecordCountrs2.CloseMsgBox "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, adLockReadOnlystr3 = rs3.RecordCountrs3.CloseMsgBox "Record count: " & str3cn.CloseSet rs1 = Nothing Set rs2 = Nothing Set rs3 = Nothing Set cn  = Nothing End Sub				
forwardonly forward only recordcount

Article ID: 272067 - Last Review: 10/06/2004 21:05:20 - Revision: 1.2

Microsoft SQL Server 2000 Windows CE Edition 2.0

  • kbhowto KB272067