Solved

Connect MFC using ADO and Access

Posted on 2002-06-20
2
3,627 Views
Last Modified: 2013-11-25
How do you connect an Access 2000 database with MFC (Visual Studio 6.0) using ADO?
0
Comment
Question by:plonergan
2 Comments
 
LVL 3

Expert Comment

by:ghimireniraj
Comment Utility
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
 

Accepted Solution

by:
zhoujohnson earned 100 total points
Comment Utility
// 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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction: Displaying information on the statusbar.   Continuing from the third article about sudoku.   Open the project in visual studio. Status bar – let’s display the timestamp there.  We need to get the timestamp from the document s…
Introduction: Finishing the grid – keyboard support for arrow keys to manoeuvre, entering the numbers.  The PreTranslateMessage function is to be used to intercept and respond to keyboard events. Continuing from the fourth article about sudoku. …
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now