Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Wednesday, November 26, 2014 
 
How To Retrieve a Record Count From an ADO Recordset  
Description:  It is often necessary to retrieve a record count from an ADO Recordset. Although the Recordset object has a built in RecordCount property, there are some special requirements needed in order to use it. This article explores these requirements as well as a better alternative to counting the records in a Recordset.

Category:  Architect How To Articles
Author:  Ryan Farley
Submitted:  1/14/2003
   
Stats: 
Article has been read 81702 times

Rating: - 5.0 out of 5 by 13 users
 

How To Retrieve a Record Count From an ADO Recordset

It is often necessary to retrieve a record count from an ADO Recordset. Although the Recordset object has a built in RecordCount property, there are some special requirements needed in order to use it. This article explores these requirements as well as a better alternative to counting the records in a Recordset.

The Recordset's RecordCount Property

The ADO Recordset object has a RecordCount property. Obviously, this property indicates the current number of records in the Recordset. However, the accuracy of its results depends on how you use it. If you open a Recordset and look at it's RecordCount property (as in the example below), you'll notice that it always returns a "-1" as the result.

Dim objConn
Dim objRS

	 Set objConn = Application.GetNewConnection

	 Set objRS = objConn.Execute("select * from account")
	 MsgBox objRS.RecordCount ' this will display -1
	
	 objRS.Close
	 Set objRS = Nothing
	 Set objConn = Nothing    

So, what's the problem? The RecordCount property will only be accurate for Recordsets that support approximate positioning or bookmarks. Basically, this is about all cursor types except server-side, forward-only cursors, and server-side, dynamic cursors.

By default, when a Recordset is created, it's cursor location is set to a server-side cursor (adUseServer) and it's cursor type to forward-only (adOpenForwardOnly). If you need the RecordCount to be correct, then set the CursorType to something other than forward-only (e.g. adOpenKeyset or adOpenStatic). Using a dynamic cursor (adOpenDynamic) will not help in this case since the number of records in a dynamic cursor may change.

If you create a client-side Recordset (adUseClient), then ADO automatically sets the CursorType to a static cursor (adOpenStatic), therefore, the RecordCount property will always be correct for client-side Recordsets. Let's take a quick look at the various CursorLocations & CursorTypes for the Recordset

CursorLocation
  • adUseServer (Default)
    Use the cursor support supplied by the data provider.
  • adUseClient
    Use the Microsoft client cursor.

CursorType
  • adOpenForwardOnly (Default)
    Opens a forward-only cursor - behaves identically to a static cursor except that it allows to scroll forward only through records. This improves performance in situations where you need to make only a single pass through a Recordset.
  • adOpenDynamic
    Opens a dynamic-type cursor - allows one to view additions, changes, and deletions by other users, and allows all types of movement through the Recordset.
  • adOpenKeyset
    Opens a keyset-type cursor - behaves like a dynamic cursor, except that it prevents one from seeing records that other users add, and prevents access to records that other users delete. Data changes by other users will still be visible.
  • adOpenStatic
    Opens a static type cursor - provides a static copy of a set of records to find data or generate reports. Additions, changes, or deletions by other users will not be visible.

Why do these matter? As I mentioned before, the cursor location & type matter when it comes to using the RecordCount property. The type of cursor, and it's location, also make a big difference on speed/performance of the Recordset so you need to be careful when choosing the type and make sure it is one that best suits your needs. If you're not sure what to use, then always use a server-side, forward-only cursor (which is the default anyway), although you won't be able to get a count this way. The code below shows a client-side cursor to be able to use the RecordCount property. Note that in order to set the cursor properties we'll have to explicitly create our Recordset using CreateObject. When you implicitly create the Recordset object using the Execute method of the Connection object, the defaults are used and the Recordset is opened (so it would be too late).

' Using a client side cursor
Dim objRS

    Set objRS = CreateObject("ADODB.Recordset")
    With objRS
        .CursorLocation = adUseClient
        .Open "select * from account", Application.GetNewConnection

        MsgBox .RecordCount & " records"

        .Close
    End With
    Set objRS = Nothing    

Not too bad, but client-side cursors are not very fast, so if you're going to be using the Recordset for looping or any type of data processing, then using a client-side cursor will be very costly. Similarly, you could use a server-side cursor with type of adOpenStatic. This would be a better idea than using the client-side cursor, but a static cursor is not the best idea either and may not be supported by the DBMS.

A Better "Count" Alternative

