locked
SqlDataSource using optimistic concurrency won't persist updates RRS feed

  • 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.aspx

    Hope it can help you.

    Best Regards,
    Terry Guo

    Friday, 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