How to update two data tables back to db using OleDb and DataAdapter in c#

Question

I got two datatables from a mdb file using oledb and modified it.

accessConnection.Open();   

string selectQuery = "SELECT * FROM Students";
DataAdapter = new OleDbDataAdapter(selectQuery, accessConnection);
DataAdapter.Fill(StudentsDataTable);

DataAdapter.SelectCommand.CommandText = "SELECT * FROM Teachers";
DataAdapter.Fill(TeachersDataTable);

// modified the two datatables
// ...

However, I have a problem in updating the two tables back into the mdb file. I can't update StudentsDataTable because the DataAdapter select command was already changed to select TeachersDataTable. When I handle only one DataTable, I don't have this kind of problem.

DataAdapter.UpdateCommand = new OleDbCommandBuilder(DataAdapter).GetUpdateCommand();
DataAdapter.Update(StudentsDataTable); <-- exception error occur that columns don't match.
DataAdapter.Update(TeachersDataTable);

Do you have any ideas for updating two datatables using one DataAdapter? or Should I have separate DataAdapters for the two tables?

How you create StudentsDataTable? – Mihai Hantea Mar 03 '14 at 03:17 — Mihai Hantea, Mar 03 '14 at 03:17
What modification you have done in Student Table? – Shell Mar 03 '14 at 03:27 — Shell, Mar 03 '14 at 03:27

Riz · Answer 1 · 2014-03-03T03:32:55.320

Essentially you have to create two instances.

DataAdapter.UpdateCommand = new OleDbCommandBuilder(DataAdapter).GetUpdateCommand();
DataAdapter.Update(TeachersDataTable);

DataAdapter.SelectCommand.CommandText = "SELECT * FROM Students";
DataAdapter.UpdateCommand = new OleDbCommandBuilder(DataAdapter).GetUpdateCommand();
DataAdapter.Update(StudentsDataTable);

The main issue is your DataAdapter is set to Teachers table as it was the last command set on it.

How to update two data tables back to db using OleDb and DataAdapter in c#

1 Answers1