Search code examples
c#oledbms-access-97

Adding New Row Data from C1FlexGrid to Access 97 .mdb Database C#

As a quick background, I'm a beginner at database programming so forgive me if anything that I post here doesn't make sense or is outright dumb.

I'm trying to make a WinForm application that will allow users to connect to a .mdb Access 97 database of their choosing, allow the user to make changes (e.g. add new data rows on WinForm which will then be applied to the original .mdb database), and sync those changes across different .mdb Access 97 databases if desired.

To give you an idea of what I have so far. I have successfully connected to a .mdb Access 97 database using C# and I output the database table contents into a DataSet object from which I then dumped into a C1FlexGrid (code will follow momentarily). Now before anyone mentions anything, I cannot upgrade the database file to a newer version, so it has to stay as an Access 97 version file.

Here is the code I used to connect:

dbConnection  = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path);
dbCommand     = new OleDbCommand("SELECT * from MAIN", dbConnection);
dbDataAdapter = new OleDbDataAdapter(dbCommand);

dbDataSet = new DataSet("MasterLanguageDB");
dbConnection.Open();
dbConnectionIsOpen = true;

// connection is successful, unlock connected mode features
EnterDatabaseConnectedMode();

dbDataAdapter.Fill(dbDataSet);
flexGrid.DataSource = dbDataSet.Tables[0];

My question is this: is the C1FlexGrid that I now have populated with the .mdb database file contents "binded" to the .mdb file? Because 1, I didn't bind the datasource the way Microsoft suggests doing it by way of the Add Data Source wizard in Visual Studio 2008 (because the user can connect to any .mdb database they choose -- not just one) and 2, I want whatever changes I make to the C1FlexGrid to apply to the original database.

If the answer is no, how do I create that "binding" or add the rows to the database?

Solution

  • The ADO.NET classes like Dataset or DataTable are disconnected objects.
    Meaning that they can't directly update the database.

    The OleDbDataAdapter has a method called Update that takes care to send all the modified data to the database. So, supposing you have a button somewhere to save your changes, then you need to call in the click event

      dbDataAdapter.Update(dbDataSet);

    However this requires that you keep the object instances at the class global level
    Another point is the need to prepare the dbDataAdapter.InsertCommand, dbDataAdapter.UpdateCommand and dbDataAdapter.DeleteCommand.

    These commands could be created simply using an instance of OleDbCommandBuilder just after you have set the SelectCommand 

      dbDataAdapter = new OleDbDataAdapter(dbCommand);
  OleDbCommandBuilder cb = new OleDbCommandBuilder(dbDataAdapter);