OLE DB - Bound controls

• Download sample project - 4 Kb

Introduction

This article is intended as a sequel to my article OLE DB - First steps. We take a look at using the data grid as a bound control. We'll see how to use the OleDbDataAdapter and the DataSet classes to populate a data grid from an MS Access database. We'll also see how we can update records.

Things to do first...

• First create a new MS Access database called test.mdb and create a single table and call it 'main'.
• Now add two fields to 'main' called 'Name' of type 'Text' and 'Age' of type 'Number'.
• Populate the table with some values
• Copy test.mdb to d:\

Populating the grid from the DB

...

DataGrid* dg;
OleDbDataAdapter* da;
OleDbConnection* odc;
DataSet* ds;

...

//Create an OleDbConnection object and point 
//it to our MS Access database
odc = new OleDbConnection(
    "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\test.mdb");

//Create an OleDbDataAdapter object passing our
//SQL command and the OleDbConnection object
da = new OleDbDataAdapter("select * from main",odc); 

//Create a new DataSet object
ds = new DataSet();

//Fill up the DataSet object using the Fill method
//of the OleDbDataAdapter class
da->Fill(ds,"main");

//Set the DataSource property of our DataGrid
//to the corresponding table in our DataSet
dg->DataSource = ds->Tables->get_Item("main");

...

The OleDbDataAdapter class is a channel through which a DataSet object reads and writes data from the actual database. The DataSet object is an in-memory database cache. When we call the Fill method on our OleDbDataAdapter object, passing it the DataSet object, the DataSet object gets filled up with the data from the database. Populating the data grid is now quite easy. It has a DataSource property which we point to the table we want from our DataSet object.

Updating records through the datagrid

DataSet* dschanged = ds->GetChanges(DataRowState::Modified);
if(dschanged)
    da->Update(dschanged,"main"); 
else
    MessageBox::Show("Nothing to update");

We call GetChanges on our DataSet object and this returns  a copy of the DataSet containing all changes made to it since it was last loaded. We use an overload of the method that allows us to specify a filter on the DataSet returned. I have used the DataRowState::Modified filter which will return a DataSet object with all the rows in the data grid that were modified. Among other options, commonly used one include DataRowState::Added and DataRowState::Deleted. Now we call Update on the OleDbDataAdapter object which will call the required INSERT, UPDATE or DELETE queries to update the database.

For the Update method to work correctly we need to set the UpdateCommand property of the OleDbDataAdapter object. We create a new OleDbCommand object which represents the SQL command to execute on our data source. As you can see we can use pseudo variables like @Age and @Name. Of course, we need to add these variables to the Parameters property which is a collection of OleDbParameterCollection objects.

da->UpdateCommand = new OleDbCommand(
    "update main set age = @Age where name = @Name",odc); 

OleDbParameter* ageparam = da->UpdateCommand->Parameters->Add(
    "@Age",OleDbType::Integer);
    ageparam->SourceColumn = "Age";

da->UpdateCommand->Parameters->Add(
    "@Name",OleDbType::VarChar,50,"Name");

Similarly you'll need to setup the DeleteCommand and the InsertCommand properties before you can call Update for queries that require DELETE and INSERT SQL queries. Be careful when you convert your database field types to the corresponding OleDbType enumeration. I had some trouble with the Number-Long Integer field type used in MS Access. As you can see I've finally split up the call into two, first calling another overload of the Add method and then manually setting the SourceColumn property.

Revision History

• Jul 08 2002 - Did a full redo of the article, added a sample project and now uses MC++ instead of C#