Click here to Skip to main content
11,412,516 members (63,401 online)
Click here to Skip to main content

MFC DataGrid

, 24 Feb 2002
Rate this:
Please Sign up or sign in to vote.
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

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.

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.

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

Share

About the Author

Kirill Panov
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

 
GeneralMy vote of 5 Pin
maplewang at 7-Jul-12 6:03
membermaplewang7-Jul-12 6:03 
GeneralMy vote of 3 Pin
liujun6033260 at 7-Mar-11 19:26
memberliujun60332607-Mar-11 19:26 
GeneralError Pin
NKBajaj at 4-May-10 3:44
memberNKBajaj4-May-10 3:44 
Generalerror Pin
zengcan at 8-Mar-10 1:08
memberzengcan8-Mar-10 1:08 
GeneralHelp My please. Pin
volxvv at 11-Dec-07 3:03
membervolxvv11-Dec-07 3:03 
GeneralHelp My please. Pin
volxvv at 11-Dec-07 3:02
membervolxvv11-Dec-07 3:02 
GeneralHelp My please. Pin
volxvv at 11-Dec-07 3:01
membervolxvv11-Dec-07 3:01 
Generalgetting data from Datagrid ROW Column Pin
eryreyeryeryeryeyryye at 17-Apr-07 3:37
membereryreyeryeryeryeyryye17-Apr-07 3:37 
How can i get a a row value from data grid

for example I have DateTime and Teletext filds and Ihave add message handler SelChange to select a row by mouse,
How can I get date in the first column and 3rd row of the datagrid


Thanks in advance
Kazim


It is Kaz

GeneralVC++ 2005 Pin
Mike Eriksson at 14-Sep-06 0:14
memberMike Eriksson14-Sep-06 0:14 
GeneralErrors Pin
Dansveen at 20-Mar-06 7:34
memberDansveen20-Mar-06 7:34 
GeneralExecution error Pin
aghoffmann at 17-Jan-06 0:02
memberaghoffmann17-Jan-06 0:02 
Generalchanging the colour of the scrollbar Pin
Alex Evans at 24-Jul-04 14:43
memberAlex Evans24-Jul-04 14:43 
GeneralHOW:edit cell in place Pin
JabraJabra at 12-Jul-04 2:20
memberJabraJabra12-Jul-04 2:20 
Generaledit current cell Pin
JabraJabra at 12-Jul-04 2:15
memberJabraJabra12-Jul-04 2:15 
GeneralError Pin
JabraJabra at 29-Jun-04 13:38
memberJabraJabra29-Jun-04 13:38 
Generali fixed it! Pin
JabraJabra at 30-Jun-04 0:30
memberJabraJabra30-Jun-04 0:30 
Generalfix for MySQL (ODBC) Pin
zwiewel at 8-Jun-04 12:37
memberzwiewel8-Jun-04 12:37 
Generalredrawing bugs with MS SQL 2000 Pin
sekrett at 14-Apr-04 0:14
membersekrett14-Apr-04 0:14 
Generallinking error while trying to call from my app Pin
tuhin24 at 17-Mar-04 16:24
membertuhin2417-Mar-04 16:24 
GeneralErrors in compilation with VC++ 7 Pin
psalm69 at 12-Feb-04 5:03
memberpsalm6912-Feb-04 5:03 
GeneralRe: Errors in compilation with VC++ 7 Pin
Sir Zooro at 22-Mar-04 7:40
memberSir Zooro22-Mar-04 7:40 
GeneralUse ORACLE Database Pin
Mamun217 at 26-Dec-03 0:40
sussMamun21726-Dec-03 0:40 
GeneralUse ORACLE Database Pin
Mamun217 at 26-Dec-03 0:39
memberMamun21726-Dec-03 0:39 
Questionsupport print??? Pin
OzzyJMalmsteen at 22-Oct-03 21:29
memberOzzyJMalmsteen22-Oct-03 21:29 
GeneralThanks! Pin
bljacobs at 24-Jun-03 11:57
memberbljacobs24-Jun-03 11:57 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150427.1 | Last Updated 25 Feb 2002
Article Copyright 2002 by Kirill Panov
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid