UPDATE/DELETE not working in Data Controls

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.

Re: UPDATE/DELETE not working in Data Controls

Great! It is working prefect when using SQLDataSource even if the bound field is null, but when I use DataSet Table adaptor with ObjectDataSource, It is throwing error

ObjectDataSource 'ObjectDataSource1' could not find a non-generic method 'Update' that has parameters: any solution for this?

Regards

Gopinathraja

Re: UPDATE/DELETE not working in Data Controls

I also have been wrestling with the same issue described in the previous post. If anyone has any insight as to its reason and solution, it would be greatly appreciated.

Thanks in advance,

Les

Re: UPDATE/DELETE not working in Data Controls

http://kanthu.blogspot.com/

scroll down to objectdatasource bit, it explains it and a way around it.

Re: UPDATE/DELETE not working in Data Controls

You can easily use UPDATE/ DELETE command by doing this:

UpdateCommand="UPDATE [TableNullable] SET [Col_Nullable] = @Col_Nullable, [Col_Nullable2] = @Col_Nullable2 WHERE [id] = @original_id"

the problem is that the AND condition

Re: UPDATE/DELETE not working in Data Controls

How do you apply this to a datetime type instead of a character type?

I have this code but it will not work when the supposedly null value contains some data.

UpdateCommand="UPDATE [Admins] SET [AdminEmail] = @AdminEmail, [AdminPassword] = @AdminPassword, [AdminCreated] = @AdminCreated, [AdminLastLogin] = @AdminLastLogin, [FailedPasswordAttemptCount] = @FailedPasswordAttemptCount, [FailedPasswordAttemptWindowStart] = @FailedPasswordAttemptWindowStart, [LastPasswordChangedDate] = @LastPasswordChangedDate WHERE [AdminID] = @original_AdminID AND [AdminEmail] = @original_AdminEmail AND [AdminPassword] = @original_AdminPassword AND ISNULL([AdminCreated], '10/04/2006') = ISNULL(@original_AdminCreated, '10/04/2006') AND ISNULL([AdminLastLogin], '') = ISNULL(@original_AdminLastLogin, '') AND ISNULL([FailedPasswordAttemptCount], '') = ISNULL(@original_FailedPasswordAttemptCount, '') AND ISNULL([FailedPasswordAttemptWindowStart], '') = ISNULL(@original_FailedPasswordAttemptWindowStart, '') AND ISNULL([LastPasswordChangedDate], '') = ISNULL(@original_LastPasswordChangedDate, '')" OnUpdated="SqlDataSource1_Updated">

Re: UPDATE/DELETE not working in Data Controls

Thanks so much for this post I have really been struggling to get the new ado.net and asp.net to actually work like the book says it should. They seem to have left out this little fact. In my case in since I am dealing with a small companys database and concurancy was not going to be an issue I just unchecked Use optimmistic concurrency and now my updates post back

Re: UPDATE/DELETE not working in Data Controls

It seems that the solution is just setting ConflictDetection to OverwriteChanges instead of CompareAllValues. But that is not the problem in my case, because I've already set it to OverwriteChanges but still no updating happens. I've been searching for a solution for 2 days now but found nothing usefull, all the solutions I found didn't help me.

It seems that I'll throw the datagrid into the trash and do the work manually.

Re: UPDATE/DELETE not working in Data Controls

I had the same problem, that is one mean little checkbox!

Re: UPDATE/DELETE not working in Data Controls

This problem drove me mad for ages. Turned out I needed to use

proper.PropertiesMainDataTable propertyTable = propAdapter.GetPropertiesByPropertyId(propertyid);

instead of

proper.PropertiesMainDataTable propertyTable = new proper.PropertiesMainDataTable();

Silly mistake but easily done, maybe this might be useful for someone.