CFF KB - Carrz-Fox-Fire Promotions Knowledge Base

 CFF KB is all about 1 thing: The Sharing of Knowledge and the Power we gain from it.
  • Breadrumbs:

  • How to count records from Access or SQL Server database table

    • CFF Knowledge Base - Share With Facebook CFF Knowledge Base - Share on Twitter CFF Knowledge Base - Share on Reddit CFF Knowledge Base - Share on Digg It CFF Knowledge Base - Share on Stumble Upon It CFF Knowledge Base - Share on Delicious
    Share With Friends (Updated 6-8-2010)
  • Article ID:
    12
  • Date Created
    Wednesday, September 29, 2010
  • Last Updated
    Wednesday, November 17, 2010
  • This Article Has been Viewed
    1891 times
  • Short Desc
    There are a lot of different methods to counting records, some seem to be a little too much while others simply are error prone. Counting records is best done through your SQL Statement.
  • Details
    Counting Records
    To count the total number of records that are in the database, we can either count all of the records, or we can count records that are for a particular Query. We will demonstrate them both here.
  • Recreate Issue
    Counting records can be a tedious job if you are not sure how it is done, here we will explain 2 of the methods that are more popular in record counting with bother Access Database and SQL Server, so this works across them both, and I believe to also work in MySQL and possible Oracle, but am not really sure, will need confirmation.
  • Resolve Issue
    Method #1:
    Count all the records in your table.

    <%
    Set sql = CreateObject("ADODB.Command")
    sql.ActiveConnection=objConn
    sql.Prepared = true
    sql.commandtext="select count(MyID)as ctIDs from MyTable"
    set rsCt = sql.execute
    %>


    <%
    =rsCt("ctIDs")
    %>


    The above method will return a total count of all the records in the database.

    Method #2:
    <%
    'Count all the records for a certain user or Query.
    Set sql = CreateObject("ADODB.Command")
    sql.ActiveConnection=objConn
    sql.Prepared = true
    getID = Request.QueryString("id") ' ID = 1
    sql.commandtext="SELECT COUNT(RecordID) AS ctRID FROM MyTable GROUP BY MyID having MyID = ?"
    sql.Parameters.Append sql.CreateParameter("@MyID", adInteger, adParamInput, 25, getID)
    set rsCt = sql.execute
    %>


    <%
    =rsCt("ctRID")
    %>


    The above will return only the records that are associated with the MyID=?

Recent Articles

All Topics

  • Coming Soon - Knowledge Exchange

Trending Articles