problem with record count...

[reigalz]

hi, i'm using the record count function to see how many records are returned from the database....but when i do response.write(rs.RecordCount), its always -1 even if there are records in the recordset....why is this so?? or is there any other way to check whether the recordset has return any records? thanks in adv!

[raf]

If you use count --> include an alias in your sql-statement. You can then refer to the alias to display the number.

Like this:

edit: in the below syntax, tablename doesn't refer to the tablename you should include. Its the name of a variable.




sql="SELECT DISTINCT TableName, Count(*) AS [numvar] FROM Metatable GROUP BY TableName ORDER BY TableName"

rsTables.Open sql, conadmin

if rsTables.EOF=true then
Response.Write("<font color='red'>Databaseproblem. Check operational site for problems !!</font>")
response.write("<br><br><a href='javascript:history.back();'>Back</a>")

else
do while rsTables.EOF=false

response.write("<br/>" & rsTables.Fields("numvar") )

rsTables.MoveNext
loop
end if

[reigalz]

then where should i put the fields i want to select?
here's my SQL code:

rs1.Open "SELECT ProductModel, Description, KeyFeatures, Category FROM Products WHERE Description LIKE '%"+searchtopic+"%'", conn

where should i put the Count(*) AS numRecs ?

[raf]

what do you mean? Is this a trickquestion?

rs1.Open "SELECT Count(*) AS numRecs FROM Products WHERE Description LIKE '%"+searchtopic+"%'", conn

will give you the number of records.

If you want to return some additional fields, you need to use a distinct.

rs1.Open "SELECT DISTINCT ProductModel, Description, KeyFeatures, Category, Count(*) AS numRecs FROM Products WHERE Description LIKE '%"+searchtopic+"%' GROUP BY ProductModel, Description, KeyFeatures, Category", conn

The combination of the values for these variables will be unique.

Normally, when you use count in a db-driven app, you dipsplay one variabe, with the count, as a link. If the user hits that link, he'll get the description and other variables.

For instance

rs1.Open "SELECT DISTINCT Category, Count(*) AS numRecs FROM Products WHERE Description LIKE '%"+searchtopic+"%' GROUP BY Category", conn


Will give you an output like
cat1 (10)
cat2 (5)
cat3 (6)

where each cat is a link (with the categoryvalue in the querystring) to a page that will display the actual productmodel etc

Is it something like that you're trying to do?

[pankaj]

Try using this statement

rs.CursorLocation = adUseClient

Response.write rs.RecordCount


Dont forget to include
<!-- # include file="adoVBS.inc" --> If not included already