Connect MFC using ADO and Access

How do you connect an Access 2000 database with MFC (Visual Studio 6.0) using ADO?
plonerganAsked:
Who is Participating?
 
zhoujohnsonConnect With a Mentor Commented:
// Helpers.h: interface for the CHelpers class.
//

#if !defined(AFX_HELPERS_H__DF0ED2F1_BED3_11D2_BDAA_204C4F4F5020__INCLUDED_)
#define AFX_HELPERS_H__DF0ED2F1_BED3_11D2_BDAA_204C4F4F5020__INCLUDED_

#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000

//////////////////////////////////////////////////////////////////////
// CHelpers

class CHelpers
{
public:
//      static int Insert(CListCtrlEx* pListCtrl, CString& sRow, int nImage = -1,
//            int nIndex = -1);
      static CString GetFileExceptionError(const int& nCause);
      static CString GetType(const int& nType);
      static CString CrackStrVariant(const COleVariant& var);
      static CString GetPropertyAttributes(const int& nPropertyAttributesEnum);
      ~CHelpers();
private:
      CHelpers();
};
// Helpers.cpp: implementation of the CHelpers class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "d:/cell/ado/ado.h"
#include "Helpers.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

//////////////////////////////////////////////////////////////////////
// CHelpers

CHelpers::CHelpers()
{
}

CHelpers::~CHelpers()
{
}

//int CHelpers::Insert(CListCtrlEx* pListCtrl, CString& sRow, int nImage/* = -1*/,
//            int nIndex/* = -1*/)
//{
//      ASSERT(pListCtrl);
//
//      int nItemIndex = -1;
//      int nEnd = sRow.Find(_T("|"));
//      if(nEnd != -1)
//      {
//            int nSubItem = 0;
//            LV_ITEM lvItem;
//            CString sColVal;
//            lvItem.mask = LVIF_TEXT;
//            
//            if(nImage != -1)
//            {
//                  lvItem.mask |= LVIF_IMAGE;
//                  lvItem.iImage = nImage;
//            }
//            
//            if(nIndex != -1)
//                  lvItem.iItem = nIndex;
//
//            lvItem.iSubItem = nSubItem++;
//            sColVal = sRow.Mid(0, nEnd);
//            lvItem.pszText = sColVal.GetBuffer(sColVal.GetLength()+1);
//            sColVal.ReleaseBuffer();
//            nItemIndex       = pListCtrl->InsertItem(&lvItem);
//            ASSERT(nItemIndex != -1);
//            if(nItemIndex != -1)
//            {
//                  while(sRow.GetLength() > nEnd)
//                  {              
//                        sRow = sRow.Mid(nEnd + 1);
//                        nEnd = sRow.Find(_T("|"));
//                        if(nEnd == -1)
//                              break;
//                        
//                        lvItem.iItem = nItemIndex;
//                        lvItem.iSubItem = nSubItem++;
//                        sColVal = sRow.Mid(0, nEnd);
//                        lvItem.pszText = sColVal.GetBuffer(sColVal.GetLength()+1);
//                        sColVal.ReleaseBuffer();
//                        pListCtrl->SetItem(&lvItem);
//                  }
//            }
//      }
//
//      return nItemIndex;
//}

CString CHelpers::GetFileExceptionError(const int& nCause)
{
      CString sBuff(_T("An unspecified error occurred."));
      
      switch(nCause)
      {
            case CFileException::fileNotFound:
                  sBuff = _T("The file could not be located.");
                  break;
            
            case CFileException::badPath:
                  sBuff = _T("All or part of the path is invalid.");
                  break;
            
            case CFileException::tooManyOpenFiles:
                  sBuff = _T("The permitted number of open files was exceeded.");
                  break;
            
            case CFileException::accessDenied:
                  sBuff = _T("The file could not be accessed.");
                  break;

            case CFileException::invalidFile:
                  sBuff = _T("There was an attempt to use an invalid file handle.");
                  break;
            
            case CFileException::removeCurrentDir:
                  sBuff = _T("The current working directory cannot be removed.");
                  break;
            
            case CFileException::directoryFull:
                  sBuff = _T("There are no more directory entries.");
                  break;
                  
            case CFileException::badSeek:
                  sBuff = _T("There was an error trying to set the file pointer.");
                  break;
                  
            case CFileException::hardIO:
                  sBuff = _T("There was a hardware error.");
                  break;

            case CFileException::sharingViolation:
                  sBuff = _T("SHARE.EXE was not loaded, or a shared region was locked.");
                  break;
                  
            case CFileException::lockViolation:
                  sBuff = _T("There was an attempt to lock a region that was already locked.");
                  break;
                  
            case CFileException::diskFull:
                  sBuff = _T("The disk is full.");
                  break;
            
            case CFileException::endOfFile:
                  sBuff = _T("The end of file was reached.");
                  break;
      }

      return sBuff;
}

