• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6895
  • Last Modified:

How to Connect to SQL Server to Retrieve Data from Query

Earlier today, I had to change strategies for retrieving information that I need for a utility program I am working on. Essentially, the program performs some low level Windows profile translations as we move to a new domain structure (AD) and a new standardized ID convention.

We presently have two formats of standardized user IDs: one in our pre-merger format and used with the older NT domains, and a new standardized user ID. I have a table in SQL Server 2000 that contains two fields, one for each type of ID, and the table contains approximately 275,000 rows.

This program is going to be electronically distributed to roughly 25,000 users, and in the program I will call GetUserName to pick up the currently logged on user, and then test the user ID to see which of the two types of ID's it is. I then want to create a connection to the SQL server, and perform a query that will return the value of the other valid ID for that user.

In the past, when I have used MFC to connect to databases, it has been on a limited number of machines, so I could always create the system DSN that I used in my code to ensure that it worked. I don't think that approach would be practical in this situation.

What would be the quickest and easiest way for me to connect to the SQL database, retrieve the data via a query and close the connection? I have spent so much time reading about RDO, ADO, ODBC, ADO.NET that I am now totally confused and time is running out.

I would appreciate any and all help.

Thanks,
Jeff
0
jpetter
Asked:
jpetter
  • 5
  • 5
1 Solution
 
Jaime OlivaresCommented:
Here is a good tutorial about different connection methods using Visual C++:
http://www.codeproject.com/database/connectionstrings.asp
0
 
jpetterAuthor Commented:
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.

0
 
nabehsCommented:
1. In Class View, Right Click, New Class. From the Dialog Combo, select Generic Class, Enter the name CQuery1 and press OK. A new class is created with query1.cpp and query1.h files
2. Open query1.h file
3. Assume that you have the table Users with the following fields
user_id : varchar(xxx)
other_user_id : varchar(100)
4. Modify the class CQuery1 in query1.h to the following
class CQuery1
{
public:
    CQuery1();
    virtual ~CQuery1();

    CHAR m_szOtherUserId[101];// always character array size + 1. So you have varchar(100): you will need CHAR [101]

BEGIN_COLUMN_MAP(CQuery1)
    COLUMN_ENTRY(1, m_szOtherUserId)
END_COLUMN_MAP( )
};

5. In stdafx.h add the following line:
#include <atldbcli.h>

6. Add the following function to get the other user id from the existing user id:

#include "query1.h"
void GetOtherUserId(LPCTSTR szUserId /*in*/, CString& strOtherUserId /*out*/);

void GetOtherUserId(LPCTSTR szUserId, CString& strOtherUserId)
{
    CoInitialize(NULL);

    CCommand<CAccessor<CQuery1> > rs;
    CString strConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=MyDatabase;Data Source=MyServer;User Id=sa;Password=;";
    // Replace MyDatabase with your database name
    // Replace MyServer with the server name where SQL Server is installed
    // Replace sa with the user id
    // Replace the blank password with the correct password

    CDataSource ds;
    CSession session;
    HRESULT hr;

    CString strQuery;
    strQuery.Format("SELECT other_user_id FROM Users WHERE user_id = '%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_szOtherUserId;
        break;
    }
    rs.Close( );
    session.Close( );
    ds.Close( );

    CoUninitialize( );
}

7. Call this function and pass the user id to get the other user id

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jpetterAuthor Commented:
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
0
 
nabehsCommented:
If you have any difficulty, try to post the problematic section and I will check it out.
0
 
jpetterAuthor Commented:
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
0
 
nabehsCommented:
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?

0
 
jpetterAuthor Commented:
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
0
 
nabehsCommented:
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.

0
 
jpetterAuthor Commented:
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
0
 
nabehsCommented:
my pleasure
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now