c# DataSet not updated back to the database

Question

I'm trying to work with an Access database through OleDb. I need to:

get data from the database to dataset
change the data in the dataset
update this dataset back to the database.

First two task are done. But I'm unable to update changed dataset back to the database. I do not see any error, no exception. Data in dataset are correctly changed (fruits.WriteXml writes correct result), but data inside the database are not changed.

Why are the data not changed in the database?

Thank you.

To reproduce it: (database file: https://drive.google.com/open?id=0ByImDaWMXaHAUGRIbTNLT0dHU0k&authuser=0)

private void button1_Click(object sender, EventArgs e)
{
        updateDb();
}

private void updateDb()
{
    String connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\local_workspaces\\visualstudio10\\FruitShop\\Database2.accdb;User Id=;Password=;";
    DataSet fruits = new DataSet();

    OleDbConnection connection = new OleDbConnection(connectionString);
    OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM fruits", connection);

    adapter.Fill(fruits);
    MessageBox.Show("Current value: " +fruits.Tables[0].Rows[0]["quantity"].ToString());

    //setting new value
    fruits.Tables[0].Rows[0]["quantity"] = 1111;
    fruits.AcceptChanges();

    OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(adapter);
    adapter.UpdateCommand = commandBuilder.GetUpdateCommand();
    adapter.Update(fruits);

    MessageBox.Show("New value: " + fruits.Tables[0].Rows[0]["quantity"].ToString());

    connection.Close();
}

edit:

the code is adapted from this example: http://msdn.microsoft.com/en-us/library/at8a576f(v=vs.110).aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-2 The original example doesn't work as well for me.

Can it be because of Access database? (I'm unable to try other databases right now)

solution:

Thank you for your help. To get it working, it is needed to get changes in the dataset and update only these changes otherwise it is not working (for me).

DataSet changes = fruits.GetChanges();
if (changes != null)
{
    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
    adapter.UpdateCommand = builder.GetUpdateCommand();

    adapter.Update(changes);
    fruits.AcceptChanges();
}

yes, even if I use connection.open(), the same behavior. Data are in the dataset but not updated in the database. — Václav Struhár, Oct 07 '14 at 12:13

score 0 · Answer 1 · edited May 23 '17 at 10:27

0

From the looks of it, you have not specified an update command (this will be an sql update statement) to use with your adapter. If you want to use the oledb method, you'll need to do something like: https://stackoverflow.com/questions/26235498/c-sharp-dataset-not-updated-back-to-the-database

Alternatively, you should be able to generate a data set that creates all of the necessary table adapters for you. This should help: http://msdn.microsoft.com/en-us/library/ms171919.aspx

edited May 23 '17 at 10:27

Community

1
1

answered Oct 07 '14 at 12:14

Cameron Woodall

94
1
6

thank you, I will check it. I was convinced that OleDbCommandBuilder will do it for me automatically. – Václav Struhár Oct 07 '14 at 12:18
@OverMind You'll need to supply the UpdateCommand with an sql statement telling it which table and values to update. See the first link I provided. It'll show you how. – Cameron Woodall Oct 07 '14 at 12:18
Have you seen [this](http://msdn.microsoft.com/en-us/library/at8a576f(v=vs.110).aspx) ? You may be 100% correct and I am just mistaken. (I have used the method you linked before, and not OP's way, though. I am just curious) – Mark C. Oct 07 '14 at 12:21

score 0 · Answer 2 · answered Sep 15 '15 at 20:39

Well there is ONE small silly mistake..

You must set...

fruits.AcceptChanges();

before...

fruits.Tables[0].Rows[0]["quantity"] = 1111;

Then the remaining code will be...

        fruits.AcceptChanges();
        fruits.Tables[0].Rows[0]["quantity"] =1111;

        MessageBox.Show(ds.Tables[0].Rows[0]["quantity"].ToString());
        DataSet changes = ds.GetChanges();
        if (changes != null)
        {
            OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
            adapter.UpdateCommand = builder.GetUpdateCommand();
            adapter.Update(changes);
            fruits.AcceptChanges();
            MessageBox.Show("New value: " + ds.Tables[0].Rows[0]["quantity"]);
        }
        connection.Close();

c# DataSet not updated back to the database

2 Answers2