#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    USA
    Posts
    144
    Rep Power
    12

    sql COUNT(*) command in C++


    Hi,

    I was trying to pass a count(*) sql command in c++ but my program keeps crashing. The count(*) command is supposed to return a value of how many rows met the criteria but I don't know the syntax for storing that variable in c++

    int returnedNumberOfMatches;

    CString sSql;
    sSql.Format(SELECT COUNT(*) FROM MyTable WHERE Name = 'Jo')

    returnedNumberOfMatches = recordset.Open(AFX_DAO_USE_DEFAULT_TYPE, sSql, NULL);


    This is how I accessed recordsets from a ms access database
    without problems:

    CString sSql;
    sSql.Format("SELECT * FROM Series WHERE Age = '%s' ", ageVar);
    recordset.Open(AFX_DAO_USE_DEFAULT_TYPE, sSql, NULL);

    I just can't get the count function working!
    Thanks!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Oct 2000
    Location
    Back in the real world.
    Posts
    5,966
    Rep Power
    190
    The count is extracted just like any other field too. For making access via field name easier, give an alias to it ("mycount" here).

    I have no idea how MS database access works in detail though...:
    Code:
    sSql.Format("SELECT COUNT(*) as mycount FROM MyTable WHERE Name = 'Jo'") 
    recordset.Open(AFX_DAO_USE_DEFAULT_TYPE, sSql, NULL); 
    // ... dunno how to retrieve the fields then, must be similar to:
    int numberofrows=recordset.GetFieldAsInteger("mycount");
    Also, you must not use the number of matches here. This would be "1" here since you retrieve one single value, the count.
    if you SELECTed * FROM MyTable, you could use the number of matches (which should be a distinct function, but not sure if MS has it).

    HTH,
    M.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    USA
    Posts
    144
    Rep Power
    12

    success, thanks!


    Thank you very very much M.Hirsch, that worked!

    Here's what I ended up with if anyone else needs it:

    CString sSql;
    sSql.Format("SELECT COUNT(*) as mycount FROM Series WHERE Name = 'jim'") ;

    recordset.Open(AFX_DAO_USE_DEFAULT_TYPE, sSql, NULL);

    long count = recordset.GetFieldValue("mycount").lVal;

    Now the variable count stores how many matches were made with the criteria you supply as an integer(how many records have jim in the field "name").

IMN logo majestic logo threadwatch logo seochat tools logo