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):
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?
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 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
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.
jcasp
Star
11540 Points
2286 Posts
UPDATE/DELETE not working in Data Controls
Jan 18, 2006 07:32 PM|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:
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.
Gopinath
Member
30 Points
6 Posts
Re: UPDATE/DELETE not working in Data Controls
Feb 15, 2006 09:36 AM|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?
Regards
Gopinathraja
lprigmore
Member
239 Points
52 Posts
Re: UPDATE/DELETE not working in Data Controls
Feb 28, 2006 08:38 PM|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,
Les
ninjamonk
Member
70 Points
20 Posts
Re: UPDATE/DELETE not working in Data Controls
Mar 01, 2006 09:04 AM|LINK
http://kanthu.blogspot.com/
scroll down to objectdatasource bit, it explains it and a way around it.
a4nsd
Participant
1124 Points
299 Posts
Re: UPDATE/DELETE not working in Data Controls
Oct 02, 2006 09:23 AM|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
doank
Member
94 Points
34 Posts
Re: UPDATE/DELETE not working in Data Controls
Oct 05, 2006 06:31 PM|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.
jrcrawford10...
Member
9 Points
6 Posts
Re: UPDATE/DELETE not working in Data Controls
Mar 03, 2007 10:58 PM|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
ashraf_gawda...
Member
27 Points
15 Posts
Re: UPDATE/DELETE not working in Data Controls
Mar 25, 2007 04:10 PM|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.
LorJay
Member
5 Points
13 Posts
Re: UPDATE/DELETE not working in Data Controls
Sep 12, 2007 04:25 PM|LINK
I had the same problem, that is one mean little checkbox!
cemiess
Member
15 Points
14 Posts
Re: UPDATE/DELETE not working in Data Controls
Jan 01, 2010 02:00 PM|LINK
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.