Dear all,



    I have problems in counting the number of rows in recordset. I have 

tried objRecordSet.RecordCount, it's fail. I have heard that it can be 

retrived from SQL statement like "select Count(*) from TABLE where 

FIELD='abc'" , but how should I get the Count Number. Should I just use 

objRecordSet.Execute? How are the syntax statement to get the Count number 

in SQL.

    Thanks so much for any suggestion.



From Ray

Ray-



In the "Output Paramenters" section on page 592 in the Beg ASP 3.0 book

there is a <snip>

on how to get the count.



<snip>

CREATE PROCEDURE usp_NumberofMovies

       @Director varchar(50)

AS

SELECT COUNT(*)

FROM AllMovies

WHERE Director LIKE @Director

</snip>





For some reason I couldn't get the obRS.Count to work when I executed

the command and assigned

it to the recordset: objRS = objCMD.Execute.    It works when you open

the recordset without an explicit connection or command object.

(objRS.Open objCmdTemp,,1,1)  I don't know why... maybe someone else

does.  The SQL should work, though.



hope that helps.....



ariel 8:)



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

From: Raymond []

Sent: Tuesday, July 25, 2000 12:54 PM

To: ASP Databases

Subject: [asp_databases] How to count the number of rows in the

recordset





Dear all,



    I have problems in counting the number of rows in recordset. I have

tried objRecordSet.RecordCount, it's fail. I have heard that it can be

retrived from SQL statement like "select Count(*) from TABLE where

FIELD=3D'abc'" , but how should I get the Count Number. Should I just 

use

objRecordSet.Execute? How are the syntax statement to get the Count

number

in SQL.

    Thanks so much for any suggestion.



From Ray



---

Do you know what you want from this list? How would you provide

programmers with the solutions they need? If you have the answers, and

are willing to work in Birmingham (UK), then you could be a List

Manager. Please send CVs and a covering letter to

listsupport@p... for further information.

---

You are currently subscribed to asp_databases


$subst('Email.Unsub')

First, make sure the driver you are using supports the RecordCount property.

(MSSQL does support it). Make sure that the connection object has been specified

as "adUseClient" and 

that the recordset is using a cursor type that supports movement backwards and

forwards thru the recordset. The default, forward-only does *not*. 



If the rs.RecordCount property returns "-1" then you have not met the above

criteria.



Raymond wrote:

> 

> Dear all,

> 

>     I have problems in counting the number of rows in recordset. I have

> tried objRecordSet.RecordCount, it's fail. I have heard that it can be

> retrived from SQL statement like "select Count(*) from TABLE where

> FIELD='abc'" , but how should I get the Count Number. Should I just use

> objRecordSet.Execute? How are the syntax statement to get the Count number

> in SQL.

>     Thanks so much for any suggestion.

> 

> >From Ray

> 

Just asign the count to a variable that you will refer normaly with you

r

recordset.



"select Count(*) as mycounter from TABLE where FIELD=3D'abc'"



and then after you execute the request just refer to it as a normal fie

ld.



Patrick Lefebvre

Analyste-programmeur

Qu=E9bectel-ami

plefebvre@q...

(xxx) xxx-xxxx    poste 302

1  xxx xxx-xxxx

SELECT COUNT(*) as intNumOfRecords

FROM table1



Response.Write(objRS("intNumOfRecords"))



Alternatively, if you don't give the COUNT(*) an aliasname then you can use

the ordinal:



Response.Write(objRS(0))



Note that COUNT(*) works differently to COUNT(fieldname) - one includes

NULLs and the other doesn't



Cheers

Ken





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

From: "Raymond" 

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

Sent: Tuesday, July 25, 2000 5:53 PM

Subject: [asp_databases] How to count the number of rows in the recordset





> Dear all,

>

>     I have problems in counting the number of rows in recordset. I have

> tried objRecordSet.RecordCount, it's fail. I have heard that it can be

> retrived from SQL statement like "select Count(*) from TABLE where

> FIELD='abc'" , but how should I get the Count Number. Should I just use

> objRecordSet.Execute? How are the syntax statement to get the Count number

> in SQL.

>     Thanks so much for any suggestion.

>

> >From Ray

>

In ADO, objrecordSet.RecordCount will never give the correct results.

To achieve this, u have to add 2 more lines of coding.

objrecordset.Movelast

reccount = objrecordSet.Recordcount

objrecordset.Movefirst



Now u will get the number the records in the objrecordset.



Regards

Ponsingh





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

From: Raymond 

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

Sent: Tuesday, July 25, 2000 5:53 PM

Subject: [asp_databases] How to count the number of rows in the recordset





> Dear all,

>

>     I have problems in counting the number of rows in recordset. I have

> tried objRecordSet.RecordCount, it's fail. I have heard that it can be

> retrived from SQL statement like "select Count(*) from TABLE where

> FIELD='abc'" , but how should I get the Count Number. Should I just use

> objRecordSet.Execute? How are the syntax statement to get the Count number

> in SQL.

>     Thanks so much for any suggestion.

>

> >From Ray