C# SqlCommandBuilder not performing delete

Im going insane trying to figure this one out.

Im populating a DataTable from my database.
A DataGridView DataSource is set to my DataTable.

I insert, update and delete rows from the DataTable which in turn updates the DataGridView.

Im using the below to update my changes back to the database where i pass in the modified DataTable.
Insert and Update are working but Delete does not remove the deleted rows from the database table.


private static void updateDatabase(DataTable dt)
{
SqlDataAdapter myAdapter = new SqlDataAdapter();
SqlCommand myCommand = new SqlCommand("SELECT * from dbo.DisplayList", sqlCon);
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);

myAdapter.SelectCommand = myCommand;

myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand();
myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand();
myAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand();

myAdapter.Update(dt);
}


The myAdapter.DeleteCommand.CommandText is:

DELETE FROM [dbo].[DisplayList] WHERE (([PropertyID] = @p1) AND
([DisplayID] = @p2) AND ([DisplayName] = @p3) AND ((@p4 = 1 AND
[TerminalID] IS NULL) OR ([TerminalID] = @p5)))


Anyone have any ideas whats wrong with the above or to help investigation how i can find out what the @p values are being converted to?

I hardly ever use the dataadapter- I use straight up cmd objects and stored procs... but...

so that delete command you posted... I assume that is captured off a break point on myAdapter.DeleteCommand = etc ?

so my question is this... that is not a valid sql statement...

DELETE FROM [dbo].[DisplayList] WHERE (([PropertyID] = @p1) AND
([DisplayID] = @p2) AND ([DisplayName] = @p3) AND ((@p4 = 1 AND
[TerminalID] IS NULL) OR ([TerminalID] = @p5)))


it should pop an error, unless you have those values @p1,@p2, etc... if you DO have those values, have you tried running that query in SQLManagement studio?

That SQL is retrieved by using MessageBox.Show(myAdapter.DeleteCommand.CommandText);

I believe the SqlCommandBuilder automatically replaces the @p values with actual values when it runs.

The update and inserts show the same @p values but they work fine.

Finally figured it out.

When deleting the rows from the datatable i was using:
myDataTable.Rows.RemoveAt(rowindex);
This completly removes it from the datatable so when the SqlCommandBuilder came around to deleting it from the database it never knew it existed in the first place.

For it to work you need to flag the row as deleted but not actually delete it.
myDataTable.Rows[rowindex].Delete();

By doing this it all worked.