C# DataTable update Access Database

Question

How can I save a DataTable to a file. accdb (Access) existing one? I've used the following code and it does not work:

using (OleDbConnection oledbConnection = new OleDbConnection(connection))
{
   oledbConnection.Open();
   string query = "SELECT * FROM Student";
   using (OleDbCommand oledbCommand = new OleDbCommand(query, oledbConnection))
   {
      using (OleDbDataAdapter oledbDataAdapter = new OleDbDataAdapter(oledbCommand))
      {
         using (OleDbCommandBuilder oledbCommandBuilder = new OleDbCommandBuilder(oledbDataAdapter))
         {
            oledbDataAdapter.DeleteCommand = oledbCommandBuilder.GetDeleteCommand(true);
            oledbDataAdapter.InsertCommand = oledbCommandBuilder.GetInsertCommand(true);
            oledbDataAdapter.UpdateCommand = oledbCommandBuilder.GetUpdateCommand(true);
            oledbDataAdapter.Update(dataTable);
         }
      }
   }
   oledbConnection.Close();
}

The variable dataTable is initialized with the original contents of the file, then it was modified by adding a row and now I have to update the table in the database.

I tried using the following code, but that does not work :(

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM Student", connection);
OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(da);
da.InsertCommand = cmdBuilder.GetInsertCommand(true);
// create and insert row in the DataTable
da.Update(dataTable);

If you issue dataTable.GetChanges() in the Watch Window, do you get any changes? — Mike Perrenoud
– Mike Perrenoud, Commented Jan 8, 2014 at 14:51
This method was useful for another function, but not for this. Thanks — user3105160
– user3105160, Commented Jan 8, 2014 at 14:57

Steve · Accepted Answer · 2014-01-08 14:57:17Z

5

Assuming that you have made some changes in the datatable, then you could pass the generated update/insert/delete command to the adapter in this way

oledbDataAdapter.DeleteCommand = oledbCommandBuilder.GetDeleteCommand();
oledbDataAdapter.InsertCommand = oledbCommandBuilder.GetInsertCommand();
oledbDataAdapter.UpdateCommand = oledbCommandBuilder.GetUpdateCommand();
oledbDataAdapter.Update(datatable);

Now the adapter knows how to update your table

answered Jan 8, 2014 at 14:57

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mike Perrenoud Over a year ago

Good catch Steve, I missed the fact that the OP wasn't assigning the commands when I read the GetDeleteCommand() lines!

user3105160 Over a year ago

yes! "Can not add the amount of data you want. field too small. inserting or pasting less data."

Steve Over a year ago

Well, that's another problem. It is generated by the value inserted in one of your table's column. The size of this (probably text column) is not enough to contain the data typed in your input boxes by your user (need to trim it down or set a maxlength)

Steve Over a year ago

I suggest to post a new question, It will get more attention than a comment in this answer.

Collectives™ on Stack Overflow

C# DataTable update Access Database

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related