Update requires a valid UpdateCommand when passed DataRow collection with modified rows

Question

So I had this working last week. At least, I thought I did! DataGridView Update

Then I start working on the project again today and am getting

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

On

scDB.SSIS_Configurations_StagingDataTable table = (scDB.SSIS_Configurations_StagingDataTable)stagingGrid.DataSource;
myStagingTableAdapter.Update(table);

The StagingTableAdapter has an additional query which takes 'filter' as a parameter. That was used to fill the DataGridView. In the wizard for creating that query I see 'update was generated'. I see that most posts with this error require that an update statement be generated with a command builder. What do I do?

Answer 1

This message will also be displayed caused when you do not have a primary key defined on the table you are updating.

Answer 2

I ran into the same problem as Sam. I had working code that just suddenly was no longer working. I didn't know when I wrote it, but it must have been automatically inferring the update command, and then stopped doing it. Perhaps a service pack from MS in between versions that we never noticed. Anyway, the solution I came across is using a (in my case for oracle) a OracleCommandBuilder which takes the DataAdapter (after calling fill) as a parameter to the constructor and then calling GetUpdateCommand() and assigning that to the UpdateCommand on the DataAdapter.

pseudocode:

DataAdapter da = new DataAdapter(...)
...
da.Fill();
da.UpdateCommand = new OracleCommandBuilder(da).GetUpdateCommand();
...
da.Update();

Answer 3

The error is quite literal: The Adapter needs a valid SQL Update statement. Dataset designers and CommandBuilders will generate these for you, but there is nothing wrong with hand-crafting a bit of SQL either.

Anyway, you'll have to verify (debugger) that the Update statement is still configured and what it actually is. It could be more of a SQL than a C# problem.

Edit: the Command Builder tools will only handle straight, single table, Select statements. Use a Join or anything fancy and you're on your own.

Answer 4

Dan's workaround works for me, but instead of OracleCommandBuilder I can just use SqlCommandBuilder:

DataAdapter da = new DataAdapter(...)
...
da.Fill();
da.UpdateCommand = new SqlCommandBuilder(da).GetUpdateCommand();
...
da.Update();

Answer 5

As others have said you need to make sure you have a primary key set on the table for the auto-generated update code to be created. I did this but was still seeing the same message of

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

I then discovered that you then need to refresh the table adapter as well (makes sense when you think about it!).

While you are doing this it may also be worth double checking that you had the Generate Insert, Update and Delete statements option selected (like the below).

Then just click through the wizard, the final screen should confirm that it will create the redo the code required (now with the primary key):

Answer 6

I just experienced the same problem, and Jason's answer did work for me: I forgot to assign a primary key for my table.

Just One more reminder to add: After correcting the problem in the table, when going back to the Visual Studio, in the .xsd file that contains the dataset, make sure to remove the original table and add it again from the Server Explorer or Database Explorer. Otherwise the error will still exist.

Answer 7

You should always have a primary key defined column field. Otherwise the problem will always exist. SqlCommandBuilder will not help you if you don't have a primary key field.

Answer 8

I also faced the same problem and got the following solution. Try it.

private void btnSave_Click(object sender, EventArgs e)
{
    ConnStr = "Data Source=FIN03;Initial Catalog=CmsTest;Integrated Security=True";
    cn = new SqlConnection(ConnStr);
    cn.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = cn;
    mytran = cn.BeginTransaction(IsolationLevel.Serializable );

    cmd.Transaction = mytran;
    try
    {

        scb = new SqlCommandBuilder(da);
        da.Update(ds, "tblworker");
        mytran.Commit ();
        MessageBox.Show("Data update successfully"); 

    }
    catch (Exception err)
    {

           mytran.Rollback();

            MessageBox.Show(err.Message.ToString());           
    }
}

Answer 9

From the page:

Note If there is enough information in the main query, the InsertCommand, UpdateCommand, and DeleteCommand commands are created by default when the TableAdapter is generated. If the TableAdapter's main query is more than a single table SELECT statement, it is possible the designer will not be able to generate the InsertCommand, UpdateCommand, and DeleteCommand. If these commands are not generated, you may receive an error when executing the TableAdapter.Update method.

So from what I can tell, these statements should have been generated. I'm using a single table.

Answer 10

you can use the following code for any table's it will automatically generates Insert, Update and Delete command for you.

private void savechanges(String tbl, SqlDataAdapter sqldap, DataSet dset)
    {

        try
        {
            SqlCommandBuilder scb = new SqlCommandBuilder(sqldap);
            scb.ConflictOption = ConflictOption.CompareRowVersion;
            sqldap.Update(dset, tbl);
            dset.AcceptChanges();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message.ToString());
        }
    }

Answer 11

You could do it by creating a new method to read the dB.

    public void Write(DataTable table) 
    {
     string selectStatement = "SELECT TOP (1000) [ID], [Artist], [Title], 
     [Album][Path] FROM [Prueba].[dbo].[Playlist]"; 
     SqlDataAdapter adapter = new SqlDataAdapter();
     adapter.SelectCommand = new SqlCommand(selectStatement, Connection); 
     SqlCommandBuilder sqlCommand = new SqlCommandBuilder();
     sqlCommand.DataAdapter= adapter;
     adapter.Update(table);
   }

:)

Answer 12

All you have to do is edit the database and ensure that at least one column is identified as a Primary Key.

In my simple example, this could be the name column – although more realistically I would add an auto-numbering identity column which will ensure that all values in that column are unique.

Resave everything, recreate a new DataSet, replace the DataGridView using drag and drop as before, and you’re good to go.

As usual, the answer is simple when you know it – incredibly frustrating when you don’t.

Source.