Click here to Skip to main content
11,642,658 members (68,816 online)
Click here to Skip to main content

OLE DB - Bound controls

, 12 Nov 2001 CPOL 186.4K 1.3K 26
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)

Share

About the Author

Nish Nishant
United States United States
Nish Nishant is a Software Architect/Consultant based out of Columbus, Ohio. He has over 15 years of software industry experience in various roles including Lead Software Architect, Principal Software Engineer, and Product Manager. Nish is a recipient of the annual Microsoft Visual C++ MVP Award since 2002 (13 consecutive awards as of 2014).

Nish is an industry acknowledged expert in the Microsoft technology stack. He authored
C++/CLI in Action for Manning Publications in 2005, and had previously co-authored
Extending MFC Applications with the .NET Framework for Addison Wesley in 2003. In addition, he has over 140 published technology articles on CodeProject.com and another 250+ blog articles on his
WordPress blog. Nish is vastly experienced in team management, mentoring teams, and directing all stages of software development.

Contact Nish : You can reach Nish on his google email id voidnish.

Website and Blog

You may also be interested in...

Comments and Discussions

 
QuestionRuntime error on VC 7.0 Pin
Mike Pliam25-Apr-07 5:51
memberMike Pliam25-Apr-07 5:51 
Generalhelp Pin
zerocool6-Jan-03 2:13
memberzerocool6-Jan-03 2:13 
Question33? Pin
Chris Maunder8-Jul-02 0:14
adminChris Maunder8-Jul-02 0:14 
AnswerRe: 33? Pin
Nishant S8-Jul-02 0:26
subeditorNishant S8-Jul-02 0:26 
GeneralArticle Pin
bjwoodburn2-Apr-02 0:31
memberbjwoodburn2-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! Pin
MN15-Oct-01 6:12
memberMN15-Oct-01 6:12 
GeneralRe: Obsolete! Pin
Franz R.12-Nov-01 23:47
memberFranz R.12-Nov-01 23:47 
GeneralRe: Obsolete! Pin
Damon Chitsaz13-Nov-01 5:17
memberDamon 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 | Terms of Use | Mobile
Web01 | 2.8.150731.1 | Last Updated 13 Nov 2001
Article Copyright 2001 by Nish Nishant
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid