Member Avatar for harcaype

Update requires a valid DeleteCommand when passed DataRow collection with deleted rows. This is the error I get when I'm deleting a row in my datagrid. I referred my code to a running program but when I embedded it on my own it occured that way. Here's the code:

myConn.Open();
            dTable = new DataTable("Records");
            dTable = this.ds.Tables[0];
            int i = this.iRowIndex;
            dTable.Rows[i].Delete();
            this.da.Update(dTable);

Any suggestions?

  • 2 Contributors
  • 2 Replies
  • 174 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by harcaype

Dani AI

Generated

That exception means the DataAdapter found DataRow(s) with RowState = Deleted but it had no DeleteCommand to translate those into SQL DELETEs. 's AcceptChanges() makes the deletion disappear from the change-tracking (Deleted rows are removed by AcceptChanges), which is why the grid changed but the database did not. Do not call AcceptChanges before calling Update — AcceptChanges commits changes in-memory and prevents Update from sending anything to the database. See the DataTable.AcceptChanges doc: DataTable.AcceptChanges.

Fix the problem by giving the DataAdapter a valid DeleteCommand (or letting a SqlCommandBuilder generate one) and then calling Update on the DataTable that contains the Deleted rows. Example of manually wiring a DeleteCommand and mapping the primary-key parameter (set SourceVersion to Original so the adapter uses the original key value for the WHERE clause):

da.DeleteCommand = new SqlCommand(
    "DELETE FROM YourTable WHERE ID = @ID", myConn);
da.DeleteCommand.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
    .SourceVersion = DataRowVersion.Original;

Alternatively, if your DataAdapter has a simple SELECT and the table has a primary key, a SqlCommandBuilder can auto-create the DeleteCommand:

  • new SqlCommandBuilder(da);

After that, mark the row as deleted (DataRow.Delete()) and call da.Update(yourDataTable). The Update method will send the DELETE(s) to the database and will call AcceptChanges on rows that were successfully persisted. See the DataAdapter.Update documentation: and the SqlCommandBuilder notes: SqlCommandBuilder.

Quick troubleshooting: verify the DataAdapter.DeleteCommand is not null, that the parameter SourceColumn matches your PK, and that the row you deleted still reports RowState = Deleted before calling Update. This will ensure the change actually reaches the database.

Recommended Answers

All 2 Replies

Member Avatar for ema005

Before calling the update() method of the data Adapter call the acceptchanges of the datatable
type dTable.AcceptChanges(); i.e

myConn.Open();
        dTable = new             DataTable("Records");
            dTable = this.ds.Tables[0];
            int i = this.iRowIndex;
            dTable.Rows[i].Delete();
dTable.AcceptChanges();
this.da.Update(dTable);

hope this help.

Member Avatar for harcaype

thanks! it worked! however. it does not affect the database itself. how can I make it delete from there too?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.