How To Get a Record Count from a SQL Server CE Recordset (272067)

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

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"
				

to:

"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