CString CHelpers::GetType(const int& nType)
{
      CString strTmp;
      
      switch(nType)
      {
         case adBigInt:
               strTmp = "(adBigInt) An 8-byte signed integer";
               break;
         case adBinary:
               strTmp = "(adBinary) A binary value";
               break;
         case adBoolean:
               strTmp = "(adBoolean) A Boolean value";
               break;
         case adBSTR:
               strTmp = "(adBSTR) A null-terminated character string (Unicode)";
               break;
         case adChar:
               strTmp = "(adChar) A String value";
               break;
         case adCurrency:
               strTmp = "(adCurrency) A currency value (8-byte signed integer scaled by 10,000)";
               break;
         case adDate:
               strTmp = "(adDate) A Date value";
               break;
         case adDBDate:
               strTmp = "(adDBDate) A date value (yyyymmdd)";
               break;
         case adDBTime:
               strTmp = "(adDBTime) A time value (hhmmss)";
               break;
         case adDBTimeStamp:
               strTmp = "(adDBTimeStamp) A date-time stamp (yyyymmddhhmmss plus a fraction in billionths)";
               break;
         case adDecimal:
               strTmp = "(adDecimal) An exact numeric value with a fixed precision and scale";
               break;
         case adDouble:
               strTmp = "(adDouble) A double-precision floating point value";
               break;
         case adEmpty:
               strTmp = "(adEmpty) No value was specified";
               break;
         case adError:
               strTmp = "(adError) A 32-bit Error code";
               break;
         case adGUID:
               strTmp = "(adGUID) A globally unique identifier (GUID)";
               break;
         case adIDispatch:
               strTmp = "(adIDispatch) A pointer to an IDispatch interface on an OLE object";
               break;
         case adInteger:
               strTmp = "(adInteger) A 4-byte signed integer";
               break;
         case adIUnknown:
               strTmp = "(adIUnknown) A pointer to an IUnknown interface on an OLE object";
               break;
         case adLongVarBinary:
               strTmp = "(adLongVarBinary) A long binary value (Parameter object only)";
               break;
         case adLongVarChar:
               strTmp = "(adLongVarChar) A long String value (Parameter object only)";
               break;
         case adLongVarWChar:
               strTmp = "(adLongVarWChar) A long null-terminated string value (Parameter object only)";
               break;
         case adNumeric:
               strTmp = "(adNumeric) An exact numeric value with a fixed precision and scale";
               break;
         case adSingle:
               strTmp = "(adSingle) A single-precision floating point value";
               break;
         case adSmallInt:
               strTmp = "(adSmallInt) A 2-byte signed integer";
               break;
         case adTinyInt:
               strTmp = "(adTinyInt) A 1-byte signed integer";
               break;
         case adUnsignedBigInt:
               strTmp = "(adUnsignedBigInt) An 8-byte unsigned integer";
               break;
         case adUnsignedInt:
               strTmp = "(adUnsignedInt) A 4-byte unsigned integer";
               break;
         case adUnsignedSmallInt:
               strTmp = "(adUnsignedSmallInt) A 2-byte unsigned integer";
               break;
         case adUnsignedTinyInt:
               strTmp = "(adUnsignedTinyInt) A 1-byte unsigned integer";
               break;
         case adUserDefined:
               strTmp = "(adUserDefined) A user-defined variable";
               break;
         case adVarBinary:
               strTmp = "(adVarBinary) A binary value (Parameter object only)";
               break;
         case adVarChar:
               strTmp = "(adVarChar) A String value (Parameter object only)";
               break;
         case adVariant:
               strTmp = "(adVariant) An OLE Automation Variant";
               break;
         case adVarWChar:
               strTmp = "(adVarWChar) A null-terminated Unicode character string (Parameter object only)";
               break;
         case adWChar:
               strTmp = "(adWChar) A null-terminated Unicode character string";
               break;
         default:  
               strTmp.Format(_T("%d = = Unrecognized Type"), nType);
   }
   
   return strTmp;
}

// CrackStrVariant() taken from the CCrack::strVariant method found in the
// DAOVIEW sample that ships with VC 5.0/6.0
CString CHelpers::CrackStrVariant(const COleVariant& var)
{
    CString strRet;
    strRet = _T("<Unknown>");
    switch(var.vt)
      {
            case VT_EMPTY:
            case VT_NULL:
                  strRet = _T("NULL");
                  break;
            case VT_I2:
                  strRet.Format(_T("%hd"), V_I2(&var));
                  break;
            case VT_I4:
                  strRet.Format(_T("%d"),V_I4(&var));
                  break;
            case VT_R4:
                  strRet.Format(_T("%e"), (double)V_R4(&var));
                  break;
            case VT_R8:
                  strRet.Format(_T("%e"), V_R8(&var));
                  break;
            case VT_CY:
                  strRet = COleCurrency(var).Format();
                  break;
            case VT_DATE:
                  strRet = COleDateTime(var).Format(_T("%c"));
                  break;
            case VT_BSTR:
                  strRet = V_BSTR(&var);
                  break;
            case VT_DISPATCH:
                  strRet = _T("VT_DISPATCH");
                  break;
            case VT_ERROR:
                  strRet = _T("VT_ERROR");
                  break;
            case VT_BOOL:
                  return ( V_BOOL(&var) ? _T("TRUE") : _T("FALSE"));
            case VT_VARIANT:
                  strRet = _T("VT_VARIANT");
                  break;
            case VT_UNKNOWN:
                  strRet = _T("VT_UNKNOWN");
                  break;
            case VT_I1:
                  strRet = _T("VT_I1");
                  break;
            case VT_UI1:
                  strRet.Format(_T("0x%02hX"), (unsigned short)V_UI1(&var));
                  break;
            case VT_UI2:
                  strRet = _T("VT_UI2");
                  break;
            case VT_UI4:
                  strRet = _T("VT_UI4");
                  break;
            case VT_I8:
                  strRet = _T("VT_I8");
                  break;
            case VT_UI8:
                  strRet = _T("VT_UI8");
                  break;
            case VT_INT:
                  strRet = _T("VT_INT");
                  break;
            case VT_UINT:
                  strRet = _T("VT_UINT");
                  break;
            case VT_VOID:
                  strRet = _T("VT_VOID");
                  break;
            case VT_HRESULT:
                  strRet = _T("VT_HRESULT");
                  break;
            case VT_PTR:
                  strRet = _T("VT_PTR");
                  break;
            case VT_SAFEARRAY:
                  strRet = _T("VT_SAFEARRAY");
                  break;
            case VT_CARRAY:
                  strRet = _T("VT_CARRAY");
                  break;
            case VT_USERDEFINED:
                  strRet = _T("VT_USERDEFINED");
                  break;
            case VT_LPSTR:
                  strRet = _T("VT_LPSTR");
                  break;
            case VT_LPWSTR:
                  strRet = _T("VT_LPWSTR");
                  break;
            case VT_FILETIME:
                  strRet = _T("VT_FILETIME");
                  break;
            case VT_BLOB:
                  strRet = _T("VT_BLOB");
                  break;
            case VT_STREAM:
                  strRet = _T("VT_STREAM");
                  break;
            case VT_STORAGE:
                  strRet = _T("VT_STORAGE");
                  break;
            case VT_STREAMED_OBJECT:
                  strRet = _T("VT_STREAMED_OBJECT");
                  break;
            case VT_STORED_OBJECT:
                  strRet = _T("VT_STORED_OBJECT");
                  break;
            case VT_BLOB_OBJECT:
                  strRet = _T("VT_BLOB_OBJECT");
                  break;
            case VT_CF:
                  strRet = _T("VT_CF");
                  break;
            case VT_CLSID:
                  strRet = _T("VT_CLSID");
                  break;
    }
   
      WORD vt = var.vt;
    if(vt & VT_ARRAY)
      {
        vt = vt & ~VT_ARRAY;
        strRet = _T("Array of ");
    }
   
      if(vt & VT_BYREF)
      {
        vt = vt & ~VT_BYREF;
        strRet += _T("Pointer to ");
    }
   
      if(vt != var.vt)
      {
        switch(vt)
            {
                  case VT_EMPTY:
                        strRet += _T("VT_EMPTY");
                        break;
                  case VT_NULL:
                        strRet += _T("VT_NULL");
                        break;
                  case VT_I2:
                        strRet += _T("VT_I2");
                        break;
                  case VT_I4:
                        strRet += _T("VT_I4");
                        break;
                  case VT_R4:
                        strRet += _T("VT_R4");
                        break;
                  case VT_R8:
                        strRet += _T("VT_R8");
                        break;
                  case VT_CY:
                        strRet += _T("VT_CY");
                        break;
                  case VT_DATE:
                        strRet += _T("VT_DATE");
                        break;
                  case VT_BSTR:
                        strRet += _T("VT_BSTR");
                        break;
                  case VT_DISPATCH:
                        strRet += _T("VT_DISPATCH");
                        break;
                  case VT_ERROR:
                        strRet += _T("VT_ERROR");
                        break;
                  case VT_BOOL:
                        strRet += _T("VT_BOOL");
                        break;
                  case VT_VARIANT:
                        strRet += _T("VT_VARIANT");
                        break;
                  case VT_UNKNOWN:
                        strRet += _T("VT_UNKNOWN");
                        break;
                  case VT_I1:
                        strRet += _T("VT_I1");
                        break;
                  case VT_UI1:
                        strRet += _T("VT_UI1");
                        break;
                  case VT_UI2:
                        strRet += _T("VT_UI2");
                        break;
                  case VT_UI4:
                        strRet += _T("VT_UI4");
                        break;
                  case VT_I8:
                        strRet += _T("VT_I8");
                        break;
                  case VT_UI8:
                        strRet += _T("VT_UI8");
                        break;
                  case VT_INT:
                        strRet += _T("VT_INT");
                        break;
                  case VT_UINT:
                        strRet += _T("VT_UINT");
                        break;
                  case VT_VOID:
                        strRet += _T("VT_VOID");
                        break;
                  case VT_HRESULT:
                        strRet += _T("VT_HRESULT");
                        break;
                  case VT_PTR:
                        strRet += _T("VT_PTR");
                        break;
                  case VT_SAFEARRAY:
                        strRet += _T("VT_SAFEARRAY");
                        break;
                  case VT_CARRAY:
                        strRet += _T("VT_CARRAY");
                        break;
                  case VT_USERDEFINED:
                        strRet += _T("VT_USERDEFINED");
                        break;
                  case VT_LPSTR:
                        strRet += _T("VT_LPSTR");
                        break;
                  case VT_LPWSTR:
                        strRet += _T("VT_LPWSTR");
                        break;
                  case VT_FILETIME:
                        strRet += _T("VT_FILETIME");
                        break;
                  case VT_BLOB:
                        strRet += _T("VT_BLOB");
                        break;
                  case VT_STREAM:
                        strRet += _T("VT_STREAM");
                        break;
                  case VT_STORAGE:
                        strRet += _T("VT_STORAGE");
                        break;
                  case VT_STREAMED_OBJECT:
                        strRet += _T("VT_STREAMED_OBJECT");
                        break;
                  case VT_STORED_OBJECT:
                        strRet += _T("VT_STORED_OBJECT");
                        break;
                  case VT_BLOB_OBJECT:
                        strRet += _T("VT_BLOB_OBJECT");
                        break;
                  case VT_CF:
                        strRet += _T("VT_CF");
                        break;
                  case VT_CLSID:
                        strRet += _T("VT_CLSID");
                        break;
        }
    }
   
      return strRet;
}

