MFC DataGrid

Kirill Panov

5.00/5 (15 votes)

Feb 25, 2002

2 min read

260388

7585

CDataGrid which was derived from CGridCtrl and used ADO for access to database.

Download demo project - 259 Kb

CDataGrid

Some time ago I started to work with database using Visual C++ and I was undeceived in MS DataGrid. I wrote my class CDataGrid which was derived from CGridCtrl by Chris Maunder and used ADO for access to database.

The sample application

Usage - Step by Step

Step 1- Using CDataGid

Add to StdAfx.h strings.

 #import "c:\program files\common files\system\ado\msado15.dll" \
  no_namespace \
  rename ("EOF", "adoEOF")
 #include <afxtempl.h<afxtempl.h>><afxtempl.h>

In this sample, I used Northwind database from MS SQL 7, 2000 and Access. You can find nwind.mdb on Visual Studio 6.0 disk #1.

Next, you should add DataGrid.h and DataGrid.cpp to the project. You will also need to add CGridCtrl files to the project. You can download the latest version of CGridCtrl here. (This sample used CGridCtrl v 2.23)

Add ExString.h and ExString.cpp to the project.

Add DDXFields.h and DDXFields.cpp to the project.

In this sample, I have used these classes:

CButtonST by Davide Calabro. It is a very useful bitmap button class.
CResizableDialog by Paolo Messina. Resizable dialog class is very helpful for developing dialog-based applications.

I developed CDataComboBox class to access a database from combo box.

If you are going to use CDataComboBox in your project, you should add DataComboBox.h and DataComboBox.cpp to the project.

Add CDataGrid variable and connection into your dialog header file:

#include "DataGrid.h"

class CDataGrid_DemoDlg : public CResizableDialog
{
// Construction
public:

  CDataGrid m_Grid;
  _ConnectionPtr  m_pConnection;

Associate the grid window with C++ object in the DoDataExchange of your dialog:

DDX_Control(pDX, IDC_GRID, m_Grid);

Into OnInitDialog add:

  m_pConnection.CreateInstance(__uuidof(Connection)); //Create connection

  try
  {
    //Open connection
    //I prefer to use udl
    //m_pConnection->Open
    //  (L"File Name=C:\\Program Files\\Common Files\\"
    //  "System\\Ole DB\\Data Links\\nwind.udl", L"", L"", -1);
    m_pConnection->Open
      (L"Provider=Microsoft.Jet.OLEDB.4.0;"
      "Data Source=D:\\DATA\\Nwind.mdb", L"", L"", -1);     ......
    ......
  }
  catch(_com_error *e)
  {
    CString Error = e->ErrorMessage();
    AfxMessageBox(e->ErrorMessage());
  }
  catch(...)
  {

  }

  SetFields();

Set fields of the grid and execute the query in the SetFields().

void CDataGrid_DemoDlg::SetFields()
{
  int n;

  m_Grid.m_field.SetSize(2);
    
  n=0;
  m_Grid.m_field[n].Field=_T("ProductName");
  m_Grid.m_field[n].Caption=_T("Product");
  //m_Grid.m_field[n].With=75;
  m_Grid.m_field[n].Find=true;
  
  n=1;
  m_Grid.m_field[n].Field=_T("QuantityPerUnit");
  m_Grid.m_field[n].Caption=_T("QuantityPerUnit");
  //m_Grid.m_field[n].With=300;
  m_Grid.m_field[n].Find=true;
  
  ......
  ......

  m_Grid.Execute(m_pConnection,"ProductId","*","Products","",1);
}

Because we use the virtual mode of the CGridCtrl, we must override OnNotify function.

BOOL CDataGrid_DemoDlg::OnNotify(WPARAM wParam, 
                         LPARAM lParam, LRESULT* pResult)
{
  // TODO: Add your specialized code here and/or call the base class
  if (wParam == (WPARAM)m_Grid.GetDlgCtrlID())
  {
    *pResult = 1;
    GV_DISPINFO *pDispInfo = (GV_DISPINFO*)lParam;
    m_Grid.SetValue(pDispInfo);
  }

  return CResizableDialog::OnNotify(wParam, lParam, pResult);
}

The screenshot below shows us the DataGrid's work.

Step 2- Using CDataComboBox

Add DataCombo control into dialog, choose Type Drop List in the style sheet.

Add CDataGrid variable into your dialog header file:

#include "DataComboBox.h"

class CDataGrid_DemoDlg : public CResizableDialog
{
// Construction
public:

