Help with DataAdapter - DB doesn't update?

Question

I have a data adapter. When I check the function at runtime I see the changes, but nothing happens in the database. How do I debug this? What went wrong?

OleDbDataAdapter adapter = new OleDbDataAdapter();
string queryString = "SELECT * FROM tasks";
OleDbConnection connection = new OleDbConnection(cn.ConnectionString);
adapter.SelectCommand = new OleDbCommand(queryString, connection);
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);

connection.Open();

adapter.Fill(ds.Tables["Tasks"]);                      

adapter.Update(ds);

return ds;

Answer 1

Nothing is happening in the database because you're running a SELECT query. What are you expecting to happen?

Answer 2

Well unless you snipped a lot of code, you are not changing anything in the dataset per se.

What are you trying to achieve?

Here, you are selecting some data, filling the dataset with it, then putting back the unchanged dataset in the DB.

You should first change something in the dataset itself before calling adapter.Update(ds)

Cheers, Florian

Answer 3

As far as I can see you've not actually changed any of the contents of the tasks table. When you call adapter.Fill you are populated the empty dataset with the records from the database. You are then calling adapter.Update without changing any records within the dataset. The update command only performs changes when the dataset > datatable > datarows are edited and marked as dirty.

Answer 4

you are only specifying the select command. you also need to specify the insert command... i.e: DataAdapter.InsertCommand = new OleDbCommand....

Answer 5

You are selecting data via the SelectCommand. If you want to update data, you need to run the UpdateCommand.

Answer 6

I assume you didn't post the full source code, as it looks as though you're not modifying the dataset. However, I just tweaked your code a bit (see my comments).

Hopefully this will help...

string queryString = "SELECT * FROM tasks";
OleDbConnection connection = new OleDbConnection(cn.ConnectionString);
connection.Open(); // open connection first

SqlCommand cmd = new SqlCommand(queryString, connection);
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); // use the cmd above when instantiating the adapter
OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);

adapter.Fill(ds.Tables["Tasks"]);                      

// Modify your dataset here.
// Don't call AcceptChanges() as this will prevent the update from working.

adapter.Update(ds);

connection.Close(); // Close connection before ending the function

return ds;

This should allow OleDbCommandBuilder to do its thing by automatically scripting the database updates.