Last post Jan 01, 2010 10:00 AM by cemiess
Jan 18, 2006 03:32 PM|jcasp|LINK
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:
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):
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.
Feb 15, 2006 05:36 AM|Gopinath|LINK
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?
Feb 28, 2006 04:38 PM|lprigmore|LINK
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,
Mar 01, 2006 05:04 AM|ninjamonk|LINK
scroll down to objectdatasource bit, it explains it and a way around it.
Oct 02, 2006 05:23 AM|a4nsd|LINK
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
Oct 05, 2006 02:31 PM|doank|LINK
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">
Mar 03, 2007 06:58 PM|jrcrawford1010|LINK
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
Mar 25, 2007 12:10 PM|ashraf_gawdat|LINK
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.
Sep 12, 2007 12:25 PM|LorJay|LINK
I had the same problem, that is one mean little checkbox!
Jan 01, 2010 10:00 AM|cemiess|LINK
This problem drove me mad for ages. Turned out I needed to use
proper.PropertiesMainDataTable propertyTable = propAdapter.GetPropertiesByPropertyId(propertyid);
proper.PropertiesMainDataTable propertyTable = new proper.PropertiesMainDataTable();
Silly mistake but easily done, maybe this might be useful for someone.