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?

Solution 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.

Solution 2

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

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

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

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

View more solutions