CString CHelpers::GetPropertyAttributes(const int& nPropertyAttributesEnum)
{
      CString strTmp(_T(""));
      
    if(nPropertyAttributesEnum & adPropNotSupported)
            strTmp += _T("NotSupported ");
    else if(nPropertyAttributesEnum & adPropRequired)
            strTmp += _T("Required ");
    else if(nPropertyAttributesEnum & adPropOptional)
            strTmp += _T("Optional ");
    else if(nPropertyAttributesEnum & adPropRead)
            strTmp += _T("Read ");
    else if(nPropertyAttributesEnum & adPropWrite)
            strTmp += _T("Write ");

    return strTmp;
}

#ifndef _ADO_H_
#define _ADO_H_

#if _MSC_VER >= 1000
#pragma once
#endif // _MSC_VER >= 1000
#include "stdafx.h"

#import "C:\Program Files\Common Files\System\ADO\msado15.dll" rename_namespace("ADOCG") rename("EOF", "EndOfFile")
using namespace ADOCG;
#include "icrsint.h"

// ado.h : header file
//

#define SCHEMA_TABLE                  0
#define SCHEMA_DATABASE                  1
#define SCHEMA_PROCEDURE            2
#define SCHEMA_FIELDS                  3


struct CAdoFieldInfo
{
      // The name of field
      char m_strName[30];
      // the field type
      short m_nType;
      // The field size
      long m_lSize;
      // the defined field size
      long m_lDefinedSize;
      // the attributes of field
      long m_lAttributes;
      // the ordinal position of current field
      short m_nOrdinalPosition;
      //
      BOOL m_bRequired;  
      BOOL m_bAllowZeroLength;
      long m_lCollatingOrder;  
};

CString IntToStr(int nVal);

CString LongToStr(long lVal);

class CADODatabase
{
public:
      bool Execute(LPCTSTR lpstrExec);
      CADODatabase()
      {
            ::CoInitialize(NULL);
                  
            m_pConnection = NULL;
            m_strConnection = _T("");
            m_pConnection.CreateInstance(__uuidof(Connection));
      }
      
      ~CADODatabase()
      {
            Close();
            //====
            //  ||
            //  \/

            m_pConnection.Release();
            m_pConnection = NULL;
            ::CoUninitialize();
      }
      
      bool Open(LPCTSTR lpstrConnection = _T(""));
      _ConnectionPtr GetActiveConnection() {return m_pConnection;};
      DWORD GetRecordCount(_RecordsetPtr m_pRs);
      long BeginTransaction()
            {return m_pConnection->BeginTrans();};
      long CommitTransaction()
            {return m_pConnection->CommitTrans();};
      long RollbackTransaction()
            {return m_pConnection->RollbackTrans();};
      bool IsOpen();
      void Close();
      void SetConnectionString(LPCTSTR lpstrConnection)
            {m_strConnection = lpstrConnection;};
      CString GetConnectionString()
            {return m_strConnection;};
      CString GetLastError()
            {return m_strLastError;};
protected:
      void dump_com_error(_com_error &e);

protected:
      _ConnectionPtr m_pConnection;
      CString m_strConnection;
      CString m_strLastError;
};

class CADORecordset
{
public:
      CString GetFieldValueByString( int i );
      BOOL OpenSchema(_ConnectionPtr mpdb,int schema,CString strTable);
      int GetNumFields();
      CADORecordset()
      {
            m_pRecordset = NULL;
            m_pCmd = NULL;
            m_strQuery = _T("");
            m_pRecordset.CreateInstance(__uuidof(Recordset));
            m_pCmd.CreateInstance(__uuidof(Command));
      }
      ~CADORecordset()
      {
            Close();
            m_pRecordset.Release();
            m_pCmd.Release();
            m_pRecordset = NULL;
            m_pCmd = NULL;
            m_strQuery = _T("");
      }

