Get a record count using SQL Statement?
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 12 of 12

Thread: Get a record count using SQL Statement?

  1. #1
    Join Date
    Feb 2001
    Posts
    2,455

    Get a record count using SQL Statement?

    Is it possible to get a record count, simply using an SQL Statement in c++ ado? I know you can do it by obtaining a _RecordsetPtr and calling GetRecordCount() but this requires you to open the recordset statically. Anyone have ideas on this?

    Mike B

  2. #2
    Join Date
    Aug 2001
    Location
    North Bend, WA
    Posts
    1,947
    select count(*) from tablename ...

  3. #3
    Join Date
    Feb 2001
    Posts
    2,455
    I thought about that one already, but where does the count returned go to?

    Do I use a _ConnectionPtr? _RecordsetPtr?

    CString csQuery = "SELECT COUNT (*) FROM MyTable WHERE MyField = somevlaue";

    int nCount = m_pConnection->ExecuteQuery(csQuery);

    This probably doesn't work? So how do I get the value?

    Mike B

  4. #4
    Join Date
    Jun 2002
    Location
    Clermont-Ferrand(France)
    Posts
    6
    Hi,

    in sql, you may rename your "complex" columns in a query using the "as" command.
    For example, "select count(*) as rcdnbr from myTable;" will bring you a single row with a signle column named "rcdnbr".
    cout << "Ciao";

  5. #5
    Join Date
    Feb 2001
    Posts
    2,455
    I still don't understand how I get the number from the select statment.

    If I was to use the following:

    CString csSelectWhat = _T("");
    csSelectWhat.Format("SELECT Fieldname From TableName");

    _RecordsetPtr pRecSet;
    try
    {
    pRecSet->OpenStatic("csSelectWhat", adCmdText, m_pConn);
    int nCount = pRecSet->GetRecordCount();
    }
    catch(...)
    {
    }
    catch(_com_error& e)
    {
    }

    How can I do this using a select statement using count?

    Mike B

  6. #6
    Join Date
    Sep 1999
    Location
    Germany, Hessen
    Posts
    226
    Originally posted by MikeB
    I still don't understand how I get the number from the select statment.

    If I was to use the following:

    [...snipped...]
    How can I do this using a select statement using count?
    Mike B
    1) when you want to use the GetRecordCount() (which is not working correctly under all conditions - not with all cursor types and not with all data providers. see ado documentation) you should do a "pRecSet->MoveLast()" before GetRecordCount(). And there are some initializations missing !!!! It looks like the problem is your use of ado and C++ (how do you want to pass the SQL command by passing it's name as String???? And I never saw an "OpenStatic" method in ADO's Recordset ...)! I think you get an Access Error, because the _RecordsetPtr points to nothing ... And you have no connection to a database in your Recordset ...

    2) To get the Record Count via an SQL command you should use the sql statement esmjun already posted. Here is the code to do that!

    Code:
    // Assuming you allready got an valid _ConnectionPtr to your database in Variable pAdoCon ...
    //  If you have probems initializing a _ConnectionPtr reply and give me 
    // the details about your database (type of db, path or whatever to open it).
    
    _bstr_t bsSelectWhat( L"SELECT COUNT(*) As [reccount] From TableName");
    
    _RecordsetPtr pRecSet;
    _variant_t vVal;
    long nCount;
    try {
         pRecSet.CreateInstance( __uuidof(Recordset) );
         pRecSet->Open( bsSelectWhat, (IDispatch*)pAdoCon, adOpenStatic, adLockOptimistic, adCmdText );
         vVal = pRecSet->GetCollect( L"reccount" );
         nCount = (long) vVal;
         pRecSet->Close();
    }
    catch(_com_error & e) {
         TRACE1( _T("Com Error: %s\", e.Description() );
    }
    Regards, Marco

  7. #7
    Join Date
    Feb 2001
    Posts
    2,455
    String???? And I never saw an "OpenStatic" method in ADO's
    LOL, Sorry about that, I actually created a custom ADO wrapper class and the OpenStatic is a method from that. I my own class mixed up with ADO functions.

    I will actually use the code you pasted and place it into "MY" GetRecordCount() function. Thanks for your reply.

    Mike B

  8. #8
    Join Date
    Sep 1999
    Location
    Germany, Hessen
    Posts
    226

    Talking

    Ok, that's explaining a lot ...

    On the first view - without knowing that - your code looked like you actually would have a lot of problems with the basic conecpts of ADO

    Oh - and another thing - just to remember you, don't forget that you might get another "real" RecordCount when using on a multi-user or multi-application environment when another process/thread/user inserts or deletes rows from your table just between the call of your "real" select stement and your "get record count"...

    Best regards, Marco
    Last edited by Marco F; June 27th, 2002 at 04:36 PM.

  9. #9
    Join Date
    Feb 2001
    Posts
    2,455
    I have one more question for you. What exactly is the [reccount] in your example? Is this a temporary field? A real field name? I don't understand how this portion of the select statment works?

    When I call GetCollect(L"reccount"), what exactly is happening here?

    Thank you very much for your help!

    Mike B

  10. #10
    Join Date
    Sep 1999
    Location
    Germany, Hessen
    Posts
    226
    Ok.

    (1) "AS" in SQL
    Count(*) is an "Aggregation function" in sql. it just counts the occurences of "*" (the rows). But you can't access this directly so you "give this thing a name", that you can access it like field.

    For Example:

    SELECT invoice.ID as ID_Invoice, customer.ID as ID_Customer, tableB.Name as Customer
    FROM invoice INNER JOIN customer ON invoice.CustId = customer.ID;

    that just 'renames' invoice.ID to ID_Invoice and so on. ID_INVOICE is called "alias" of invoice.ID.

    --------

    (2) GetCollect() Method of ADO Rs
    _variant_t ADODB::Recordset::GetCollect( _variant_t & index );

    GetCollect() returns the value of the column (field) which is described by the variant value in index.

    As Parameter you can pass:
    - the number (index) of that column (field) when variants vartype is a numeric type (VT_I4 ? I never referred to fields by index)
    or
    - its name when variants type is VT_BSTR.

    L"someString" makes a BSTR, so its autamtically converted to a Variant of VT_BSTR type.

    -----------

    Regards, Marco

  11. #11
    Join Date
    Sep 1999
    Location
    Germany, Hessen
    Posts
    226
    what i forgot:
    the [] brackets arround [reccount] are only "string terminators" for names of tables or colums in SQL, you can leave the if you don't have a space or some other token in the name of the field.

  12. #12
    Join Date
    Feb 2001
    Posts
    2,455
    Thank you very much. GREAT!!! answer. I knew about the brackets [] simply because I have a field named Level which also happened to be an SQL key word, so I learned about that the hard way! lol

    Mike B

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center