Guest Author - Lisa Shea
If you're trying to figure out how many records are in a given SQL result set, you can use either the RecordCount or Count command. Both work in different ways.
First, if all you want is the count of how many items are in a set, there is no need to actually return all the records in that query! That is incredibly inefficient. All you need is that one count number. The syntax to do that would be
select count(*) as ct from writers;
That query runs quickly, returns one value called count, and can be accessed with the normal style assignment -
WriterCt = objRec3("ct")
So I always recommend getting counts in that manner if that is all you want.
If you are actually getting a batch of information from the database, and need to know at the end how many records you went through, I would just use a counter variable. So say you are listing out your writers and at the bottom you want to show a Total Writers amount to the user. You would do a normal loop:
WriterCt = 0
do while not objRec3.EOF
(show the writers etc)
WriterCt = WriterCt + 1
When you end the loop, WriterCt will be set to the total writers you processed. This is especially good in cases where you are ignoring certain writers as you process them. You can make sure to only count writers you are actually showing to the user.
Finally, there is a recordset attribute called RecordCount. However, the RecordCount attribute doesn't work with all styles of cursors. There are all sorts of cursors used in ASP. Some are set up to move in a forward direction only. Some are set to allow sequential moves through the database while others are meant to get one and only one result value. Depending on which style of cursor you use, you may or may not have access to a total record count of the result set. If you do actually want to get that record count before you begin working with the results, make sure you set up a cursor style that is compatible with the RecordCount attribute. But remember that different cursor styles have different amounts of server load and overhead. So it may be much more efficient to just count as you go, vs opening up a cursor style that you really don't need.