      CString GetQuery()
            {return m_strQuery;};
      void SetQuery(LPCSTR strQuery)
            {m_strQuery = strQuery;};
      bool RecordBinding(CADORecordBinding &pAdoRecordBinding);
      DWORD GetRecordCount();
      bool IsOpen();
      void Close();
      bool Open(_ConnectionPtr mpdb, LPCTSTR lpstrExec = _T(""));
      bool GetFieldValue(LPCTSTR lpFieldName, double& dbValue);
      bool GetFieldValue(int nIndex, double& dbValue);
      bool GetFieldValue(LPCTSTR lpFieldName, long& lValue);
      bool GetFieldValue(int nIndex, long& lValue);
      bool GetFieldValue(LPCTSTR lpFieldName, int& nValue);
      bool GetFieldValue(int nIndex, int& nValue);
      bool GetFieldValue(LPCTSTR lpFieldName, CString& strValue);
      bool GetFieldValue(int nIndex, CString& strValue);
      bool GetFieldValue(LPCTSTR lpFieldName, COleDateTime& time);
      bool GetFieldValue(int nIndex, COleDateTime& time);
      bool IsFieldNull(LPCTSTR lpFieldName);
      bool IsFieldNull(int nIndex);
      bool IsFieldEmpty(LPCTSTR lpFieldName);
      bool IsFieldEmpty(int nIndex);      
      bool IsEof()
            {return m_pRecordset->EndOfFile == VARIANT_TRUE;};
      bool IsBof()
            {return m_pRecordset->BOF == VARIANT_TRUE;};
      void MoveFirst()
            {m_pRecordset->MoveFirst();};
      void MoveNext()
            {m_pRecordset->MoveNext();};
      void MovePrevious()
            {m_pRecordset->MovePrevious();};
      void MoveLast()
            {m_pRecordset->MoveLast();};
      long GetAbsolutePage()
            {return m_pRecordset->GetAbsolutePage();};
      void SetAbsolutePage(int nPage)
            {m_pRecordset->PutAbsolutePage((enum PositionEnum)nPage);};
      long GetPageCount()
            {return m_pRecordset->GetPageCount();};
      long GetPageSize()
            {return m_pRecordset->GetPageSize();};
      void SetPageSize(int nSize)
            {m_pRecordset->PutPageSize(nSize);};
      long GetAbsolutePosition()
            {return m_pRecordset->GetAbsolutePosition();};
      void SetAbsolutePosition(int nPosition)
            {m_pRecordset->PutAbsolutePosition((enum PositionEnum)nPosition);};
      bool GetFieldInfo(LPCTSTR lpFieldName, CAdoFieldInfo* fldInfo);
      bool GetFieldInfo(int nIndex, CAdoFieldInfo* fldInfo);
      bool GetChunk(LPCTSTR lpFieldName, CString& strValue);
      CString GetString(LPCTSTR lpCols, LPCTSTR lpRows, LPCTSTR lpNull, long numRows = 0);
      CString GetLastError() {return m_strLastError;};

protected:
      CString m_strLastError;
      void dump_com_error(_com_error &e);
      _RecordsetPtr m_pRecordset;
      _CommandPtr m_pCmd;
      CString m_strQuery;
};

#endif







#include "stdafx.h"
#include "ado.h"
#include "helpers.h"
#include <afxdisp.h>

DWORD CADODatabase::GetRecordCount(_RecordsetPtr m_pRs)
{
      DWORD numRows = 0;
      
      numRows = m_pRs->GetRecordCount();

      if(numRows == -1)
      {
            if(m_pRs->EndOfFile != VARIANT_TRUE)
                  m_pRs->MoveFirst();

            while(m_pRs->EndOfFile != VARIANT_TRUE)
            {
                  numRows++;
                  m_pRs->MoveNext();
            }
            if(numRows > 0)
                  m_pRs->MoveFirst();
      }
      return numRows;
}

bool CADODatabase::Open(LPCTSTR lpstrConnection)
{
      HRESULT hr = S_OK;

      if(IsOpen())
            Close();

      if(strcmp(lpstrConnection, _T("")) != 0)
            m_strConnection = lpstrConnection;

      ASSERT(!m_strConnection.IsEmpty());

      try
      {
            hr = m_pConnection->Open(_bstr_t(m_strConnection), "", "", NULL);
            return hr == S_OK;
      }
      catch(_com_error &e)
      {
            dump_com_error(e);
      }
      return false;
}

void CADODatabase::dump_com_error(_com_error &e)
{
      CString ErrorStr;
      
      
      _bstr_t bstrSource(e.Source());
      _bstr_t bstrDescription(e.Description());
      ErrorStr.Format( "CADODataBase Error\n\tCode = %08lx\n\tCode meaning = %s\n\tSource = %s\n\tDescription = %s\n",
            e.Error(), e.ErrorMessage(), (LPCSTR)bstrSource, (LPCSTR)bstrDescription );
      m_strLastError = _T("Connection String = " + GetConnectionString() + '\n' + ErrorStr);
      #ifdef _DEBUG
            AfxMessageBox( ErrorStr, MB_OK | MB_ICONERROR );
      #endif      
}

bool CADODatabase::IsOpen()
{
      if(m_pConnection )
            return m_pConnection->GetState() != adStateClosed;
      return false;
}

void CADODatabase::Close()
{
      if(IsOpen())
            m_pConnection->Close();
}

