Count rows in Recordset from Stored Procedure

[globularbob]

I have a recordset that is being populated from a Stored Procedure. When I use the rs.RecordCount function, it always returns -1. Is there another way to return the count of records from a recordset from a SP?

sqlTeamCount = "Execute dbo.proc_Get_Org_Chart " & session("emplID&quot

 

[TomKane]

You could do a count query - I've got some stuff from sql below:

COUNT (T-SQL)
Returns the number of items in a group.

Syntax
COUNT({[ALL | DISTINCT] expression] | *})

Arguments
ALL
Applies the aggregate function to all values. ALL is the default.
DISTINCT
Specifies that COUNT returns the number of unique nonnull values.
expression
Is an expression of any type except uniqueidentifier, text, image, or ntext. Aggregate functions and subqueries are not permitted.
*
Specifies that all rows should be counted to return the total number of rows in a table. COUNT(*) takes no parameters and cannot be used with DISTINCT. COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without eliminating duplicates. It counts each row separately, including rows that contain null values.

--------------------------------------------------------------------------------

Important Distinct aggregates, for example AVG(DISTINCT column_name), COUNT(DISTINCT column_name), MAX(DISTINCT column_name), MIN(DISTINCT column_name), and SUM(DISTINCT column_name), are not supported when using CUBE or ROLLUP. If used, Microsoft® SQL Server™ returns an error message and cancels the query.


--------------------------------------------------------------------------------

Return Types
int

Remarks
COUNT(*) returns the number of items in a group, including NULL values and duplicates.

COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

Examples
A. Use COUNT and DISTINCT
This example finds the number of different cities in which authors live.

USE pubs

GO

SELECT COUNT(DISTINCT city)

FROM authors

GO



Here is the result set:

-----------

16



(1 row(s) affected)



B. Use COUNT(*)
This example finds the total number of books and titles.

USE pubs

GO

SELECT COUNT(*)

FROM titles

GO



Here is the result set:


-----------

18



(1 row(s) affected)



C. Use COUNT(*) with other aggregates
The example shows that COUNT(*) can be combined with other aggregate functions in the select list.

USE pubs

GO

SELECT COUNT(*), AVG(price)

FROM titles

WHERE advance > $1000

GO



Here is the result set:

----------- --------------------------

15 14.42



(1 row(s) affected)

 

[disord3r]

globularBob:

The .RecordCount property will return -1 if you haven't set your cursor type appropriately. I open connections like this:

rs.Open sql_query, connection_name

...and adding ", 1" to that command made this work for me (asOpenKeyset is the cursor type, if I recall).