There are a lot of times when we need to count and display the number of records we have in a database. There are two simple ways to do this, by either using DO…LOOP or by using COUNT in your SQL statement. Below are examples of both.
To use the DO…LOOP method, paste the below code into your page:
<!--#INCLUDE VIRTUAL="/includes/connection.asp" -->
<%
DIM mySQL, objRS
mySQL = "SELECT * FROM myTable"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open mySQL, objConn
' Count how many records exist
DIM iRecordCount
iRecordCount = 0
DO WHILE NOT objRS.EOF
iRecordCount = iRecordCount + 1
objRS.MoveNext
Loop
' Display result
Response.Write "(" & iRecordCount & ")"
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>
The SQL COUNT method uses a little less code. To use it, just paste this code into your page:
<!--#INCLUDE VIRTUAL="/includes/connection.asp" -->
<%
DIM mySQL, objRS
mySQL = "SELECT Count(*) AS intTotal FROM myTable"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open recSQL, objConn
' Display result
Response.Write objRS("intTotal")
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>
Either method will work. Like most things in ASP, it is really a matter of preference and what type of project you are working on. Happy counting!