CODEWITHSUNDEEP
c#.nettableadapter
Sam
Sam

Reputation: 7678

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

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?

Upvotes: 19

Views: 63025

Answers (12)

Hellen Rios
Hellen Rios

Reputation: 1

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

:)

Upvotes: 0

paywand dlshad nejeeb
paywand dlshad nejeeb

Reputation: 77

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

Upvotes: 0

Jaky Suriyakul Na Ayudhya
Jaky Suriyakul Na Ayudhya

Reputation: 1

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.

Upvotes: -1

d219
d219

Reputation: 2835

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

enter image description here

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

enter image description here

Upvotes: 3

Md Shahriar
Md Shahriar

Reputation: 2776

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.

Upvotes: 1

Sam
Sam

Reputation: 7678

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.

Upvotes: 0

MulugetaLegesse
MulugetaLegesse

Reputation: 1

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

Upvotes: 0

LaBird
LaBird

Reputation: 299

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.

Upvotes: 2

Jason
Jason

Reputation: 309

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

Upvotes: 30

Hoang
Hoang

Reputation: 91

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

Upvotes: 9

Dan
Dan

Reputation: 332

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

Upvotes: 19

Henk Holterman
Henk Holterman

Reputation: 273554

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.

Upvotes: 9

Related Questions