Click here to Skip to main content
15,867,141 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 253.9K   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

 
Questionvs2010/MFC Pin
Member 1234730523-Feb-16 16:23
Member 1234730523-Feb-16 16:23 
GeneralMy vote of 5 Pin
maplewang7-Jul-12 5:03
maplewang7-Jul-12 5:03 
GeneralMy vote of 3 Pin
liujun60332607-Mar-11 18:26
liujun60332607-Mar-11 18:26 
GeneralError Pin
NKBajaj4-May-10 2:44
NKBajaj4-May-10 2:44 
Generalerror Pin
zengcan8-Mar-10 0:08
zengcan8-Mar-10 0:08 
GeneralHelp My please. Pin
volxvv11-Dec-07 2:03
volxvv11-Dec-07 2:03 
GeneralHelp My please. Pin
volxvv11-Dec-07 2:02
volxvv11-Dec-07 2:02 
GeneralHelp My please. Pin
volxvv11-Dec-07 2:01
volxvv11-Dec-07 2:01 
Generalgetting data from Datagrid ROW Column Pin
kazim bhai17-Apr-07 2:37
kazim bhai17-Apr-07 2:37 
GeneralVC++ 2005 Pin
Mike Eriksson13-Sep-06 23:14
Mike Eriksson13-Sep-06 23:14 
GeneralErrors Pin
Dansveen20-Mar-06 6:34
Dansveen20-Mar-06 6:34 
GeneralExecution error Pin
aghoffmann16-Jan-06 23:02
aghoffmann16-Jan-06 23:02 
Generalchanging the colour of the scrollbar Pin
Alex Evans24-Jul-04 13:43
Alex Evans24-Jul-04 13:43 
GeneralHOW:edit cell in place Pin
JabraJabra12-Jul-04 1:20
JabraJabra12-Jul-04 1:20 
Generaledit current cell Pin
JabraJabra12-Jul-04 1:15
JabraJabra12-Jul-04 1:15 
GeneralError Pin
JabraJabra29-Jun-04 12:38
JabraJabra29-Jun-04 12:38 
Generali fixed it! Pin
JabraJabra29-Jun-04 23:30
JabraJabra29-Jun-04 23:30 
Generalfix for MySQL (ODBC) Pin
zwiewel8-Jun-04 11:37
zwiewel8-Jun-04 11:37 
Generalredrawing bugs with MS SQL 2000 Pin
sekrett13-Apr-04 23:14
sekrett13-Apr-04 23:14 
Generallinking error while trying to call from my app Pin
tuhin2417-Mar-04 15:24
tuhin2417-Mar-04 15:24 
GeneralErrors in compilation with VC++ 7 Pin
psalm6912-Feb-04 4:03
psalm6912-Feb-04 4:03 
GeneralRe: Errors in compilation with VC++ 7 Pin
Daniel Fruzynski22-Mar-04 6:40
Daniel Fruzynski22-Mar-04 6:40 
GeneralUse ORACLE Database Pin
Mamun21725-Dec-03 23:40
Mamun21725-Dec-03 23:40 
GeneralUse ORACLE Database Pin
Mamun21725-Dec-03 23:39
Mamun21725-Dec-03 23:39 
Questionsupport print??? Pin
OzzyJMalmsteen22-Oct-03 20:29
OzzyJMalmsteen22-Oct-03 20:29 

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.