hi ,
how can we get the actual recordcount in simple steps.

thank u
riax

I don't usually use RecordSets for such operations because it is slow. Putting it in an array works better.

rst.Open sql, objconn
dim arrRecs, TotalRecords

IF rst.EOF THEN
'--No records found
ELSE
arrRecs = rst.GetRows() '--put records into array
rst.close() '--close recordset
TotalRecords = Ubound(arrRecs, 2) '
'-- get total records
'-- 1 means columns, 2 means records
END IF

thanks 4 it, i hav done the same thing but need more convinient code too, since Recordset provides facility to countrecord then why so its not working ???
thanking u once again.
riax

Counting records is contentious in Asp, if I am correct.

Something about doing a "rs.count" moves through each record and you end up at the end.

So you have to check for rs.eof first, then you have to do a rs.movefirst, then you can do a count.
dim i
i = 0
if rs.bof <> rs.eof then
while rs.eof <> true
i = i+1
do
end if

Something like that works too.

It's all dependant on the cursor type and cursor location that you use when you open your recordset....

Some cursors will only give you a valid recordcount if you movelast but then they will not let you move back through the recordset etc....

Have a fiddle with the cursors that you are using and you will see different results.... you can also try disconnecting the recordset, from memory that will always work but of course it may not fit in with what you are trying to do....

Hmm, since my numbers indicate that going through an array is faster than going through a recordset, it seems to me that switching to an array would be most beneficial if you are going through the entire recordset multiple times on the same page... for example, if you have to poulate two dropdown boxes with the same data. In that case, an array method would definitely be better than just moving through the recordset.

hehehe back on to the arrays vs recordsets....

In answer to this guys specific question (eg. why does recordcount return -1) the answer is the cursor type....

Weather he should be using arrays of not is a decision for him to make...

It's possible that, for what he wants to do, a recordset is the best option...

Additionally, I don't know that I would loop through a recordset twice to build to seperate option boxes.... it might be more effective to build them both at once (only loop once). You do get the performance hit from doing the concatenation for one of your option boxes but it might be less then the hit you would get by looping through things twice...

Another thought,.... with your stats did you try doing multiple loops? Perhaps the first loop with a recordset is slow and the second faster? Would be interesting to know.

To obtain the count of records, the DBMS must move to the last record. If you need to do that, you can do so ... but it means that the DBMS must complete the query before you can proceed. Otherwise the DBMS might continue to work on your query in the background, after having delivered just the first bucketful of data to you.

Often, you simply want to know if the result-set is empty. You can do that by checking properties such as 'EOF.'

Okie, enough of this,... straight from the horse mouth so to speak (MSDN).