  CDataComboBox m_cmbDep;
  CDataComboBox m_cmbBill;

Associate the combo boxes with C++ objects in the DoDataExchange of your dialog:

  DDX_Control(pDX, IDC_CMB_CAT, m_cmbCat);
  DDX_Control(pDX, IDC_CMB_SUP, m_cmbSup);

Fill DataComboBox: There are two modes of the DataComboBox: BOUND and UNBOUND.

In the 3rd step, I'll show you how to use UNBOUND mode. But now fill DataComboBoxes using BOUND mode in the OnInitDialog.

  m_pConnection.CreateInstance(__uuidof(Connection)); //Create connection

  try
  {
    //Open connection
    //I prefer to use udl
    //m_pConnection->Open
    //  (L"File Name=C:\\Program Files\\Common Files"
    //  "\\System\\Ole DB\\Data Links\\nwind.udl", L"", L"", -1);
    m_pConnection->Open
      (L"Provider=Microsoft.Jet.OLEDB.4.0;"
      "Data Source=D:\\DATA\\Nwind.mdb", L"", L"", -1);     ......

    m_cmbCat.Execute(m_pConnection,_
      T("SELECT * FROM Categories ORDER BY CategoryName"),
      _T("CategoryName"));
    m_cmbSup.Execute(m_pConnection,
      _T("SELECT * FROM Suppliers ORDER BY CompanyName"),
      _T("CompanyName"));

    ......
    ......
  }
  catch(_com_error *e)
  {
    CString Error = e->ErrorMessage();
    AfxMessageBox(e->ErrorMessage());
  }
  catch(...)
  {

  }

  SetFields();

Create Master-Detail's relation:

Override message CBN_SELCHANGE of the ComboBoxes4 and create function Requery(). Remove m_Grid.Execute from SetFields() to the Requery().

void CDataGrid_DemoDlg::Requery()
{
  CString strCat,strSup,strWhere;

  if (!m_cmbCat.IsAddPosition())
  {
    m_strCatId=m_cmbCat.m_pSet->GetCollect(L"CategoryID");
    strCat=_T(" CategoryID=")+m_strCatId;
  }

  if (!m_cmbSup.IsAddPosition())
  {
    m_strSupId=m_cmbSup.m_pSet->GetCollect(L"SupplierID");
    strSup=_T(" SupplierID=")+m_strSupId;
  }
  
  strWhere=strCat;

  if( ( strSup.GetLength()*strWhere.GetLength() ) ==0 )
    strWhere+=strSup;
  else
    strWhere+=" AND "+strSup;
  
  m_Grid.Execute(m_pConnection,"ProductId"/*Primary key field */ 
    ,"*"//fields in SELECT statment
    ,"Products" //from 
    ,strWhere   // where 
    ,0);        // order by N of the Grid column      
}

The screenshot below shows us how the relationship works.

Step 3- Changing data in the DataGrid

Create the dialog, place edit boxes and combo boxes into the dialog template. Add essential classes and variables into header file.

#include "DataComboBox.h"
#include "DDXFields.h"

/////////////////////////////////////////////////////////////////////////////
// CDialEdit dialog

class CDialEdit : public CDialog
{
// Construction
public:
  bool m_catChange,m_supChange;
  _RecordsetPtr m_pSet;
  CDDXFields m_DDXFields;
  enum {EDIT, COPY, NEW};
  int m_operation;

  CDataComboBox m_cmbCat;
  CDataComboBox m_cmbSup;
  . . . 
  . . .

Associate controls into DoDataExchange:

  DDX_Control(pDX, IDC_CMB_CAT, m_cmbCat);
  DDX_Control(pDX, IDC_CMB_SUP, m_cmbSup);
  
