Update requires a valid InsertCommand when passed DataRow collection with new rows

Question

I am trying to add a new row to my database . Here is my code :

ds1 is my Dataset , da1 is my data adapter

        dRow = ds1.Tables["localitati"].NewRow();
        dRow[1] = aux1.Replace(" ", "").Replace("-", "").ToLower();
        dRow[2] = aux2.ToLower().Replace(" ", "");
        dRow[3] = aux1;
        dRow[4] = e.X;
        dRow[5] = e.Y;
        ds1.Tables["localitati"].Rows.Add(dRow);

        da1.Update(ds1, "localitati");

at the da1.update(ds1,"localitati"); the program stops and gives me the error : "Update requires a valid InsertCommand when passed DataRow collection with new rows."

The connection to the database works ( i have retrieved info from the db )

Any ideas ?

Ali Saeed · Answer 1 · 2013-12-31T18:55:45.233

For adding DataRows:

SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

// add rows to dataset

builder.GetInsertCommand();

//Without the SqlCommandBuilder this line would fail
adapter.Update(dataSet);

Explanation:

adapter.Update(dataset) will try to save the changes in the dataset to the database. It will need:

InsertCommand (if DataRows were added)
DeleteCommand (if DataRows were deleted)
UpdateCommand (if DataRows were modified)

You can put a breakpoint to inspect your adapter.InsertCommand before the adapter.Update() line to see if it is set.

To set them, simply create a SqlCommandBuilder and fire GetInsertCommand(), GetDeleteCommand(), etc.

This should solve the errors:

"Update requires a valid Update Command when passed DataRow collection with modified rows." Or
"Update requires a valid Insert Command when passed DataRow collection with new rows.", etc.

See MSDN Link for More Info

score 8 · Answer 2 · edited Dec 12 '12 at 12:23

Quesion Solved;Your question:

dRow = ds1.Tables["localitati"].NewRow();
dRow[1] = aux1.Replace(" ", "").Replace("-", "").ToLower();
dRow[2] = aux2.ToLower().Replace(" ", "");
dRow[3] = aux1;
dRow[4] = e.X;
dRow[5] = e.Y;
ds1.Tables["localitati"].Rows.Add(dRow);

da1.Update(ds1, "localitati");

Answer:

you must use commandBuilder. That is before your update using dataAdapter (or before you creating dataRow) Add the code:

SqlCommandBuilder cmdb = new SqlCommandBuilder(da);

score 6 · Accepted Answer · answered Nov 05 '10 at 11:29

6

You must define an InsertCommand for you DataAdapter

http://www.codeproject.com/KB/database/relationaladonet.aspx

answered Nov 05 '10 at 11:29

Alex

14,338
5
41
59

1

i've also found this http://csharp.net-informations.com/dataadapter/insertcommand-sqlserver.htm for anyone else who is interested – Alex Nov 05 '10 at 12:13
2

There is no point in using a DataAdapter in that case. It's a pure example about what isn't right to do. Unparametrized query, useless DataAdapter, unclosed connection, string initialization with null - all things that shouldn't exist in a project. – Alex Nov 05 '10 at 13:21

score 0 · Answer 4 · answered Oct 22 '21 at 18:36

0

dRow = ds1.Tables["localitati"].NewRow();
        dRow[1] = aux1.Replace(" ", "").Replace("-", "").ToLower();
        dRow[2] = aux2.ToLower().Replace(" ", "");
        dRow[3] = aux1;
        dRow[4] = e.X;
        dRow[5] = e.Y;
        ds1.Tables["localitati"].Rows.Add(dRow);
        SqlCommandBuilder scb = new SqlCommandBuilder(da);//just add this line
        da1.Update(ds1, "localitati");

answered Oct 22 '21 at 18:36

Subhadeep

30
9

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 22 '21 at 19:38

Update requires a valid InsertCommand when passed DataRow collection with new rows

4 Answers4

Linked

Related