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?

Slovene

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

Profiteer

Answer 2

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

Enthusiasm

And you can add a new row even with a primary key - it's the Update/Delete that fails – Clash

Took me hours to figure this out. What a silly mistake on my part. Thank you. – Complaint

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

Treulich

This worked for me with SQLiteCommandBuilder (and of course I was using the SQLiteDataAdapter). – Fume

This also worked for me using Postgresql and NpgsqlDataAdapter – Dwelling

Probably redundant, but this works with SqlCommandBuilder and SqlDataAdapter too – Saavedra

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

Hitch

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

Braunschweig

Thank you for your answer! Whether if I add a primary key ir not, when I go to Advanced Options the last checkbox "Refresh the data table" is not even enabled! Do you know how to enable it? Thanks! – Cartelize

@Cartelize - I don't I'm afraid, have you definitely made a change? The only thing I can think of is that the wizard doesn't believe anything has changed so has the checkbox disabled. – Braunschweig

Oh :(. I have saved all changes (creating the PK in the DataSet Editor), closed my Visual Studio application and reopened it, saw the PK created and the checkbox is not enabled. – Cartelize

Just to clarify: the DB was made on MS Access 2007 with just one table, the connection was done using the VS 2010 Assistant, I am working with VB.NET. Also, I tried to save the DB from Access using a different name and making a new connection but the problem holds. With new DBs from Access the problem is solved. Maybe my DB is corrupted? – Cartelize

I was using MS SQL - this was a while back so I don't remember the details I'm afraid – Braunschweig

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.

Hypersonic

Didn't see this until after I posted my answer but you're right; the code needs recreating - you don't actually need to remove the original table just go through the wizard again mind (but not much difference in time doing either I guess!) – Braunschweig

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.

Inner

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

Slovene

Please be advised, that UPDATE and DELETE statements cannot be generated for tables without a primary key. Just ran into the same problem, where the PK was omitted on the database table and the TableAdapter-Wizard failed to generate the UPDATE command, causing the above stated behavior. – Twinscrew

Answer 9

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

Minded

Your code is not properly formatted. please format it. – Traditionalism

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

Syndesmosis

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

:)

Keyway

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Abarca

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.

Dipsomaniac