Update using MySqlDataAdapter doesn't work

Question

I am trying to use MySqlDatAdapter to update a MySql table. But, the table never updates!!! I did this before but with SQL server. Is there anything else that is specific to MySql that I am missing in my code?

        DataTable myTable = new DataTable("testtable");

        MySqlConnection mySqlCon = new MySqlConnection(ConfigurationManager.ConnectionStrings["DBConStr"].ConnectionString);

        MySqlCommand mySqlCmd = new MySqlCommand("SELECT * FROM testtable WHERE Name = 'Tom'");
        mySqlCmd.Connection = mySqlCon;

        MySqlDataAdapter adapter = new MySqlDataAdapter(mySqlCmd);
        MySqlCommandBuilder myCB = new MySqlCommandBuilder(adapter);
        adapter.UpdateCommand = myCB.GetUpdateCommand();

        mySqlCon.Open();

        adapter.Fill(myTable);
        myTable.Rows[0]["Name"] = "Was Tom";
        myTable.AcceptChanges();
        adapter.Update(myTable);
        mySqlCon.Close();

Thanks

Kilt

Answer 1

Remove myTable.AcceptChanges() before the update. Othwerwise that will set all rows RowState to Unchanged, hence the DataAdapter will not know that something was changed.

adapter.Update(myTable) will call AcceptChanges itself after the update is finished.

So...

myTable.Rows[0]["Name"] = "Was Tom";
//myTable.AcceptChanges();
adapter.Update(myTable);

Stickinthemud

@usp: That's not stupid, i assume that many people misunderstand the meaning of AcceptChanges since it's name suggests something desirable befor an update ;) – Stickinthemud

you are right if I didn't know it. But I was just not paying attention to that line as I copied the code from other project that is using a DataTable with GridView. Anyways, thanks for the help. – Kilt

Answer 2

My some one need to look into the following solution; In other scenario people may need different solution. Even Don't do any manipulation with Datatable when you Debug at Run-time like this,

myTable.GetChanges(); // Return Any of Chnages Made without applying myTable.Accepchanges()
myTable.GetChanges(DataRowState.Added); // Return added rows without applying myTable.Accepchanges()
myTable.GetChanges(DataRowState.Deleted); 
myTable.GetChanges(DataRowState.Detached);
myTable.GetChanges(DataRowState.Modified);
myTable.GetChanges(DataRowState.Unchanged);

You may get Data According to the above commands. So better try to debug before you pass the datatable to update or insert or delete command.

If myTable.GetChanges() return null then you can SetAdded() or SetModified() back to your DataTable;

foreach(DataRow row in myTable.Rows)
{
  row.SetAdded(); // For Insert Command
  row.SetModified(); // For Update Command
}

Alanna