How to Connect to SQL Server to Retrieve Data from Query

Here is a good tutorial about different connection methods using Visual C++:
http://www.codeproject.com/database/connectionstrings.asp

Like dozens of sites, that site does give examples of connection strings for just about every RDBMS and technology, but doesn't provide any insight into how to put the pieces together and use the connection.

nabehs,

Thanks for the detailed information. That helps considerably. I still cannot get it to work as "rs" never gets populated; the debugger jumps right over the while loop.

I'll play around with both my connection string and query, though I'm guessing the connection string is okay as hr returns '0'.

This is certainly worth the points and more! I really appreciate your help and effort.

Thanks,
Jeff

If you have any difficulty, try to post the problematic section and I will check it out.

nabehs,

Thanks again, and I'll take you up on your offer. I've been up against a hard stop on another aspect of this project and haven't been able to try as many different approaches as I would like. If you spot something obvious I would appreciate you pointing it out. I'll include the code for the class too since this approach you have suggested is different than one I've seen before, so I probably have it coded incorrectly.

Here's a snippet from Query1.h
class CQuery1  
{
public:
      CQuery1();
      virtual ~CQuery1();

      char      m_szBaid[8];
      char      m_szVzid[8];
      void GetOtherUserId(LPCTSTR szUserId /*in*/, CString& strOtherUserId /*out*/);

      BEGIN_COLUMN_MAP (CQuery1)
            COLUMN_ENTRY (1, m_szBaid)
            COLUMN_ENTRY (2, m_szVzid)
      END_COLUMN_MAP()

};

Here's the function which is in my primary cpp file and not the Query1.cpp (not sure that is correct)
void CGetWkstnInfoDlg::GetOtherUserId(LPCTSTR szUserId, CString &strOtherUserId)
{
      CoInitialize (NULL);
      CCommand<CAccessor<CQuery1> > rs;
      CString strConnectionString = "Provider=sqloledb;Data Source=USNYMEN11WXX01;Initial Catalog=US1Migration;User Id=migrationUser;Password=!dataViewer";

      CDataSource ds;
    CSession session;
    HRESULT hr;

    CString strQuery;
    strQuery.Format("SELECT VZID FROM tblIDs WHERE BAID = '%s'", szUserId);
    // without error checking
    hr = ds.OpenFromInitializationString(strConnectionString.AllocSysString( ));
    hr = session.Open(ds);
    hr = rs.Open(session, LPCTSTR(strQuery));
    while(rs.MoveNext( ) == S_OK)
    {
        strOtherUserId = rs.m_szVzid;
        break;
    }
    rs.Close( );
    session.Close( );
    ds.Close( );

    CoUninitialize( );
}
 Thanks,
Jeff

you are returning only one column from the query VZID, but in the column map you have two columns COLUMN_ENTRY (1, m_szBaid) and COLUMN_ENTRY (2, m_szVzid). You have two options to correct this:

1. set the query statement to "SELECT BAID, VZID FROM tblIDs WHERE BAID = '%s'"

OR

2. delete the first column from the column map and it will look like this:

class CQuery1  
{
public:
     CQuery1();
     virtual ~CQuery1();

     char     m_szVzid[8];
     void GetOtherUserId(LPCTSTR szUserId /*in*/, CString& strOtherUserId /*out*/);

     BEGIN_COLUMN_MAP (CQuery1)
          COLUMN_ENTRY (1, m_szVzid)
     END_COLUMN_MAP()

};

Secondly, I wonder how you declared void GetOtherUserId(LPCTSTR szUserId /*in*/, CString& strOtherUserId /*out*/); in CQuery1 class but defined it in GetWkstnInfoDlg?

nabehs,

You are definitely hitting on my weak point, which is creating classes. Lately too, I've been pulled in other directions, so this piece that was hot enough for them to call me at home on a Saturday morning, cooled off for a little while. But it's coming back.

Okay, what would be the correct approach? Should I define the void GetOtherUserId(LPCTSTR szUserId /*in*/, CString& strOtherUserId /*out*/); function in the class CQuery1? That would make sense to me....place the function prototype in the header, the actual code in the CQuery1.cpp file, and then in my program define a variable of CQuery1 type? I've seen it done so many different ways that it does get confusing.

Thanks,
Jeff

I suggest that you define GetOtherUserId as a static function inside CQuery1, and whenever you want to use it you can call:

CString strOtherUserId;
CQuery1::GetOtherUserId(szUserId, strOtherUserId); // it will fill the strOtherUserId with the other user id

This way the class will encapsulate all its related functionality.

That is absolutely AWESOME! I really appreciate the help. I must admit I was amazed when it actually worked, but I can't tell you how much weight it took off my shoulders.

Thanks,
Jeff

my pleasure