Please help with the SQL COUNT statement!

I'm trying to run this SQL statement in my ASP code, sql="SELECT COUNT(*) FROM order_list WHERE ref_index='"&ref_index&"'"

The problem is, should i create a variable to store the returned integer? I tried rs_itemcount = conn.execute(sql) and then response.write(rs_itemcount.value)

But it returns with an error... any ideas how to store the integer and use it?!

thx!

It doesn't quite work like that. Count basically becomes a field as part of your recordset.
sql="SELECT COUNT(*) AS theCount FROM order_list WHERE ref_index='"&ref_index&"'"

Then, once you open your recordset:
yourInt = rs("theCount")

That will set yourInt to the recordcount. However, this is only accessible after you run through the whole recordset.

Thx for the reply...

I tried, but it says "Item cannot be found in the collection corresponding to the requested name or ordinal. "

And what do you mean by running through the whole recordset? Does it mean doing all the
if rs.eof = false then
rs.movenext
end if?
I don't understand this at all it says the sql statement should return an integer but how can i get that number?!

Hi


I read your error message ..this will arrive when you make wrong field name...

The following script will work

sql="SELECT COUNT(*) as cnt FROM order_list WHERE ref_index='"&ref_index&"'"

Is the ref_index field is varchar or char type? If yes then ok else
you have to change the query

now

rs = cn.execute(sql)
response.write rs("cnt")
set rs = nothing
set cn = nothing


It will work definitly

Register in http://www.balainfo.com/forums/ for detailed support

Regds
Bala