execute sql count statement in asp

[Matt]

In ASP page, if we want to retrive the number of rows in database: should we
use recordset object? Since this is not update, insert, delete, or select
statement

sqlStmt = "SELECT COUNT(*) from table1;"
Set objRS = Server.CreateObject("ADODB.RecordSet")
objRS.open sqlStmt, strConnect

But how do we can the value?

 

[Ray at]

In ASP page, if we want to retrive the number of rows in database: should we
use recordset object? Since this is not update, insert, delete, or select
statement

Yes, you should, since you need to retrieve a value. But it'd be like so:

sqlStmt = "SELECT COUNT(*) from table1;"
Set oADO = Server.CreateObject("ADODB.Connection")
oADO.Open strConnect
Set rsCount = oADO.Execute(sqlStmt)
iCount = rsCount.Fields.Item(0).Value
rsCount.Close : Set rsCount = Nothing
oADO.Close : Set oADO = Nothing

Response.Write "The count is " & iCount


Just be aware of issues with counting * as opposed to a specific column that
will always have a value, ie. the PK. See here, at least.
http://www.aspfaq.com/show.asp?id=2073

Ray at home