11/21/2024 3:50:37 PM
slxdeveloper.com
Now Live!
|
|
| |
| 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 183204 times
|
Rating:
 - 5.0 out of 5 by 13 users |
|
|
|
fiogf49gjkf0d
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 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.
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.
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.
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
|
|
|
|
|
|
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]
|
|
|
- subject is missing.
- comment text is missing.
|
|
|
| Best approach instead of COUNT(*)
Posted: 6/12/2003 11:16:01 AM | fiogf49gjkf0d Use COUNT(PRIMARYKEYFIELD). This ensures that the most efficient indexes are used. | |
|
| Re: Best approach instead of COUNT(*)...
Posted: 6/12/2003 1:42:26 PM | fiogf49gjkf0d 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...
|
|
|
|
|
|