Click here to Skip to main content
15,867,594 members
Articles / Desktop Programming / MFC
Article

MFC DataGrid

Rate me:
Please Sign up or sign in to vote.
5.00/5 (17 votes)
24 Feb 20022 min read 254K   7.5K   88   36
CDataGrid which was derived from CGridCtrl and used ADO for access to database.

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

Image 1

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:

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.

Image 2

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:

Image 3

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.

Image 4

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
Russian Federation Russian Federation
He is a Visual C++ developer,an MCSD and an MCDBA

He has been programming in C/C++ for 6 years, Visual C++ with MFC for 4 years and RDBMS: Oracle, MS SQL for 3 years

Comments and Discussions

 
Generalnice work! And a question: Pin
gok13-Jan-03 10:04
professionalgok13-Jan-03 10:04 
GeneralDateTimePicker DLL Pin
isaac Zohar23-Oct-02 6:36
sussisaac Zohar23-Oct-02 6:36 
GeneralError Pin
Julien Lesur19-Jul-02 3:30
sussJulien Lesur19-Jul-02 3:30 
GeneralExecution time error Pin
sudhindra19-Jun-02 3:11
sudhindra19-Jun-02 3:11 
GeneralRe: Execution time error Pin
Kirill Panov19-Jun-02 17:32
Kirill Panov19-Jun-02 17:32 
GeneralRequery error Pin
aghoffmann8-Jan-06 22:52
aghoffmann8-Jan-06 22:52 
QuestionHow I can exchange a value of DateTimePicker control in dialog window between field of Recordset. Pin
7-Apr-02 13:17
suss7-Apr-02 13:17 
GeneralTo author Pin
Morozov Alexey25-Feb-02 13:13
Morozov Alexey25-Feb-02 13:13 
GeneralThanks! Pin
TeleStar25-Feb-02 5:09
TeleStar25-Feb-02 5:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.