Retrieving Data Using ADODB

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 have found the most examples using ADODB, so that is what I am trying to implement.

So, far, the code below is what I've come up with, but the program crashers when it calls: rec = con->Execute (ConStr, vRecordsAffected, 1);


I would appreciate any and all help.

Thanks,
Jeff


bool SetupProfileStrings ( )
{
      HRESULT                  hResult;
      CLSID                  clsid;
      char                  buf[MAX_PATH];
      TCHAR                  newbuf[MAX_PATH];
      char                  szUserName[NAMEBUF];
      char                  szLookup[NAMEBUF];
      char                  szProfilePath[PROFILEBUF];
      //FILE                  *fin;
      ifstream                            inFile;
      ADODB::_ConnectionPtr      con = NULL;
      ADODB::_RecordsetPtr      rec = NULL;
      ADODB::FieldPtr            pVal;
      HRESULT                  hr = S_OK;
      char                  ConStr[512];
      char                  strID[10];
      VARIANT                  *vRecordsAffected = NULL;
      int                  itype;

      ConStr[0] = '\0';
      lstrcat (ConStr, "Provider=sqloledb;Data Source=USNYMEN11WXX01;"
                  "Initial Catalog=US1Migration;"
                  "User Id=migrationUser;Password=!dataV");

      hr = con.CreateInstance (__uuidof(ADODB::Connection));
      con->Open (ConStr, "", "", 0);
      
      ConStr[0] = '\0';
      if ((szUserName[0] == 'b') || (szUserName[0] == 'B') ||
            (szUserName[0] == 'd') || (szUserName[0] == 'D'))
      {
                     lstrcat (ConStr, "SELECT VZID FROM tblIDs WHERE BAID=");
            lstrcat (ConStr, szUserName);
            itype = 0;
      }

      else if ((szUserName[0] == 'v') || (szUserName[0] == 'V') ||
            (szUserName[0] == 'z') || (szUserName[0] == 'Z'))
      {
            lstrcat (ConStr, "SELECT BAID FROM tblIDs WHERE VZID=");
            lstrcat (ConStr, szUserName);
            itype = 1;
      }
            
      rec = con->Execute (ConStr, vRecordsAffected, 1);
      //rec = con->OpenSchema(ADODB::adSchemaColumns, vtCriteria, vtMissing);

      //long limit = rec->GetFields()->Count;

      if (itype == 0)
            pVal = rec->Fields->GetItem ("BAID");
      else if (itype == 1)
            pVal = rec->Fields->GetItem ("VZID");

      vVal.Clear();
      vVal = pVal->Value;
      WideCharToMultiByte (CP_ACP, 0, vVal.bstrVal, -1, strID, sizeof (strID), NULL, NULL);

      MessageBox (NULL, szUserName, strID, MB_OK);      
      return true;
                }
}