There is a better way. The problem is that by changing your cursor type or location to something less optimized for speed, is that the code that actually uses the Recordset will suffer, all because you just needed to use the RecordCount property. This is no good. So, a better approach to getting a count? Simple, break it up into two parts. First, use the server to perform the count using a select count(*) from table. Second, open the data using an optimized server-side, read-only, & forward-only Recordset that you'll use for the actual data processing.

It is a little extra work, but the payoff will make it worth it if you have a lot of work to do with the data in the Recordset. Let's look at an example.

Dim objRS

    Set objRS = CreateObject("ADODB.Recordset")
    With objRS
    	' set cursor properties
        .CursorLocation = adUseServer
        .CursorType = adOpenForwardOnly
        ' set lock type
        .LockType = adLockReadOnly
        ' set connection for Recordset
        Set .ActiveConnection = Application.GetNewConnection

        ' Get record count
        .Open "select count(*) from account"
        MsgBox .fields(0).value & " records"

        ' Now close Recordset and reopen with data to be processed
        If .State = adStateOpen Then .Close
        .Open "select account, type from account"

        ' Do some useless processing...
        While Not (.BOF Or .EOF)
              Listbox1.Items.Add .Fields("account").value & ""
              .MoveNext
        Wend
        .Close
    End With
    Set objRS = Nothing    

In that example, I created my Recordset and set up the cursor & lock properties so it is optimized for the heavy processing I was about to perform. I opened the query to count the records, then closed it and reopened the Recordset with the actual data I'll need for the heavy data processing.

A Few Things To Note

When using SQL to perform the count, you should do a count of '*' (ie: all fields), or at least some field that you know will not be null (preferably an indexed field or primary key for the table). If you do a count of a certain field that could potentially contain null values, then your count will be inaccurate since you'll only get a count of the non-null values in that field which may be different than the number of actual rows.

There will likely be situations where this method might be overkill. However since you're working with an already connected ADO connection from the pool the hit will be minimal (although I've never benchmarked it). The alternative, using a client-side cursor, is too costly. There are two performance penalties associated with using a client-side cursor. First, as I mentioned before, when you set the CursorLocation to adUseClient, the CursorType is automatically set to adOpenStatic (even if you've specified otherwise), which is about twice as expensive as adOpenForwardOnly. Second, in situations where the application is on a physically separate machine than the database server, additional network traffic is generated as the Microsoft OLEDB Cursor Service located on the client machine is now managing the cursor, not the DBMS.

Another argument to use this method is that it becomes more generic. There has been some discussion to the effect that the MS Oracle OLEDB Provider (or earlier versions of this provider) do not support either approximate positioning or bookmarking, hence require client-side cursors in order for .RecordCount to work. I'd rather not have to go with a client-side cursor, and have SalesLogix appear to have slowed down even more, just to be able to support this special case.

For those feeling adventurous, another excellent method to get all the data (and be able to get a count of the rows) is to use the Recordset's GetRows method which extracts the Recordset into an array. You can then discard of the Recordset and retrieve your rows and field values from the array as well as use UBound to get the number of rows in the array.

Wrapping it Up

As there is no single solution to solve the problems of every scenario, knowing what you have available to use (and what they mean) for simple tasks such as getting a record count, will prove to be invaluable for your every day development efforts.

Until next time, happy coding.
-Ryan
 

About the Author

  Ryan Farley
(SalesLogix Business Partner)
Customer FX Corporation

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix since 2001 and believes in sharing with the community. He loves C#, Javascript, Python, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

View Ryan's SalesLogix Mobile Seveloper Series
View Ryan's SalesLogix SData Developer Series
View Ryan's Git for the SalesLogix Developer series



View online profile for Ryan Farley
 

[ back to top] [ send to a friend]  

Rate This Article you must log-in to rate articles. [login here] 
 
Please log in to rate article.
 

Comments & Discussion you must log-in to add comments. [login here] 
 
Author Article Comments and Discussion
Jake Horn
 

Best approach instead of COUNT(*)
Posted: 6/12/2003 11:16:01 AM
Use COUNT(PRIMARYKEYFIELD). This ensures that the most efficient indexes are used.
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: Best approach instead of COUNT(*)...
Posted: 6/12/2003 1:42:26 PM
Great point Jake. Definately the best way to do it. Just to stress that you want to make sure you're using a true non-nullable primary key (which is what you're saying to do), because NULLs won't be included in the count when you count on a specific field. (Not to say that you didn't know that Jake, but wanted to clear that up for any others).

-Ryan
 
 

       Visit the slxdeveloper.com Community Forums!
Not finding the information you need here? Try the forums! Get help from others in the community, share your expertise, get what you need from the slxdeveloper.com community. Go to the forums...
 



 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2014 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 11/26/2014 5:48:14 PM