Guys & Anyone viewing. I've had the exact same problem. Built multiple web-apps. trying to figure out what is going on. Finally figured out what was happening in the auto-generated SQL that is created by visual webdeveloper. It all lies within the SQL
update commands WHERE value's. Sometimes or all the time, visual web adds alot of AND statements in the WHERE clause. Lot's of problems can arise from this and the best fix is to get your WHERE clause equal to just the primary key and nothing else.
LOOK BELOW and you can see what I'm talking about.
I believe that Optimistic Concurrency puts these AND values in and then SQL server checks for any values that have been changed by other users. Let's say both you and I are working on the same row of data and I make a change to the Office_Shop field, but
you don't but my change got written first. Optimistic Concurrency will requery the row and compare the data against all of the original values it had retrieved from your original query. Thus, since the value I changed doesn't match your original query, it
will not commit your changes without warning you. This really has nothing to do with the problems I was encountering; it was simply a matter of removing all blank or null values from EVERY row. Therefore, I had to come up with some creative ways of adding
default values for some things, particularly time/date fields.
It's all a little moot for me right now; SQL Server Express has bombed on me big time and I cannot even uninstall it since it no longer appears in the Add/Remove programs list. I'm beginning to conclude (how's that for a contradiction in terms), that this
whole package is for the birds. I'm about to backup my important data, reformat the drive, reinstall Windows and my apps that work and leave this stuff alone...just go back to the old tried and true stuff. Having been through about two weeks of hell now, I
don't think I want any more aggravation. Microsoft certainly hasn't sold me on the .NET technology, and I certainly won't be buying the full Visual Studio 2005 suite. I had hoped that the Express package would see me through the learning curve, but this just
isn't happening. If the full paid package is as ill-behaved as this, I can do without any of it.
Please modify this thread so that it quickly reflects the problem and the solution.
Updating a row in Gridview or Detailsview when a null value is present
IS POSSIBLE when Optimistic Concurrency is unchecked.
After unchecking Optimistic Concurrency, I was able to perform a record update with multiple NULL values in my form using both the Gridview and Detailsview.
I spent five hours researching this problem and I have seen many threads indicate that you simply cannot have null values in a form. Multiple erroneous solutions have been posted instructing people on how to intercept the null value during the RowUpdating
Event and replace them with non-Null values, etc. This simply isn't necessary so far as my testing has determined.
Updating a row in Gridview or Detailsview when a null value is present
IS POSSIBLE when Optimistic Concurrency is unchecked.
After unchecking Optimistic Concurrency, I was able to perform a record update with multiple NULL values in my form using both the Gridview and Detailsview.
I spent five hours researching this problem and I have seen many threads indicate that you simply cannot have null values in a form. Multiple erroneous solutions have been posted instructing people on how to intercept the null value during the RowUpdating
Event and replace them with non-Null values, etc. This simply isn't necessary so far as my testing has determined.
Sorry my reply was incomplete...I certainly don't have THE answer; I'm a relative Newbie to SQL Server and the .NET platform and it was very late when I wrote my reply. But I have found this...
That when using the Gridview and Detailsview controls I have found no way to make them work properly for Update and Insert if there are NULLs or Blank values in any column in any row of the table. Whether or not this has anything to do with Optimistic Concurrency,
I don't know. My comments on OC were based on your observation of the various code appended to the queries which I believe (don't know for sure, but it seems to make sense).
I spent the better part of a week researching and availing myself of some help here in this thread only to find the No Nulls/No Blanks situation. It almost seems like the controls have some built in 'validation' to ensure that each field is filled with a
correct data type; whether a bug or by design, I don't know and, like yourself, I wish the experienced SQL/.NET Gods would offer some opinion here.
The only way I could get the controls to work since I want the user to be able to enter NOTHING in SOME fields, was to apply default values. The problem here, I couldn't even get the back-end default settings (in the table) to work. I had some date/time
fields that had to be updated by the system...I wound up putting code for this on the Detailsview.Inserting or .Updating event. For optional fields, I added Default= "*optional*" to the parameters for each bound field in the DataSource (both insert and update).
This worked out fine. A pain in the butt, but it works, avoiding any NULL or Blank values in the table. I agree, OC should work regardless what the data is or isn't, but I think the problem is either a flaw or limitation of the controls.
For fun, here are some examples of how I've handled providing default and automatic values:
Protected Sub DetailsView1_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles DetailsView1.ItemInserting
If DetailsView1.CurrentMode = DetailsViewMode.Insert Then
e.Values("LastUserUpdate") = Now.ToString("yyyy-MM-dd hh:mm")
End If
End Sub
The code above will handle the insertion of the current date into a field.
The code above adds deafults where needed and is duplicated in the Update parameters also but not in the "original_fieldname" parameters. Disregard the default values of the dates in my sample; these were put in for testing until I had solved the use of
the events to insert them in realtime.
I know this may not be much help and it is, as I say, not THE answer. If there is some inherent limitation to the controls, I have not seen mention of any in the docs...but the 'docs' is yet another issue, isn't it?
of some help here in this thread only to find the No Nulls/No Blanks situation. It almost seems like the controls have some built in 'validation' to ensure that each field is filled with a correct data type; whether a bug or by design, I don't know and, like
yourself, I wish the experienced SQL/.NET Gods would offer some opinion here.
(
([ColumnName] IS NULL AND @original_ColumnName IS NULL)
OR
([ColumnName] = @original_ColumnName)
)
</div></div>
<div style="CLEAR: left"></div>
This modification can be made directly through the declarative markup, via the UpdateQuery or DeleteQuery options from the Properties window, or through the UPDATE and DELETE tabs in the “Specify a custom SQL statement or stored procedure” option in the Configure Data Source wizard. Again, this modification must be made for every column in the
UpdateCommand
and
DeleteCommand
’s
WHERE
clause that can contain
NULL
values.
Applying this to our example results in the following modified
UpdateCommand
and
DeleteCommand
values:
<div class="p code">
<div>
UPDATE [Products] SET
[ProductName] = @ProductName,
[UnitPrice] = @UnitPrice,
[Discontinued] = @Discontinued
WHERE
[ProductID] = @original_ProductID AND
[ProductName] = @original_ProductName AND
(([UnitPrice] IS NULL AND @original_UnitPrice IS NULL)
OR ([UnitPrice] = @original_UnitPrice)) AND
[Discontinued] = @original_Discontinued
DELETE FROM [Products]
WHERE
[ProductID] = @original_ProductID AND
[ProductName] = @original_ProductName AND
(([UnitPrice] IS NULL AND @original_UnitPrice IS NULL)
OR ([UnitPrice] = @original_UnitPrice)) AND
[Discontinued] = @original_Discontinued
of some help here in this thread only to find the No Nulls/No Blanks situation. It almost seems like the controls have some built in 'validation' to ensure that each field is filled with a correct data type; whether a bug or by design, I don't know and, like
yourself, I wish the experienced SQL/.NET Gods would offer some opinion here.
Thank you...thank you...thank you!!!! Not just for some very helpful information, but for guiding me to the fact that I haven't lost all my marbles yet. (Although that event may not be far away!) Thanks again.
I did most of my early work on my computer and it worked fine. Just recently I started working from my company server and I had problems with seemingly simple things not working (example: Gridview shows update, and after you type the change and click update
again, the value is still the original).
Is this similar to the problem you are facing?
I ended up just using a major control to display content, and I use C# code behind with SQL queries to make changes, deletions, or adding content.
JRFisher
Member
6 Points
3 Posts
Re: AND THE ANSWER IS...
Jan 28, 2007 04:40 AM|LINK
The Login functions worked well on my local machine also.
I am trying to host on a company server running Win2003 Server Enterprise with IIS 6 on which I have administrative privleges.
The server is up to date, latest .net framework, all SP and patches etc.
I have other sites running correctly on this server........mostly ASP classic sites, so I know its not the server or its setup.
I will be adding default values and see if this solves the delete, edit issues. I will post my results after I try this.
nhosper
Member
2 Points
1 Post
Re: Update, Delete in Gridview and Detailsview controls don't work
Feb 09, 2007 02:33 AM|LINK
Guys & Anyone viewing. I've had the exact same problem. Built multiple web-apps. trying to figure out what is going on. Finally figured out what was happening in the auto-generated SQL that is created by visual webdeveloper. It all lies within the SQL update commands WHERE value's. Sometimes or all the time, visual web adds alot of AND statements in the WHERE clause. Lot's of problems can arise from this and the best fix is to get your WHERE clause equal to just the primary key and nothing else.
LOOK BELOW and you can see what I'm talking about.
Correct SQL UdateCommand:
UpdateCommand="UPDATE [Comp_Table] SET [Comp_AssetID] = @Comp_AssetID, [Comp_Model] = @Comp_Model, [Comp_Manuf] = @Comp_Manuf, [Comp_Type] = @Comp_Type, [Comp_Site_Location] = @Comp_Site_Location, [Comp_Dept] = @Comp_Dept, [Comp_User] = @Comp_User, [Comp_Building] = @Comp_Building, [Office_Shop] = @Office_Shop, [Comp_Image] = @Comp_Image, [Comp_OS] = @Comp_OS, [Comp_ServicePack] = @Comp_ServicePack, [Comp_RAM] = @Comp_RAM, [Comp_HD] = @Comp_HD, [Date_Deployed] = @Date_Deployed, [Date_Returnable] = @Date_Returnable, [Notes] = @Notes, [Surplus] = @Surplus, [RecordCreation] = @RecordCreation WHERE [Comp_SN] = @original_Comp_SN">
What visual webdeveloper generates: (this is incorrect and will NOT update your record)
UpdateCommand="UPDATE [Comp_Table] SET [Comp_AssetID] = @Comp_AssetID, [Comp_Model] = @Comp_Model, [Comp_Manuf] = @Comp_Manuf, [Comp_Type] = @Comp_Type, [Comp_Site_Location] = @Comp_Site_Location, [Comp_Dept] = @Comp_Dept, [Comp_User] = @Comp_User, [Comp_Building] = @Comp_Building, [Office_Shop] = @Office_Shop, [Comp_Image] = @Comp_Image, [Comp_OS] = @Comp_OS, [Comp_ServicePack] = @Comp_ServicePack, [Comp_RAM] = @Comp_RAM, [Comp_HD] = @Comp_HD, [Date_Deployed] = @Date_Deployed, [Date_Returnable] = @Date_Returnable, [Notes] = @Notes, [Surplus] = @Surplus, [RecordCreation] = @RecordCreation WHERE [Comp_SN] = @original_Comp_SN AND [Comp_AssetID] = @original_AND Comp_AssetID, [Comp_Model] = original_Comp_Model _AND [Comp_Manuf] = @original__AND Comp_Manuf, [Comp_Type] = @original__AND Comp_Type, [Comp_Site_Location] = @original_Comp_Site_Location, _AND [Comp_Dept] = @original_Comp_Dept, _AND [Comp_User] = @original_Comp_User, _AND [Comp_Building] = @original_Comp_Building, _AND [Office_Shop] = @original_Office_Shop, _AND [Comp_Image] = @original_Comp_Image, _AND [Comp_OS] = @original_Comp_OS, [Comp_ServicePack] = @original_Comp_ServicePack, _AND [Comp_RAM] = @original_Comp_RAM, _AND [Comp_HD] = @original_Comp_HD, [Date_Deployed] = @original_Date_Deployed, _AND [Date_Returnable] = @original_Date_Returnable, _AND [Notes] = @original_Notes, _AND [Surplus] = @original_Surplus, [RecordCreation] = @original_RecordCreation">
eyetech
Member
21 Points
49 Posts
Re: Update, Delete in Gridview and Detailsview controls don't work
Feb 09, 2007 02:59 AM|LINK
I believe that Optimistic Concurrency puts these AND values in and then SQL server checks for any values that have been changed by other users. Let's say both you and I are working on the same row of data and I make a change to the Office_Shop field, but you don't but my change got written first. Optimistic Concurrency will requery the row and compare the data against all of the original values it had retrieved from your original query. Thus, since the value I changed doesn't match your original query, it will not commit your changes without warning you. This really has nothing to do with the problems I was encountering; it was simply a matter of removing all blank or null values from EVERY row. Therefore, I had to come up with some creative ways of adding default values for some things, particularly time/date fields.
It's all a little moot for me right now; SQL Server Express has bombed on me big time and I cannot even uninstall it since it no longer appears in the Add/Remove programs list. I'm beginning to conclude (how's that for a contradiction in terms), that this whole package is for the birds. I'm about to backup my important data, reformat the drive, reinstall Windows and my apps that work and leave this stuff alone...just go back to the old tried and true stuff. Having been through about two weeks of hell now, I don't think I want any more aggravation. Microsoft certainly hasn't sold me on the .NET technology, and I certainly won't be buying the full Visual Studio 2005 suite. I had hoped that the Express package would see me through the learning curve, but this just isn't happening. If the full paid package is as ill-behaved as this, I can do without any of it.
[8o|]
Kaemon
Member
20 Points
11 Posts
Re: Update, Delete in Gridview and Detailsview controls don't work
Feb 09, 2007 05:53 AM|LINK
To the moderators.
Please modify this thread so that it quickly reflects the problem and the solution.
Updating a row in Gridview or Detailsview when a null value is present IS POSSIBLE when Optimistic Concurrency is unchecked.
After unchecking Optimistic Concurrency, I was able to perform a record update with multiple NULL values in my form using both the Gridview and Detailsview.
I spent five hours researching this problem and I have seen many threads indicate that you simply cannot have null values in a form. Multiple erroneous solutions have been posted instructing people on how to intercept the null value during the RowUpdating Event and replace them with non-Null values, etc. This simply isn't necessary so far as my testing has determined.
Gridview Update doesn't work Null
eyetech
Member
21 Points
49 Posts
Re: Update, Delete in Gridview and Detailsview controls don't work
Feb 09, 2007 12:47 PM|LINK
Sorry my reply was incomplete...I certainly don't have THE answer; I'm a relative Newbie to SQL Server and the .NET platform and it was very late when I wrote my reply. But I have found this...
That when using the Gridview and Detailsview controls I have found no way to make them work properly for Update and Insert if there are NULLs or Blank values in any column in any row of the table. Whether or not this has anything to do with Optimistic Concurrency, I don't know. My comments on OC were based on your observation of the various code appended to the queries which I believe (don't know for sure, but it seems to make sense).
I spent the better part of a week researching and availing myself of some help here in this thread only to find the No Nulls/No Blanks situation. It almost seems like the controls have some built in 'validation' to ensure that each field is filled with a correct data type; whether a bug or by design, I don't know and, like yourself, I wish the experienced SQL/.NET Gods would offer some opinion here.
The only way I could get the controls to work since I want the user to be able to enter NOTHING in SOME fields, was to apply default values. The problem here, I couldn't even get the back-end default settings (in the table) to work. I had some date/time fields that had to be updated by the system...I wound up putting code for this on the Detailsview.Inserting or .Updating event. For optional fields, I added Default= "*optional*" to the parameters for each bound field in the DataSource (both insert and update). This worked out fine. A pain in the butt, but it works, avoiding any NULL or Blank values in the table. I agree, OC should work regardless what the data is or isn't, but I think the problem is either a flaw or limitation of the controls.
For fun, here are some examples of how I've handled providing default and automatic values:
Protected Sub DetailsView1_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles DetailsView1.ItemInserting If DetailsView1.CurrentMode = DetailsViewMode.Insert Then e.Values("LastUserUpdate") = Now.ToString("yyyy-MM-dd hh:mm") End If End SubThe code above will handle the insertion of the current date into a field.
The code above adds deafults where needed and is duplicated in the Update parameters also but not in the "original_fieldname" parameters. Disregard the default values of the dates in my sample; these were put in for testing until I had solved the use of the events to insert them in realtime.
I know this may not be much help and it is, as I say, not THE answer. If there is some inherent limitation to the controls, I have not seen mention of any in the docs...but the 'docs' is yet another issue, isn't it?
drusho
Member
2 Points
1 Post
Re: Update, Delete in Gridview and Detailsview controls don't work
Feb 13, 2007 03:38 PM|LINK
I have been following this issue for the past 3 weeks.
I finally solved it: It is the placement of the CommandField bar
By default it is on the bottom.
In my case I had placed it on the top. So Insert worked but Delete and Updated never succeeded.
So I replaced the command field on the bottom and and all the changed fields updated even blank (NULL) fields.
Final test I placed the commandField in the middle of my info.
The rows above it were updated, the rows below not changed
The delete function on worked when it was below the Identity row.
Hope this helps
gridview ASP.NET Visual Web Express
Kaemon
Member
20 Points
11 Posts
Re: Update, Delete in Gridview and Detailsview controls don't work
Mar 23, 2007 07:16 PM|LINK
Scott Mitchell just discussed the issue of Null Values and Optimistic Concurrency in his Data Tutorials article, "Implementing Optimistic Concurrency with the SqlDataSource"
****************************
Correctly Handling Values
Unfortunately, the augmented
and statements autogenerated by the Configure Data Source wizard when using optimistic concurrency do not work with records that contain values. To see why, consider our SqlDataSource’s : <div class="p code"> <div></div></div> <div style="CLEAR: left"></div>The
column in the table can have values. If a particular record has a value for , the clause portion “” will always evaluate to False because always returns False. Therefore, records that contain values cannot be edited or deleted, as the and statements’ clauses won’t return any rows to update or delete.Note: This bug was first reported to Microsoft in June of 2004 in SqlDataSource Generates Incorrect SQL Statements and is reportedly scheduled to be fixed in the next version of ASP.NET.
To fix this, we have to manually update the
clauses in both the and properties for all columns that can have values. In general, change to: <div class="p code"> <div>( ([ColumnName] IS NULL AND @original_ColumnName IS NULL) OR ([ColumnName] = @original_ColumnName) )</div></div> <div style="CLEAR: left"></div>This modification can be made directly through the declarative markup, via the UpdateQuery or DeleteQuery options from the Properties window, or through the UPDATE and DELETE tabs in the “Specify a custom SQL statement or stored procedure” option in the Configure Data Source wizard. Again, this modification must be made for every column in the
and ’s clause that can contain values.Applying this to our example results in the following modified
and values: <div class="p code"> <div>UPDATE [Products] SET [ProductName] = @ProductName, [UnitPrice] = @UnitPrice, [Discontinued] = @Discontinued WHERE [ProductID] = @original_ProductID AND [ProductName] = @original_ProductName AND (([UnitPrice] IS NULL AND @original_UnitPrice IS NULL) OR ([UnitPrice] = @original_UnitPrice)) AND [Discontinued] = @original_Discontinued DELETE FROM [Products] WHERE [ProductID] = @original_ProductID AND [ProductName] = @original_ProductName AND (([UnitPrice] IS NULL AND @original_UnitPrice IS NULL) OR ([UnitPrice] = @original_UnitPrice)) AND [Discontinued] = @original_Discontinued</div></div>Null Values Optimistic Concurrency Update Insert
eyetech
Member
21 Points
49 Posts
Re: Update, Delete in Gridview and Detailsview controls don't work
Mar 23, 2007 10:17 PM|LINK
Kaemon:
Thank you...thank you...thank you!!!! Not just for some very helpful information, but for guiding me to the fact that I haven't lost all my marbles yet. (Although that event may not be far away!) Thanks again.
Don
chlc1966
Member
8 Points
4 Posts
Re: Update, Delete in Gridview and Detailsview controls don't work
Jan 22, 2008 08:24 PM|LINK
sorry! didn't work...
ldechent
Contributor
6326 Points
1577 Posts
Re: Update, Delete in Gridview and Detailsview controls don't work
Jan 23, 2008 12:02 PM|LINK
What environment are you working in?
I did most of my early work on my computer and it worked fine. Just recently I started working from my company server and I had problems with seemingly simple things not working (example: Gridview shows update, and after you type the change and click update again, the value is still the original).
Is this similar to the problem you are facing?
I ended up just using a major control to display content, and I use C# code behind with SQL queries to make changes, deletions, or adding content.
-Larry