I am currently having a problem with the RecordCount ADO property.



When a run the following ASP code:



set rs = Server.CreateObject ("ADODB.Recordset")

  rs.Open sql, db, adOpenDynamic, adLockReadOnly, adCmdText

  intNoOfRecords = rs.RecordCount



!(where sql = Select * From table;)



The result set returns the rows from the SQL select (I am able to move 

through and print them), however the rs.RecordCount returns a -1, when 

there are 4 records in the database.  I need rs.RecordCount to return the 

actual value, so my indexing will work correctly.  Any ideas why this is 

happening?



Tom

www.adopenstatic.com/faq/recordcounterror.asp

(adOpenDynamic does not always return correct recordcount - depends on the

provider.)



www.adopenstatic.com/faq/recordcountalternatives.asp

What I'd consider to be better ways to get a recordcount.



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Tom Garrison" <tom.garrison@e...>

Subject: [asp_databases] Problem with ADO RecordCount using SQL





: I am currently having a problem with the RecordCount ADO property.

:

: When a run the following ASP code:

:

: set rs = Server.CreateObject ("ADODB.Recordset")

:   rs.Open sql, db, adOpenDynamic, adLockReadOnly, adCmdText

:   intNoOfRecords = rs.RecordCount

:

: !(where sql = Select * From table;)

:

: The result set returns the rows from the SQL select (I am able to move

: through and print them), however the rs.RecordCount returns a -1, when

: there are 4 records in the database.  I need rs.RecordCount to return the

: actual value, so my indexing will work correctly.  Any ideas why this is

: happening?



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Recordcount returns the number of records, only if the recordset has been

fully populated (ie re.movefirst rs.movelast). This adds a performance hit

to your page. If this is needed to loop through the recordset, use

Do While Not rs.EOF

	rs.MoveNext

Loop



You could add in a counter to the loop if you needed to have the number of

records.



If you _must_ have the number of records before you loop through the

recordset then change your query eg



SELECT <fields>

FROM table1



becomes



SELECT <fields>, COUNT(*)

FROM table1

GROUP BY <fields>



HTH



regards

David Cameron

nOw.b2b

dcameron@i...



-----Original Message-----

From: Tom Garrison [mailto:tom.garrison@e...]

Sent: Tuesday, 11 September 2001 9:04 AM

To: ASP Databases

Subject: [asp_databases] Problem with ADO RecordCount using SQL





I am currently having a problem with the RecordCount ADO property.



When a run the following ASP code:



set rs = Server.CreateObject ("ADODB.Recordset")

  rs.Open sql, db, adOpenDynamic, adLockReadOnly, adCmdText

  intNoOfRecords = rs.RecordCount



!(where sql = Select * From table;)



The result set returns the rows from the SQL select (I am able to move 

through and print them), however the rs.RecordCount returns a -1, when 

there are 4 records in the database.  I need rs.RecordCount to return the 

actual value, so my indexing will work correctly.  Any ideas why this is 

happening?



Tom

 

Make sure you've set up a clientside cursor for the connection object.



-- Tomm



> -----Original Message-----

> From: Tom Garrison [mailto:tom.garrison@e...]

> Sent: Monday, September 10, 2001 11:04 PM

> To: ASP Databases

> Subject: [asp_databases] Problem with ADO RecordCount using SQL

>

>

> I am currently having a problem with the RecordCount ADO property.

>

> When a run the following ASP code:

>

> set rs = Server.CreateObject ("ADODB.Recordset")

>   rs.Open sql, db, adOpenDynamic, adLockReadOnly, adCmdText

>   intNoOfRecords = rs.RecordCount

>

> !(where sql = Select * From table;)

>

> The result set returns the rows from the SQL select (I am able to move

> through and print them), however the rs.RecordCount returns a -1, when

> there are 4 records in the database.  I need rs.RecordCount to return the

> actual value, so my indexing will work correctly.  Any ideas why this is

> happening?

>

> Tom

>

Thanks Ken, really good ideas.  I will probably go with the following

methodology:



SELECT Count(*) as Expr1

FROM table1

WHERE foo = 'bar' 



Tom Garrison

EDS POS Aurora Group





-----Original Message-----

From: Ken Schaefer [mailto:ken@a...]

Sent: Monday, September 10, 2001 8:56 PM

To: ASP Databases

Subject: [asp_databases] Re: Problem with ADO RecordCount using SQL





www.adopenstatic.com/faq/recordcounterror.asp

(adOpenDynamic does not always return correct recordcount - depends on the

provider.)



www.adopenstatic.com/faq/recordcountalternatives.asp

What I'd consider to be better ways to get a recordcount.



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Tom Garrison" <tom.garrison@e...>

Subject: [asp_databases] Problem with ADO RecordCount using SQL





: I am currently having a problem with the RecordCount ADO property.

:

: When a run the following ASP code:

:

: set rs = Server.CreateObject ("ADODB.Recordset")

:   rs.Open sql, db, adOpenDynamic, adLockReadOnly, adCmdText

:   intNoOfRecords = rs.RecordCount

:

: !(where sql = Select * From table;)

:

: The result set returns the rows from the SQL select (I am able to move

: through and print them), however the rs.RecordCount returns a -1, when

: there are 4 records in the database.  I need rs.RecordCount to return the

: actual value, so my indexing will work correctly.  Any ideas why this is

: happening?



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Client-side cursor is not required - all that is required is the correct

cursor type - adOpenKeyset, or adOpenStatic - you can use a server-side

cursor if you want.



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Tomm Matthis" <matthis@b...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, September 11, 2001 11:42 AM

Subject: [asp_databases] RE: Problem with ADO RecordCount using SQL





: Make sure you've set up a clientside cursor for the connection object.

:

: -- Tomm

: