Click here to Skip to main content
Click here to Skip to main content

OLE DB - Bound controls

, 12 Nov 2001
Rate this:
Please Sign up or sign in to vote.
Shows how you can use data bound controls with OLE DB

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#

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Nish Sivakumar

United States United States
Nish is a real nice guy who has been writing code since 1990 when he first got his hands on an 8088 with 640 KB RAM. Originally from sunny Trivandrum in India, he has been living in various places over the past few years and often thinks it’s time he settled down somewhere.
 
Nish has been a Microsoft Visual C++ MVP since October, 2002 - awfully nice of Microsoft, he thinks. He maintains an MVP tips and tricks web site - www.voidnish.com where you can find a consolidated list of his articles, writings and ideas on VC++, MFC, .NET and C++/CLI. Oh, and you might want to check out his blog on C++/CLI, MFC, .NET and a lot of other stuff - blog.voidnish.com.
 
Nish loves reading Science Fiction, P G Wodehouse and Agatha Christie, and also fancies himself to be a decent writer of sorts. He has authored a romantic comedy Summer Love and Some more Cricket as well as a programming book – Extending MFC applications with the .NET Framework.
 
Nish's latest book C++/CLI in Action published by Manning Publications is now available for purchase. You can read more about the book on his blog.
 
Despite his wife's attempts to get him into cooking, his best effort so far has been a badly done omelette. Some day, he hopes to be a good cook, and to cook a tasty dinner for his wife.

Comments and Discussions

 
QuestionRuntime error on VC 7.0 PinmemberMike Pliam25-Apr-07 5:51 
Generalhelp Pinmemberzerocool6-Jan-03 2:13 
Question33? PinadminChris Maunder8-Jul-02 0:14 
AnswerRe: 33? PinsubeditorNishant S8-Jul-02 0:26 
GeneralArticle Pinmemberbjwoodburn2-Apr-02 0:31 
I found it an extremely good introduction to Data Access with C# and I am not from India.
 
Poke tongue | ;-P
 
"A beginner is an expert who hasn't yet learned."
B.Woodburn (2002, about lunchtime)
GeneralObsolete! PinmemberMN15-Oct-01 6:12 
GeneralRe: Obsolete! PinmemberFranz R.12-Nov-01 23:47 
GeneralRe: Obsolete! PinmemberDamon Chitsaz13-Nov-01 5:17 

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 | Mobile
Web03 | 2.8.140709.1 | Last Updated 13 Nov 2001
Article Copyright 2001 by Nish Sivakumar
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid