29319

Update TableAdapter with DataSet - Update requires a valid DeleteCommand error

Question:

In the code below what does "Update requires a valid DeleteCommand when passed DataRow collection with deleted rows." mean?

foreach (DataGridViewRow item in this.dataGridView2.SelectedRows) { fuelStopsDataSet1.Tables[0].Rows[item.Index].Delete(); } this.fuelStopsTableAdapter.Update(this.fuelStopsDataSet1.FuelStops);

Answer1:

It means that you are using a DataAdapter to update a table which contains deleted DataRows (their <a href="http://msdn.microsoft.com/en-us/library/system.data.datarowstate.aspx" rel="nofollow">RowState</a> is Deleted). Then the DataAdapter uses the specified <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.deletecommand.aspx" rel="nofollow">DeleteCommand</a> to delete this row in the database. But you haven't provided it.

So you need to provide it.

<a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.deletecommand.aspx" rel="nofollow">MSDN</a> example:

public static SqlDataAdapter CreateCustomerAdapter( SqlConnection connection) { SqlDataAdapter adapter = new SqlDataAdapter(); // Create the SelectCommand. SqlCommand command = new SqlCommand("SELECT * FROM Customers " + "WHERE Country = @Country AND City = @City", connection); // Add the parameters for the SelectCommand. command.Parameters.Add("@Country", SqlDbType.NVarChar, 15); command.Parameters.Add("@City", SqlDbType.NVarChar, 15); adapter.SelectCommand = command; // Create the InsertCommand. command = new SqlCommand( "INSERT INTO Customers (CustomerID, CompanyName) " + "VALUES (@CustomerID, @CompanyName)", connection); // Add the parameters for the InsertCommand. command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID"); command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"); adapter.InsertCommand = command; // Create the UpdateCommand. command = new SqlCommand( "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " + "WHERE CustomerID = @oldCustomerID", connection); // Add the parameters for the UpdateCommand. command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID"); command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"); SqlParameter parameter = command.Parameters.Add( "@oldCustomerID", SqlDbType.NChar, 5, "CustomerID"); parameter.SourceVersion = DataRowVersion.Original; adapter.UpdateCommand = command; // Create the DeleteCommand. command = new SqlCommand( "DELETE FROM Customers WHERE CustomerID = @CustomerID", connection); // Add the parameters for the DeleteCommand. parameter = command.Parameters.Add( "@CustomerID", SqlDbType.NChar, 5, "CustomerID"); parameter.SourceVersion = DataRowVersion.Original; adapter.DeleteCommand = command; return adapter; }

The last command is the DeleteCommand.

Answer2:

You can have the DeleteCommand be created automatically in some situations (inferred from the SelectCommand).

SqlDataAdapter da = new SqlDataAdapter("...SELECT Statement...", connection); SqlCommandBuilder cmd_b = new SqlCommandBuilder(da); // this already creates // the Update- and DeleteCommands for the DA

Here is an article about it:<br /><a href="http://msdn.microsoft.com/library/vstudio/tf579hcz.aspx" rel="nofollow">http://msdn.microsoft.com/library/vstudio/tf579hcz.aspx</a>

Recommend

  • How to retrieve data based on two conditions given?
  • DataGridView / DataTable | rowState doesn't change
  • DataTable Rowstate always in Added state
  • Add Spinner dynamically in a loop
  • What is the optimal way to use setInterval in jquery ajax calls?
  • GridView take a Row
  • Using a list to receive rows from a database
  • GridView template Column conditionally set to readonly
  • 'doc_del_count' bigger than 'doc_count' on CouchDB
  • Compress a file with GZipStream while maintaining its meta-data
  • How can I get the choice “H2” back in the H2 consol?
  • Upload file that is in the cpan database
  • Enabling DTD support in Sql Server
  • UWP/C# - Issue with AQS and USB Devices
  • Silverlight DependencyProperty.SetCurrentValue Equivalent
  • Zurb Foundation _global.scss meta styles for js?
  • Database structure design with variable amounts of fields
  • Adding a button at the bottom of a table view
  • Retrieving value from sql ExecuteScalar()
  • Getting last autonumber in access
  • Submit form in a displaytag pagination
  • How to extract text from Word files using C#?
  • How to check if every primary key value is being referenced as foreign key in another table
  • Sending data from AppleScript to FileMaker records
  • Join two tables and save into third-sql
  • How to make Safari send if-modified-since header?
  • Delete MySQLi record without showing the id in the URL
  • GridView Sorting works once only
  • Invalid access key error using credentials redeemed from an amazon open id token
  • How do I rollback to a specific git commit
  • Unanticipated behavior
  • Comma separated Values
  • retrieve vertices with no linked edge in arangodb
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Trying to get generic when generic is not available
  • Understanding cpu registers
  • Add sale price programmatically to product variations
  • Can't mass-assign protected attributes when import data from csv file
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • Unable to use reactive element in my shiny app