How to count the total record queried by SQL?

Normally, we can use the RecordCount to get the total record in a Recordset. But the problem is that you only need the total record and do not need to query all records to the Recordset.

In this case, Count command in the SQL can help you:

<%

Set conn = Server.CreateObject("Adodb.Connection")

conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("db1.mdb")

Set rs = Server.CreateObject("Adodb.Recordset")

strMember="Jack"

strSQL ="SELECT Count(*) As CountItem FROM Sales WHERE Buyer='" & strMember & "';" 'Select all sales transaction that make by a member; and count How many thing that the member purchase.

rs.Open strSQL,conn,1,3

If Not rs.EOF Then

Response.Write("The total thing that purchase by the member is " & rs.fields("CountItem") & "
")

End If

rs.Close

Set rs = Nothing

%>