Update requires a valid UpdateCommand when passed

问题:

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?

回答1:

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.

回答2:

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

回答3:

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();

回答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();

回答5:

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.

回答6:

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.

回答7:

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());           
    }
}

回答8:

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.

回答9:

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):