bool CADORecordset::Open(_ConnectionPtr mpdb, LPCTSTR lpstrExec)
{      
      Close();
      
      if(strcmp(lpstrExec, _T("")) != 0)
            m_strQuery = lpstrExec;

      ASSERT(!m_strQuery.IsEmpty());
      
      m_strQuery.TrimLeft();
      BOOL bIsSelect = m_strQuery.Mid(0, strlen("Select ")).CompareNoCase("select ") == 0;

      try
      {
            if(bIsSelect)
                  m_pRecordset->Open((LPCSTR)m_strQuery, _variant_t((IDispatch*)mpdb, true),
                                          adOpenStatic, adLockOptimistic, adCmdUnknown);
            else
            {
                  m_pCmd->ActiveConnection = mpdb;
                  m_pCmd->CommandText = _bstr_t(m_strQuery);
                  m_pCmd->CommandType = adCmdStoredProc;
                  
                  m_pRecordset = m_pCmd->Execute(NULL, NULL, adCmdText);
            }
      }
      catch(_com_error &e)
      {
            dump_com_error(e);
            return FALSE;
      }

      return m_pRecordset != NULL;
}

bool CADORecordset::GetFieldValue(LPCTSTR lpFieldName, double& dbValue)
{      
      double val = (double)NULL;
      _variant_t vtFld;
      
      vtFld = m_pRecordset->Fields->GetItem(lpFieldName)->Value;
      if(vtFld.vt != VT_NULL)
            val = vtFld.dblVal;
      dbValue = val;
      return true;
}


bool CADORecordset::GetFieldValue(LPCTSTR lpFieldName, long& lValue)
{
      long val = (long)NULL;
      _variant_t vtFld;
      
      vtFld = m_pRecordset->Fields->GetItem(lpFieldName)->Value;
      if(vtFld.vt != VT_NULL)
            val = vtFld.lVal;
      lValue = val;
      return true;
}

bool CADORecordset::GetFieldValue(LPCTSTR lpFieldName, int& nValue)
{
      int val = NULL;
      _variant_t vtFld;
      
      vtFld = m_pRecordset->Fields->GetItem(lpFieldName)->Value;
      switch(vtFld.vt)
      {
      case VT_I2:
            val = vtFld.iVal;
            break;
      case VT_BOOL:
            val = vtFld.boolVal;
      case VT_NULL:
      case VT_EMPTY:
            break;
      default:
            nValue = 0;
            return false;
      }      
      nValue = val;
      return true;
}

bool CADORecordset::IsFieldNull(LPCTSTR lpFieldName)
{
      _variant_t vtFld;
      
      vtFld = m_pRecordset->Fields->GetItem(lpFieldName)->Value;
      return vtFld.vt == VT_NULL;
}

bool CADORecordset::IsFieldNull(int nIndex)
{
      _variant_t vtFld;
      _variant_t vtIndex;

      vtIndex.vt = VT_I2;
      vtIndex.iVal = nIndex;
      
      vtFld = m_pRecordset->Fields->GetItem(vtIndex)->Value;
      return vtFld.vt == VT_NULL;
}

bool CADORecordset::IsFieldEmpty(LPCTSTR lpFieldName)
{
      _variant_t vtFld;
      
      vtFld = m_pRecordset->Fields->GetItem(lpFieldName)->Value;
      return vtFld.vt == VT_EMPTY || vtFld.vt == VT_NULL;
}

bool CADORecordset::IsFieldEmpty(int nIndex)
{
      _variant_t vtFld;
      _variant_t vtIndex;
      
      vtIndex.vt = VT_I2;
      vtIndex.iVal = nIndex;
            
      vtFld = m_pRecordset->Fields->GetItem(vtIndex)->Value;
      return vtFld.vt == VT_EMPTY || vtFld.vt == VT_NULL;
}

bool CADORecordset::GetFieldValue(int nIndex, int& nValue)
{
      int val = (int)NULL;
      _variant_t vtFld;
      _variant_t vtIndex;
      
      vtIndex.vt = VT_I2;
      vtIndex.iVal = nIndex;
      vtFld = m_pRecordset->Fields->GetItem(vtIndex)->Value;
      switch(vtFld.vt)
      {
      case VT_I2:
            val = vtFld.iVal;
            break;
      case VT_NULL:
      case VT_EMPTY:
            val = 0;
            break;
      default:
            return false;
      }      
      nValue = val;
      return true;
}

bool CADORecordset::GetFieldValue(LPCTSTR lpFieldName, CString& strValue)
{
      CString str = _T("");
      _variant_t vtFld;
      
      vtFld = m_pRecordset->Fields->GetItem(lpFieldName)->Value;
      switch(vtFld.vt)
      {
      case VT_BSTR:
            str = vtFld.bstrVal;
            break;
      case VT_I4:
            str = IntToStr(vtFld.iVal);
            break;
      case VT_DATE:
            {
                  COleDateTime dt(vtFld);

                  str = dt.Format("%Y-%m-%d %H:%M:%S");
            }
            break;
      case VT_EMPTY:
      case VT_NULL:
            break;
      default:
            strValue.Empty();
            return false;
      }
      strValue = str;
      return true;
}

