I've seen many posts related to UPDATE or DELETE not working when using Data Controls like GridView or FormView. To further confuse the issue, no errors occur when this behavior kicks in, so people are left perplexed with little to go on.
I suppose there could be other reasons for this behavior, but one of the reason I've recently tracked down is related to your DB Table definition. More specifically, if you have a Column defined that is Nullable, then you may very well face this problem if your DataSource control (i.e. SqlDataSource) is defined to use ConflictDetection where all your column values are compared prior to UPDATE/DELETE operation to ensure that someone else did not make changes just before you.
The reason why you UPDATE/DELETE operation may "fail" is because you can't compared Null to Null and expect a logical answer. In many languages and in SQL, the expression (NULL = NULL) returns FALSE. In fact, comparing any value to NULL will always return FALSE, so the auto-generated Conflict Detection enable SQL statements for UPDATE and DELETE operations will fail when involving a Nullable column (with NULL value).
For example, following is a sample SQL statement generated with ConflictDetection enabled:
UpdateCommand="UPDATE [TableNullable] SET [Col_Nullable] = @Col_Nullable, [Col_Nullable2] = @Col_Nullable2 WHERE [id] = @original_id AND [Col_Nullable] = @original_Col_Nullable AND [Col_Nullable2] = @original_Col_Nullable2"
Notice that each Column is simply compared (with =) to the "original" column values. Knowing that any comparison to NULL value will result in FALSE, the above WHERE CLAUSE will fail. To correct the problem, you can either change your columns to be Non-Nullable or change the WHERE CLAUSE of your UPDATE/DELETE SQL statements to something similar to below:
UpdateCommand="UPDATE [TableNullable] SET [Col_Nullable] = @Col_Nullable, [Col_Nullable2] = @Col_Nullable2 WHERE [id] = @original_id AND ISNULL([Col_Nullable],'') = ISNULL(@original_Col_Nullable,'') AND ISNULL([Col_Nullable2],'') = ISNULL(@original_Col_Nullable2,'')"
You must keep in mind, however, that this example uses columns that are Character type. You must keep in mind what your column type is when using ISNULL to translate it.
Finally, it's a good programming practice to check if your SQL operation went through okay. Problems resulting from above WILL NOT throw an error -- thus the confusion for many people. What you must do is to handle the Updated event for your DataSource control and check for the following (Delete event should be similar):
protected void SqlDataSource1_Updated(object sender, SqlDataSourceStatusEventArgs e)
{
e.AffectedRows; (if 0, then your operation failed)
e.Exception; (check for exceptions here)
e.ExceptionHandled; (set to True when you've handled it)
}
I hope this post was helpful to some of you that are facing this "simple" and yet elusive issue. Happy programming.