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!

Answer 1

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

Answer 2

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.

Answer 3

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?

Answer 4

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?

Answer 5

User521424079 posted

Hi philb,

Thanks for your sharing, it help me much.

Regards.