bool CADORecordset::GetFieldValue(int nIndex, CString& strValue)
{
      CString str = _T("");
      _variant_t vtFld;
      _variant_t vtIndex;

      vtIndex.vt = VT_I2;
      vtIndex.iVal = nIndex;
      
      vtFld = m_pRecordset->Fields->GetItem(vtIndex)->Value;
      switch(vtFld.vt)
      {
      case VT_BSTR:
            str = vtFld.bstrVal;
            break;
      case VT_DATE:
            {
                  COleDateTime dt(vtFld);
                  
                  str = dt.Format("%Y-%m-%d %H:%M:%S");
            }
            break;
      case VT_EMPTY:
      case VT_NULL:
            break;
      default:
            strValue.Empty();
            return false;
      }
      strValue = str;
      return true;
}

bool CADORecordset::GetFieldValue(LPCTSTR lpFieldName, COleDateTime& time)
{
      _variant_t vtFld;
      
      vtFld = m_pRecordset->Fields->GetItem(lpFieldName)->Value;
      switch(vtFld.vt)
      {
      case VT_DATE:
            {
                  COleDateTime dt(vtFld);
                  time = dt;
            }
            break;
      case VT_EMPTY:
      case VT_NULL:
            break;
      default:
            return false;
      }
      return true;
}

bool CADORecordset::GetFieldValue(int nIndex, COleDateTime& time)
{
      _variant_t vtFld;
      _variant_t vtIndex;
      
      vtIndex.vt = VT_I2;
      vtIndex.iVal = nIndex;
      
      vtFld = m_pRecordset->Fields->GetItem(vtIndex)->Value;
      switch(vtFld.vt)
      {
      case VT_DATE:
            {
                  COleDateTime dt(vtFld);
                  time = dt;
            }
            break;
      case VT_EMPTY:
      case VT_NULL:
            break;
      default:
            return false;
      }
      return true;
}

DWORD CADORecordset::GetRecordCount()
{
      DWORD nRows = 0;
      
      nRows = m_pRecordset->GetRecordCount();

      if(nRows == -1)
      {
            nRows = 0;
            if(m_pRecordset->EndOfFile != VARIANT_TRUE)
                  m_pRecordset->MoveFirst();
            
            while(m_pRecordset->EndOfFile != VARIANT_TRUE)
            {
                  nRows++;
                  m_pRecordset->MoveNext();
            }
            if(nRows > 0)
                  m_pRecordset->MoveFirst();
      }
      
      return nRows;
}

bool CADORecordset::IsOpen()
{
      if(m_pRecordset)
            return m_pRecordset->GetState() != adStateClosed;
      return false;
}

void CADORecordset::Close()
{
      if(IsOpen())
            m_pRecordset->Close();
            
}


bool CADODatabase::Execute(LPCTSTR lpstrExec)
{
      ASSERT(m_pConnection != NULL);
      ASSERT(strcmp(lpstrExec, _T("")) != 0);

      try
      {
            m_pConnection->Execute(_bstr_t(lpstrExec), NULL, adExecuteNoRecords);
      }
      catch(_com_error &e)
      {
            dump_com_error(e);
      }
      return true;      
}

bool CADORecordset::RecordBinding(CADORecordBinding &pAdoRecordBinding)
{
      IADORecordBinding *picRs = NULL;
      HRESULT hr;

      //Open the binding interface.
      if(FAILED(hr = m_pRecordset->QueryInterface(__uuidof(IADORecordBinding), (LPVOID*)&picRs )))
      {
            _com_issue_error(hr);
            return false;
      }
      
      //Bind the recordset to class
      if(FAILED( hr = picRs->BindToRecordset(&pAdoRecordBinding)))
      {
            _com_issue_error(hr);
            return false;
      }
      return true;
}

void CADORecordset::dump_com_error(_com_error &e)
{
      CString ErrorStr;
      
      
      _bstr_t bstrSource(e.Source());
      _bstr_t bstrDescription(e.Description());
      ErrorStr.Format( "CADORecordset Error\n\tCode = %08lx\n\tCode meaning = %s\n\tSource = %s\n\tDescription = %s\n",
            e.Error(), e.ErrorMessage(), (LPCSTR)bstrSource, (LPCSTR)bstrDescription );
      m_strLastError = _T("Query = " + GetQuery() + '\n' + ErrorStr);

      #ifdef _DEBUG
            AfxMessageBox( ErrorStr, MB_OK | MB_ICONERROR );
      #endif      
}

bool CADORecordset::GetFieldInfo(LPCTSTR lpFieldName, CAdoFieldInfo* fldInfo)
{
      _variant_t vtFld;
      
      strcpy(fldInfo->m_strName, (LPCTSTR)m_pRecordset->Fields->GetItem(lpFieldName)->GetName());
      fldInfo->m_lSize = m_pRecordset->Fields->GetItem(lpFieldName)->GetActualSize();
      fldInfo->m_lDefinedSize = m_pRecordset->Fields->GetItem(lpFieldName)->GetDefinedSize();
      fldInfo->m_nType = m_pRecordset->Fields->GetItem(lpFieldName)->GetType();
      fldInfo->m_lAttributes = m_pRecordset->Fields->GetItem(lpFieldName)->GetAttributes();
      return true;
}

