Answered by:
SqlDataSource using optimistic concurrency won't persist updates

Question
-
User-1427834641 posted
Using VS 2012, I have a Web Application Project using the beloved Northwind database. The problem is a bit involved, but basically involves SqlDataSource using ConflictDetection="CompareAllValues" and updates not working.
- started with an Asp.Net Empty Web Application
- Added a Asp:GridView
- Configured its datasource for
- Sql Database
- Browsed to find the existing database
- Chose the Customers Table – all columns
- Advanced button – checked both 'Generate Insert, Update, and Delete Statements' and 'Use optimistic concurrency'.
- On the GridView, enabled Paging and Editing
Tests run fine. Able to update any column while SqlDataSource is set with ConflictDetection="CompareAllValues".
Now some changes:
- Changed GridView 'Country' column from asp:BoundField to asp:TemplateField using an EditItemTemplate using a DropDownList of countries.
- Added GridView RowDataBound event handler for: If row in edit mode, show the current country as selected in the list.
- Added GridView RowUpdating event handler for: During update, save the currently selected country from the list of countries.
Now update is broken. Unable to update any column - nothing persists.
** Noticed during the GridView RowUpdating event handler, in the GridViewUpdateEventArgs, the collections in NewValues and OldValues no longer include the country!
Code excerpts:
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" DataKeyNames="CustomerID" DataSourceID="SqlDataSource1" OnRowDataBound="GridView1_RowDataBound" OnRowUpdating="GridView1_RowUpdating"> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:BoundField DataField="CustomerID" HeaderText="CustomerID" ReadOnly="True" SortExpression="CustomerID" /> <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" SortExpression="CompanyName" /> <asp:BoundField DataField="ContactName" HeaderText="ContactName" SortExpression="ContactName" /> <asp:BoundField DataField="ContactTitle" HeaderText="ContactTitle" SortExpression="ContactTitle" /> <asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address" /> <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" /> <asp:BoundField DataField="Region" HeaderText="Region" SortExpression="Region" /> <asp:BoundField DataField="PostalCode" HeaderText="PostalCode" SortExpression="PostalCode" /> <%-- <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />--%> <asp:TemplateField HeaderText="Country" SortExpression="Country"> <ItemTemplate> <%# Eval("Country") %> </ItemTemplate> <EditItemTemplate> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="Country" DataValueField="Country"> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind2012ConnectionString %>" SelectCommand="SELECT DISTINCT [Country] FROM [Customers]"></asp:SqlDataSource> </EditItemTemplate> </asp:TemplateField> <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /> <asp:BoundField DataField="Fax" HeaderText="Fax" SortExpression="Fax" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:Northwind2012ConnectionString %>" DeleteCommand="DELETE FROM [Customers] WHERE [CustomerID] = @original_CustomerID AND [CompanyName] = @original_CompanyName AND (([ContactName] = @original_ContactName) OR ([ContactName] IS NULL AND @original_ContactName IS NULL)) AND (([ContactTitle] = @original_ContactTitle) OR ([ContactTitle] IS NULL AND @original_ContactTitle IS NULL)) AND (([Address] = @original_Address) OR ([Address] IS NULL AND @original_Address IS NULL)) AND (([City] = @original_City) OR ([City] IS NULL AND @original_City IS NULL)) AND (([Region] = @original_Region) OR ([Region] IS NULL AND @original_Region IS NULL)) AND (([PostalCode] = @original_PostalCode) OR ([PostalCode] IS NULL AND @original_PostalCode IS NULL)) AND (([Country] = @original_Country) OR ([Country] IS NULL AND @original_Country IS NULL)) AND (([Phone] = @original_Phone) OR ([Phone] IS NULL AND @original_Phone IS NULL)) AND (([Fax] = @original_Fax) OR ([Fax] IS NULL AND @original_Fax IS NULL))" InsertCommand="INSERT INTO [Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Customers]" UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE [CustomerID] = @original_CustomerID AND [CompanyName] = @original_CompanyName AND (([ContactName] = @original_ContactName) OR ([ContactName] IS NULL AND @original_ContactName IS NULL)) AND (([ContactTitle] = @original_ContactTitle) OR ([ContactTitle] IS NULL AND @original_ContactTitle IS NULL)) AND (([Address] = @original_Address) OR ([Address] IS NULL AND @original_Address IS NULL)) AND (([City] = @original_City) OR ([City] IS NULL AND @original_City IS NULL)) AND (([Region] = @original_Region) OR ([Region] IS NULL AND @original_Region IS NULL)) AND (([PostalCode] = @original_PostalCode) OR ([PostalCode] IS NULL AND @original_PostalCode IS NULL)) AND (([Country] = @original_Country) OR ([Country] IS NULL AND @original_Country IS NULL)) AND (([Phone] = @original_Phone) OR ([Phone] IS NULL AND @original_Phone IS NULL)) AND (([Fax] = @original_Fax) OR ([Fax] IS NULL AND @original_Fax IS NULL))"> <DeleteParameters> <asp:Parameter Name="original_CustomerID" Type="String" /> <asp:Parameter Name="original_CompanyName" Type="String" /> <asp:Parameter Name="original_ContactName" Type="String" /> <asp:Parameter Name="original_ContactTitle" Type="String" /> <asp:Parameter Name="original_Address" Type="String" /> <asp:Parameter Name="original_City" Type="String" /> <asp:Parameter Name="original_Region" Type="String" /> <asp:Parameter Name="original_PostalCode" Type="String" /> <asp:Parameter Name="original_Country" Type="String" /> <asp:Parameter Name="original_Phone" Type="String" /> <asp:Parameter Name="original_Fax" Type="String" /> </DeleteParameters> <InsertParameters> <asp:Parameter Name="CustomerID" Type="String" /> <asp:Parameter Name="CompanyName" Type="String" /> <asp:Parameter Name="ContactName" Type="String" /> <asp:Parameter Name="ContactTitle" Type="String" /> <asp:Parameter Name="Address" Type="String" /> <asp:Parameter Name="City" Type="String" /> <asp:Parameter Name="Region" Type="String" /> <asp:Parameter Name="PostalCode" Type="String" /> <asp:Parameter Name="Country" Type="String" /> <asp:Parameter Name="Phone" Type="String" /> <asp:Parameter Name="Fax" Type="String" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="CompanyName" Type="String" /> <asp:Parameter Name="ContactName" Type="String" /> <asp:Parameter Name="ContactTitle" Type="String" /> <asp:Parameter Name="Address" Type="String" /> <asp:Parameter Name="City" Type="String" /> <asp:Parameter Name="Region" Type="String" /> <asp:Parameter Name="PostalCode" Type="String" /> <asp:Parameter Name="Country" Type="String" /> <asp:Parameter Name="Phone" Type="String" /> <asp:Parameter Name="Fax" Type="String" /> <asp:Parameter Name="original_CustomerID" Type="String" /> <asp:Parameter Name="original_CompanyName" Type="String" /> <asp:Parameter Name="original_ContactName" Type="String" /> <asp:Parameter Name="original_ContactTitle" Type="String" /> <asp:Parameter Name="original_Address" Type="String" /> <asp:Parameter Name="original_City" Type="String" /> <asp:Parameter Name="original_Region" Type="String" /> <asp:Parameter Name="original_PostalCode" Type="String" /> <asp:Parameter Name="original_Country" Type="String" /> <asp:Parameter Name="original_Phone" Type="String" /> <asp:Parameter Name="original_Fax" Type="String" /> </UpdateParameters> </asp:SqlDataSource> And Code Behind: protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.DataItem != null) { DataRowView drv = (DataRowView)e.Row.DataItem; // If row in edit mode, show the current country as selected in the list. if ((e.Row.RowState == DataControlRowState.Edit) || (e.Row.RowState == (DataControlRowState.Alternate | DataControlRowState.Edit))) { DropDownList ddl = (DropDownList)e.Row.Cells[9].FindControl("DropDownList1"); ListItem li = ddl.Items.FindByValue(drv["Country"].ToString()); li.Selected = true; } } } /// <summary> /// During update, save the currently selected country from the list of countries. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) { GridViewRow gvr = this.GridView1.Rows[this.GridView1.EditIndex]; DropDownList ddl = (DropDownList)gvr.Cells[9].FindControl("DropDownList1"); e.NewValues["Country"] = ddl.SelectedValue; }
So then I selected the GridView / Configure Data Source / Advanced Button / unchecked 'Use optimistic concurrency'.
Now updates work fine!
So it seems having the country column configured via a list is OK. But it breaks updates when using 'optimistic concurrency'. Am I doing something wrong?
Thanks for your time.
** Noticed during the GridView RowUpdating event handler, in the GridViewUpdateEventArgs, the collections in NewValues and OldValues no longer include the country!
Wednesday, November 20, 2013 2:14 PM
Answers
-
User-1427834641 posted
I found an answer. I enhanced the Gridview's EditTemplate for the 'country column' by adding an Asp Label bound to the 'Country' with Visible=False, like so:
<asp:TemplateField HeaderText="Country" SortExpression="Country"> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("Country") %>'/> </ItemTemplate> <EditItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("Country") %>' Visible="false"/> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="Country" DataValueField="Country"> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind2012ConnectionString %>" SelectCommand="SELECT DISTINCT [Country] FROM [Customers]"></asp:SqlDataSource> </EditItemTemplate> </asp:TemplateField>
Now 'Country' is being passed to the RowUpdating event in the GridViewUpdateEventArgs, and the user can select a country from a drop down list - works OK.
Q. Is this a good solution?
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, November 26, 2013 9:11 PM
All replies
-
User697462465 posted
Hi philb,
Enabling optimistic concurrency updates the WHERE clauses of the SqlDataSource control's UPDATE and DELETE statements to include parameters named @original_ColumnName
. For example, when creating a SqlDataSource control that returns the ProductID, ProductName, UnitPrice, and Discontinued fields from the Northwind Products database table, the following UPDATE statement is generated:
UPDATE [Products] SET [ProductName] = @ProductName, [UnitPrice] = @UnitPrice, [Discontinued] = @Discontinued WHERE [ProductID] = @original_ProductID AND [ProductName] = @original_ProductName AND [UnitPrice] = @original_UnitPrice AND [Discontinued] = @original_Discontinued
More information please refer to:
http://www.4guysfromrolla.com/articles/052108-1.aspxHope it can help you.
Best Regards,
Terry GuoFriday, November 22, 2013 2:08 AM -
User-1427834641 posted
Yes, but the WHERE clause does have all that. I see my code post was too huge to easily scroll and see, so I'll post again here:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:Northwind2012ConnectionString %>" DeleteCommand="DELETE FROM [Customers] WHERE [CustomerID] = @original_CustomerID AND [CompanyName] = @original_CompanyName AND (([ContactName] = @original_ContactName) OR ([ContactName] IS NULL AND @original_ContactName IS NULL)) AND (([ContactTitle] = @original_ContactTitle) OR ([ContactTitle] IS NULL AND @original_ContactTitle IS NULL)) AND (([Address] = @original_Address) OR ([Address] IS NULL AND @original_Address IS NULL)) AND (([City] = @original_City) OR ([City] IS NULL AND @original_City IS NULL)) AND (([Region] = @original_Region) OR ([Region] IS NULL AND @original_Region IS NULL)) AND (([PostalCode] = @original_PostalCode) OR ([PostalCode] IS NULL AND @original_PostalCode IS NULL)) AND (([Country] = @original_Country) OR ([Country] IS NULL AND @original_Country IS NULL)) AND (([Phone] = @original_Phone) OR ([Phone] IS NULL AND @original_Phone IS NULL)) AND (([Fax] = @original_Fax) OR ([Fax] IS NULL AND @original_Fax IS NULL))" InsertCommand="INSERT INTO [Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Customers]" UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE [CustomerID] = @original_CustomerID AND [CompanyName] = @original_CompanyName AND (([ContactName] = @original_ContactName) OR ([ContactName] IS NULL AND @original_ContactName IS NULL)) AND (([ContactTitle] = @original_ContactTitle) OR ([ContactTitle] IS NULL AND @original_ContactTitle IS NULL)) AND (([Address] = @original_Address) OR ([Address] IS NULL AND @original_Address IS NULL)) AND (([City] = @original_City) OR ([City] IS NULL AND @original_City IS NULL)) AND (([Region] = @original_Region) OR ([Region] IS NULL AND @original_Region IS NULL)) AND (([PostalCode] = @original_PostalCode) OR ([PostalCode] IS NULL AND @original_PostalCode IS NULL)) AND (([Country] = @original_Country) OR ([Country] IS NULL AND @original_Country IS NULL)) AND (([Phone] = @original_Phone) OR ([Phone] IS NULL AND @original_Phone IS NULL)) AND (([Fax] = @original_Fax) OR ([Fax] IS NULL AND @original_Fax IS NULL))">
And here are the SqlDataSource parameters:
<DeleteParameters> <asp:Parameter Name="original_CustomerID" Type="String" /> <asp:Parameter Name="original_CompanyName" Type="String" /> <asp:Parameter Name="original_ContactName" Type="String" /> <asp:Parameter Name="original_ContactTitle" Type="String" /> <asp:Parameter Name="original_Address" Type="String" /> <asp:Parameter Name="original_City" Type="String" /> <asp:Parameter Name="original_Region" Type="String" /> <asp:Parameter Name="original_PostalCode" Type="String" /> <asp:Parameter Name="original_Country" Type="String" /> <asp:Parameter Name="original_Phone" Type="String" /> <asp:Parameter Name="original_Fax" Type="String" /> </DeleteParameters> <InsertParameters> <asp:Parameter Name="CustomerID" Type="String" /> <asp:Parameter Name="CompanyName" Type="String" /> <asp:Parameter Name="ContactName" Type="String" /> <asp:Parameter Name="ContactTitle" Type="String" /> <asp:Parameter Name="Address" Type="String" /> <asp:Parameter Name="City" Type="String" /> <asp:Parameter Name="Region" Type="String" /> <asp:Parameter Name="PostalCode" Type="String" /> <asp:Parameter Name="Country" Type="String" /> <asp:Parameter Name="Phone" Type="String" /> <asp:Parameter Name="Fax" Type="String" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="CompanyName" Type="String" /> <asp:Parameter Name="ContactName" Type="String" /> <asp:Parameter Name="ContactTitle" Type="String" /> <asp:Parameter Name="Address" Type="String" /> <asp:Parameter Name="City" Type="String" /> <asp:Parameter Name="Region" Type="String" /> <asp:Parameter Name="PostalCode" Type="String" /> <asp:Parameter Name="Country" Type="String" /> <asp:Parameter Name="Phone" Type="String" /> <asp:Parameter Name="Fax" Type="String" /> <asp:Parameter Name="original_CustomerID" Type="String" /> <asp:Parameter Name="original_CompanyName" Type="String" /> <asp:Parameter Name="original_ContactName" Type="String" /> <asp:Parameter Name="original_ContactTitle" Type="String" /> <asp:Parameter Name="original_Address" Type="String" /> <asp:Parameter Name="original_City" Type="String" /> <asp:Parameter Name="original_Region" Type="String" /> <asp:Parameter Name="original_PostalCode" Type="String" /> <asp:Parameter Name="original_Country" Type="String" /> <asp:Parameter Name="original_Phone" Type="String" /> <asp:Parameter Name="original_Fax" Type="String" /> </UpdateParameters> </asp:SqlDataSource>
Again, this SqlDataSource does work with optimistic concurrency when the columns in GridView are all BoundFields, but when one GridView column is changed to be a TemplateField, then update is broken. So still need help on this. Thanks.Monday, November 25, 2013 1:36 PM -
User-1427834641 posted
Perhaps it is because the changed GridView column involves binding to a 2nd SqlDataSource for a list of countries:
<asp:TemplateField HeaderText="Country" SortExpression="Country"> <ItemTemplate> <%# Eval("Country") %> </ItemTemplate> <EditItemTemplate> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="Country" DataValueField="Country"> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind2012ConnectionString %>" SelectCommand="SELECT DISTINCT [Country] FROM [Customers]"></asp:SqlDataSource> </EditItemTemplate> </asp:TemplateField>
Then during the RowUpdating event, a handler tries to update the newly selected country, but the country is not in the
GridViewUpdateEventArgs NewValues (and OldValues) collection (during optimistic concurrency) per next:
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) { GridViewRow gvr = this.GridView1.Rows[this.GridView1.EditIndex]; DropDownList ddl = (DropDownList)gvr.Cells[9].FindControl("DropDownList1"); e.NewValues["Country"] = ddl.SelectedValue; }
Since the NewValues collection is missing the 'Country', then those values won't match the SqlDataSource update parameters, so I think that is why all updates fail. But I would like this confirmed. And, how would someone code a column to offer a list of countries while running with optimistic concurrency?
Monday, November 25, 2013 2:00 PM -
User-1427834641 posted
I found an answer. I enhanced the Gridview's EditTemplate for the 'country column' by adding an Asp Label bound to the 'Country' with Visible=False, like so:
<asp:TemplateField HeaderText="Country" SortExpression="Country"> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("Country") %>'/> </ItemTemplate> <EditItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("Country") %>' Visible="false"/> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2" DataTextField="Country" DataValueField="Country"> </asp:DropDownList> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind2012ConnectionString %>" SelectCommand="SELECT DISTINCT [Country] FROM [Customers]"></asp:SqlDataSource> </EditItemTemplate> </asp:TemplateField>
Now 'Country' is being passed to the RowUpdating event in the GridViewUpdateEventArgs, and the user can select a country from a drop down list - works OK.
Q. Is this a good solution?
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, November 26, 2013 9:11 PM -
User521424079 posted
Hi philb,
Thanks for your sharing, it help me much.
Regards.
Wednesday, November 27, 2013 2:01 AM