| | |
Counting the records in a MSSQL Table |
It is very often required to know the total records present in a table or number of records matching to our query. If we are simply displaying the records then we can use one counter variable to count the number of records within the loop. There is a built in RecordCount method of the RecordSet object. To use this RecordCount method in our MSSQL table we have to use scrollable cursor. If we are not using a scrollable cursor and using a forward-only cursor then RecordCount method will return -1.
Now let us change our script as explained in the basic select query tutorial. We will only discuss the changes required here.
There is a line opening the cursor in forward only mode.
rs1.open " select * from student " , conn
Change this line to
rs1.open " select * from student " , conn , adOpenKeySet
Now we can add the line to display the total number of records before the Do while loop line this
Response.Write "Total " & rs1.RecordCount & " Records"
Using Count SQL command to know the total records.
Now you must have understood the purpose of using scrollable cursor to count the records. We can use the command COUNT ( read more on SQL COUNT Here )to get the total records present in our query or table. This command is a SQL part so no need to use any scrollable cursor. Here is a simple query to get the total number of records present.
rs1.open " select count(*) as no from student " , conn
Response.Write "Total " & rs1("no") & " Records"
However Count command can be used along with GROUP BY command to develop powerful reports. We will discuss that in our next tutorial.
| |
|
|
|