bool CADORecordset::GetFieldInfo(int nIndex, CAdoFieldInfo* fldInfo)
{
      _variant_t vtFld;
      _variant_t vtIndex;
      
      vtIndex.vt = VT_I2;
      vtIndex.iVal = nIndex;
            
      strcpy(fldInfo->m_strName, (LPCTSTR)m_pRecordset->Fields->GetItem(vtIndex)->GetName());
      fldInfo->m_lSize = m_pRecordset->Fields->GetItem(vtIndex)->GetActualSize();
      fldInfo->m_lDefinedSize = m_pRecordset->Fields->GetItem(vtIndex)->GetDefinedSize();
      fldInfo->m_nType = m_pRecordset->Fields->GetItem(vtIndex)->GetType();
      fldInfo->m_lAttributes = m_pRecordset->Fields->GetItem(vtIndex)->GetAttributes();
      return true;
}


bool CADORecordset::GetChunk(LPCTSTR lpFieldName, CString& strValue)
{
      CString str = _T("");
      long lngSize, lngOffSet = 0;
      _variant_t varChunk;    
      int ChunkSize = 100;

      lngSize = m_pRecordset->Fields->GetItem(lpFieldName)->ActualSize;
      
      str.Empty();
      while(lngOffSet < lngSize)
      {
            varChunk = m_pRecordset->Fields->GetItem(lpFieldName)->GetChunk(ChunkSize);
            str += varChunk.bstrVal;
            lngOffSet += ChunkSize;
      }

      lngOffSet = 0;
      strValue = str;
      return TRUE;
}

CString CADORecordset::GetString(LPCTSTR lpCols, LPCTSTR lpRows, LPCTSTR lpNull, long numRows)
{
      _bstr_t varOutput;
      _bstr_t varNull("");
      _bstr_t varCols("\t");
      _bstr_t varRows("\r");

      if(strlen(lpCols) != 0)
            varCols = _bstr_t(lpCols);

      if(strlen(lpRows) != 0)
            varRows = _bstr_t(lpRows);
      
      if(numRows == 0)
            numRows =(long)GetRecordCount();                  
                  
      varOutput = m_pRecordset->GetString(adClipString, numRows, varCols, varRows, varNull);

      return (LPCTSTR)varOutput;
}

CString IntToStr(int nVal)
{
      CString strRet;
      char buff[10];
      
      itoa(nVal, buff, 10);
      strRet = buff;
      return strRet;
}

CString LongToStr(long lVal)
{
      CString strRet;
      char buff[20];
      
      ltoa(lVal, buff, 10);
      strRet = buff;
      return strRet;
}

int CADORecordset::GetNumFields()
{
      return m_pRecordset->Fields->Count;
}

BOOL CADORecordset::OpenSchema(_ConnectionPtr mpdb,int schema, CString strTable=_T(""))
{
      Close();
      switch ( schema)
      {
      case SCHEMA_TABLE:
            m_pRecordset = mpdb->OpenSchema(adSchemaTables);
            break;
      case SCHEMA_DATABASE:
            m_pRecordset = mpdb->OpenSchema(adSchemaCatalogs);
            break;
      case SCHEMA_PROCEDURE:
            m_pRecordset = mpdb->OpenSchema(adSchemaProcedures);
            break;
      case SCHEMA_FIELDS:
            {
            if(strTable.Find('[') != -1)
            {
                  int nLength = strTable.GetLength();
                  strTable = strTable.Mid(1, nLength-2);
            }
            
            _bstr_t bstrTableName((LPCTSTR)strTable);
            const int nSize = 3;
            _variant_t varCriteria[nSize];
            varCriteria[0].vt = VT_EMPTY;
            varCriteria[1].vt = VT_EMPTY;
            varCriteria[2].vt = VT_BSTR;
            varCriteria[2].bstrVal = bstrTableName;
            
            SAFEARRAYBOUND rgsabound[1];
            rgsabound[0].lLbound = 0;
            rgsabound[0].cElements = nSize;
            HRESULT hr = S_OK;
            SAFEARRAY* pSA = ::SafeArrayCreate(VT_VARIANT, 1, rgsabound);
            ASSERT(pSA != NULL);

            // Fill the safe array.
            for(long lIndex = 0; lIndex < nSize; lIndex++)
            {
                  hr  = ::SafeArrayPutElement(pSA, &lIndex, &varCriteria[lIndex]);
                  if(FAILED(hr))
                        _com_issue_error(hr);
            }
            
            // Initialize variant with safearray.
            VARIANT varData;
            varData.vt = VT_VARIANT | VT_ARRAY;
            V_ARRAY(&varData) = pSA;
            m_pRecordset = mpdb->OpenSchema(adSchemaColumns, varData);
            }
            break;
      }
      return m_pRecordset != NULL;      
}

CString CADORecordset::GetFieldValueByString(int i)
{
      int val = (int)NULL;
      _variant_t vtFld;
      _variant_t vtIndex;
      
      vtIndex.vt = VT_I2;
      vtIndex.iVal = i;
      
      
      vtFld = m_pRecordset->Fields->GetItem(vtIndex)->Value;
      return CHelpers::CrackStrVariant (vtFld);


}
0
 
ghimirenirajCommented:
Looks like you have posted twice

All Experts please comment here:

http://www.experts-exchange.com/mfc/Q_20314287.html

plonergan : Ask to delete this so you can get back your points:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.