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. " select * from student " , conn

Change this line to " 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. " 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.

Be the first to post comment on this article :

Post your comments , suggestion , error , requirements etc here .

We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2022 All rights reserved worldwide Privacy Policy Disclaimer