CODEWITHSUNDEEP
c#databasevisual-studio-2012
Matthew Winter
Matthew Winter

Reputation: 65

tableadaptor - delete rows

I have a dataset created in visual studio designer. It has a tableadaptor for charges.

One of the columns in the charges table is 'booking'. This column is NOT a primary key.

I want to delete all rows where the value of 'booking' is 27.

I added a query to the chargesTableAdaptor called DeleteByBooking. It's code:

DELETE FROM charges
WHERE        (chargesid = @Original_chargesid)

In my code I then run:

this.chargesTableAdapter.DeleteBookingCharges(27);
this.chargesBindingSource.EndEdit();
this.chargesTableAdapter.Update(this.quick_quoteDataSet.charges);

Despite the code building fine, and the app running ok, no rows get deleted from the database!

What am I doing wrong please?

Upvotes: 3

Views: 4113

Answers (2)

MadDev
MadDev

Reputation: 113

If you are using a dataGrid as control you can try this without SQL queries, equally you can adapt this code just editing the dataset line.

DataSet.yourRow iAmARow;
try
{

   iAMARow = 
   this.yourDataSet.yourTable.FindByBooking(nameOfTheColumn);

   if (iAmARow != null)
   {
       iAmARow.Delete();
       this.yourTableAdapter.Update(this.yourDataSet);
       this.yourDataSet.yourTable.AcceptChanges();

   }

}
catch (SqlException ex)
{
   ex.StackTrace.ToString();
}

I hope that it can help you. ;)

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460238

I added a query to the chargesTableAdaptor called DeleteByBooking

If you add a query manually, you have to call this method on your TableAdapter instance manually:

chargesTableAdapter.DeleteByBooking(27);

If you want to use the Update functionality on your (strongly typed) DataSet,DataTable or DataRow, you have to provide the DeleteCommand of the TableAdapter.

MSDN

TableAdapter Update Method

TableAdapters use data commands to read to and write from the database. The TableAdapter's initial Fill (main) query is used as the basis for creating the schema of the associated data table, as well as the InsertCommand, UpdateCommand, and DeleteCommand commands associated with the TableAdapter.Update method. This means that calling a TableAdapter's Update method executes the statements created when the TableAdapter was originally configured, and not one of the additional queries added with the TableAdapter Query Configuration Wizard.

Note that the commands are created automatically if you select one table in the TableAdapter configuration wizard. If you join multiple tables you have to create the Update,Insert and DeleteCommand manually.

Upvotes: 2

Related Questions