CODEWITHSUNDEEP
c#winforms
RichGK
RichGK

Reputation: 560

Update TableAdapter with DataSet - Update requires a valid DeleteCommand error

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

Upvotes: 0

Views: 9295

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460238

It means that you are using a DataAdapter to update a table which contains deleted DataRows (their RowState is Deleted). Then the DataAdapter uses the specified DeleteCommand to delete this row in the database. But you haven't provided it.

So you need to provide it.

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

Upvotes: 4

KekuSemau
KekuSemau

Reputation: 6853

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:
http://msdn.microsoft.com/library/vstudio/tf579hcz.aspx

Upvotes: 5

Related Questions