  m_DDXFields.DDX(pDX);

Add into the constructor:

CDialEdit::CDialEdit(CWnd* pParent /*=NULL*/)
  : CDialog(CDialEdit::IDD, pParent)
{
  //{{AFX_DATA_INIT(CDialEdit)
    // NOTE: the ClassWizard will add member initialization here
  //}}AFX_DATA_INIT
  m_operation=EDIT;
  m_catChange=m_supChange=false;
  m_DDXFields.SetSize(5);
}

Into OnInitDialog add:

BOOL CDialEdit::OnInitDialog()
{
  CDialog::OnInitDialog();


  // TODO: Add extra initialization here


  int i;

  m_DDXFields.m_pWnd=this;

  i=0;
  m_DDXFields.ElementAt(i).Set(_T( "ProductName" ),IDC_ED_PROD) ;
  m_DDXFields.ElementAt(i).m_description=_T("Product");

  i=1;
  m_DDXFields.ElementAt(i).Set(_T( "QuantityPerUnit" ), 
                       IDC_ED_QTY, _T( "1" ),false,true) ;
  m_DDXFields.ElementAt(i).m_description=_T("Quantity Per Unit");

  i=2;
  m_DDXFields.ElementAt(i).Set(_T( "UnitPrice" ), 
                       IDC_ED_PRICE, _T( "0" ),false,true) ;
  m_DDXFields.ElementAt(i).m_description=_T("Price") ;

  i=3;
  m_DDXFields.ElementAt(i).Set(_T( "UnitsInStock" ),
                       IDC_ED_UNITS, _T( "0" ),false,true) ;
  m_DDXFields.ElementAt(i).m_description=_T("Units In Stock");

  i=4;
  m_DDXFields.ElementAt(i).Set(_T( "Discontinued" ),
                       IDC_ED_DISCONT,_T( "0" ),false,true) ;

  switch (m_operation)
  {
    case NEW:
      break;
    default:
      m_DDXFields.ReadData(m_pSet);
    break;
  }
  UpdateData(FALSE);

  m_cmbSup.Fill();
  m_cmbCat.Fill();
 .  . .
 .  . .

Create "Save" button in the dialog and function.

void CDialEdit::OnBtnSave()
{
  // TODO: Add your control notification handler code here
  CExString strField;
  UpdateData();
  try
  {
    switch (m_operation)
    {
      case EDIT:
        break;
      default:
        m_pSet->AddNew();

        strField=_T("CategoryID");
        m_pSet->Fields->GetItem
           (strField.Variant())->Value = m_catId.Variant();

        strField=_T("SupplierID");
        m_pSet->Fields->GetItem
          (strField.Variant())->Value = m_supId.Variant();

        break;
    }
    try
    {
      if( m_DDXFields.WriteData(m_pSet)==-1)
        return;
    }
    catch(...)
    {
      ;
    }
    CExString strCatId,strSupId;
    strCatId=m_cmbCat.GetCurId();
    strSupId=m_cmbSup.GetCurId();;

    if(strCatId!=m_catId)
    {

      m_catChange=true;
      strField=_T("CategoryID");
      m_pSet->Fields->GetItem
        (strField.Variant())->Value=strCatId.Variant();
    }

    if(strSupId!=m_supId)
    {
      m_supChange=true;
      strField=_T("SupplierID");
      m_pSet->Fields->GetItem
        (strField.Variant())->Value=strSupId.Variant();
    }
    m_pSet->Update();

    CDialog::OnOK();


  }//try
  catch( _com_error &e )
  {

    CString mes1,mes2,mes3,mes4;

    mes1.Format(_T("Error:%08lx.\n"), e.Error());
    mes2.Format(_T("ErrorMessage:%s.\n"), e.ErrorMessage());
    mes3.Format(_T("Source:%s.\n"), 
        (LPCTSTR) _bstr_t(e.Source()));
    mes4.Format(_T("Description:%s.\n"), 
        (LPCTSTR) _bstr_t(e.Description()));
    MessageBox(mes1+mes2+mes3+mes4,
        _T("Invalid field "),MB_ICONERROR);

    return;
  }
  catch(...)
  {

    MessageBox(_T("Unhandled Exception"),
       _T("Invalid field ")+strField,MB_ICONERROR);
    return;
  }

}

Insert into definition of the main dialog class, CArrayStringBox variables, because we are using m_cmbSup and m_cmbCat in UNBOUND mode.

class CDataGrid_DemoDlg : public CResizableDialog
{
// Construction
public:
  CArrayStringBox m_SupArray;
  CArrayStringBox m_CatArray;

Fill it in the OnInitDialog of the main dialog.

  try
  {
    //Open connection
    //I prefer to use udl
    //m_pConnection->Open
    //  (L"File Name=C:\\Program Files\\"
    //   "Common Files\\System\\Ole DB\\Data Links\\nwind.udl", 
    //   L"", L"", -1);
    m_pConnection->Open(L"Provider=Microsoft.Jet.OLEDB.4.0;"
       "Data Source=D:\\DATA\\Nwind.mdb", L"", L"", -1);

    . . .
    . . .
    m_CatArray.Fill(m_pConnection,
      _T("SELECT * FROM Categories ORDER BY CategoryName"),
      _T("CategoryName"),_T("CategoryID"));
    m_SupArray.Fill(m_pConnection,
      _T("SELECT * FROM Suppliers ORDER BY CompanyName"),
      _T("CompanyName"),_T("SupplierID"));

  }
  catch(_com_error *e)
  {
    CString Error = e->ErrorMessage();
    AfxMessageBox(e->ErrorMessage());
  }
  catch(...)
  {

  }

Create the "Edit", "Add", "Copy" and "Delete" buttons in the main dialog and functions:

#include "DialEdit.h"

void CDataGrid_DemoDlg::OnBtnEdit()
{
  // TODO: Add your control notification handler code here
  int nRow=m_Grid.IsSelectRow();
  if(nRow==-1)
    return;

  CDialEdit dlg;

  dlg.m_pSet=m_Grid.m_pSet;

  dlg.m_catId=m_Grid.m_pSet->GetCollect(L"CategoryID") ;
  dlg.m_supId=m_Grid.m_pSet->GetCollect(L"SupplierID") ;

  dlg.m_cmbCat.Attach(&m_CatArray,dlg.m_catId);
  dlg.m_cmbSup.Attach(&m_SupArray,dlg.m_supId);

  dlg.m_operation=CDialEdit::EDIT;

  if (dlg.DoModal() == IDOK)
  {
    if( ((dlg.m_catChange)&&(!m_cmbCat.IsAddPosition()))
        || ( (dlg.m_supChange)&&(!m_cmbSup.IsAddPosition()) ) )
    {
      if(nRow==1)
      {
        if(1!=m_Grid.GetRowCount())
          m_Grid.SetRowFocus(nRow);
        else
          m_Grid.SetRowFocus(0);
      }
      else
      {
        m_Grid.SetRowFocus(nRow-1);
      }
      m_Grid.RequerySource();
    }
    else
      m_Grid.Invalidate();
  }
}

void CDataGrid_DemoDlg::OnBtnAd()
{
  // TODO: Add your control notification handler code here
  CDialEdit dlg;
  dlg.m_pSet=m_Grid.m_pSet;

  dlg.m_catId=m_Grid.m_pSet->GetCollect(L"CategoryID") ;
  dlg.m_supId=m_Grid.m_pSet->GetCollect(L"SupplierID") ;

  dlg.m_cmbCat.Attach(&m_CatArray,dlg.m_catId);
  dlg.m_cmbSup.Attach(&m_SupArray,dlg.m_supId);

  dlg.m_operation=CDialEdit::NEW;

  if (dlg.DoModal() == IDOK)
  {
    m_Grid.AddNew();
  }
}

void CDataGrid_DemoDlg::OnBtnCopy()
{
  // TODO: Add your control notification handler code here
  if(m_Grid.IsSelectRow()==-1)
    return;

  CDialEdit dlg;
  dlg.m_pSet=m_Grid.m_pSet;

  dlg.m_operation=CDialEdit::COPY;

  dlg.m_catId=m_Grid.m_pSet->GetCollect(L"CategoryID") ;
  dlg.m_supId=m_Grid.m_pSet->GetCollect(L"SupplierID") ;

  dlg.m_cmbCat.Attach(&m_CatArray,dlg.m_catId);
  dlg.m_cmbSup.Attach(&m_SupArray,dlg.m_supId);

  if (dlg.DoModal() == IDOK)
  {
    m_Grid.AddNew();
  }
}

void CDataGrid_DemoDlg::OnBtnDel()
{
  // TODO: Add your control notification handler code here
  m_Grid.Delete();
}

Add menu, find dialog and your application is ready.