Binding MFC Grid to database.

• Download source files - 3.47 Kb

Introduction

Last week I posted a new version of MFC Grid Control which worked with database; I've changed the source code,and sub class it from CGridCtrl, and also deleted some extra code. So this is the new version.

Creating this control is the same as MFC grid exept that when you want to define variable grid, you have to define it with CDbMFCGrid, not CGridCtrl. You have to import msado15.dll to your project too. In yout stdafx.h, you can write domething like this:

#import "E:\Program Files\Common Files\System\ado\msado15.dll" 
    no_namespace rename( "EOF", "adoEOF" )

Here is the rest of information about this grid:

I've tested this grid with most of SQLServer data types, so I think it will work with most of them except Image ,binary ,timestamp ,sql_variant, uniqueidentifier. The same thing was found for MS-Access database, but I don't think these caused a lot of trouble, because these are not very common types for displaying in grid. Maybe I'll add these features later to my grid. Also if somebody wants to do it, he/she can mail new version to me, and I'll update source code. After all it is not FULLY tested and maybe there are some bugs in it.

How to use?

Simple. Just after you create grid like the way it is said in MFC Grid article, give datasource and sqlstring to it, then use Bind() and FillGrid() functions like this:

//SQL datasource
//m_gridctrl.m_strDataSource =
//L"Provider=SQLOLEDB.1;Integrated Security=SSPI;
//Persist Security Info=False;
//Initial Catalog=Test;Data Source=mazy;
//Use Procedure for Prepare=1;Auto Translate=True;
//Packet Size=4096;Workstation ID=MAZY;Use Encryption for 
//Data=False;Tag with column collation when possible=True";
//access datasource



m_gridctrl.m_strDataSource = "Provider=Microsoft.Jet.OLEDB.4.0;"
    "Data Source=db1.mdb";

m_gridctrl.m_strQuery = "select * from Table1";

m_gridctrl.Bind();
m_gridctrl.FillGrid();

That's it. It reads datasource and fills up the grid. Then if the grid is not in readonly mode, if user edits a cell, it will automaticlly update database; also if you select row(s) and press DELETE key, it will delete records from both database and grid.

Features

• For adding records,you have to first add a new row to the grid. (You have to add it to last row), then simply call AddNewRecord() function for grid. Don't forget that this function always reads last row of grid and adds it to database.

  //add new row to grid then :
  m_gridctrl.Refresh();
  m_gridctrl.AddNewRecord();	

• It sorts rows when you click on fixed column based on cell types automatically.
• For all date/time types, I convert them to "%d/ %m/ %Y" format and put them in the grid, if you want other date/time format, you HAVE TO edit codes in two place - first find this line in CDbMFCGrid::FillGrid(void) function, CString str1 = date.Format(_T("%d/ %m/ %Y")); and change it to your desired format, then correct the codes in CALLBACK CDbMFCGrid::pfnCellDateCompare function. It is for comparing two date/time value and sorting date/time columns. Clear?
• You can filter grid easily too:

  m_gridctrl.pRecordset->Filter = "col1 ='Mazy'";
  
  m_gridctrl.FillGrid();

• If you use SetItemText() in your program and you want to update database, you have to call CDbMFCGrid::UpdateCell(int iRow,int iCol) after SetItemText()
• CDbMFCGrid::DeleteDataRow(int iRow) function is for deleting a row from grid AND database.