Microsoft KB Archive/272067

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

Article ID: 272067

Article Last Modified on 10/6/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Windows CE Edition 2.0

-



This article was previously published under Q272067



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

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 &quot;SELECT * FROM mytable&quot;, 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 &quot;cheap&quot; 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 &quot; select count(*) from table .. &quot; 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 &quot;Provider=microsoft.sqlserver.oledb.ce.1.0;Data Source=\ssce.sdf&quot; to: &quot;Provider=microsoft.sqlserver.oledb.ce.2.0;Data Source=\ssce.sdf&quot;

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

cn.Open strConnect

cn.Execute &quot;Create table mytable (col1 int NOT NULL)&quot; cn.Execute &quot;CREATE UNIQUE INDEX idx1 ON mytable(col1)&quot; cn.Execute &quot;insert mytable values (70)&quot; cn.Execute &quot;insert mytable values (71)&quot;

' 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(&quot;ADOCE.Recordset.3.1&quot;) rs1.Open &quot;mytable&quot;, cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect

str1 = rs1.RecordCount rs1.Close MsgBox &quot;Record count: &quot; & 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(&quot;ADOCE.Recordset.3.1&quot;) rs2.Open &quot;Select * from mytable&quot;, cn, adOpenForwardOnly, adLockOptimistic str2 = rs2.RecordCount rs2.Close MsgBox &quot;Record count: &quot; & str2

' Scrollable cursors can use either adOpenStatic or adOpenKeyset. ' These cursors should use adLockReadOnly and not specify the adCmdTableDirect flag. Set rs3 = CreateObject(&quot;ADOCE.Recordset.3.1&quot;) rs3.Open &quot;Select * from mytable&quot;, cn, adOpenStatic, adLockReadOnly str3 = rs3.RecordCount rs3.Close MsgBox &quot;Record count: &quot; & str3

cn.Close Set rs1 = Nothing Set rs2 = Nothing Set rs3 = Nothing Set cn = Nothing

End Sub

Additional query words: forwardonly forward only recordcount

Keywords: kbhowto KB272067

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.