locked
GridView with SqlDataSource: Update does not work RRS feed

  • Question

  • User931280660 posted

    I'm using the auto generated edit feature of GridView and the edit button appears, clicking it enters edit mode but making changes and clicking update does nothing. As far as I can tell OnRowEditing triggers but OnRowUpdating does not. INSERT, SELECT and DELETE all work perfectly. I have tested my UpdateCommand on the SQL database directly and it works as intended. 

    I have a feeling that it has something to do with GridView not having the correct Primary Key (DomainName), but I can't figure out why.

    <asp:SqlDataSource
                id="SqlDataSource1"
                runat="server"
                ConnectionString="<%$ ConnectionStrings:SQLconnect %>"
                SelectCommand="SELECT DomainName,InUse,DomainCost,TLD,Registrar,ExpirationDate,Status, 
                                        Privacy,ForwardingURL,Notes,DepartmentProgram,ProgramContact FROM DomainList"
                UpdateCommand="UPDATE DomainList SET InUse=@InUse WHERE DomainName=@DomainName"
                DeleteCommand="DELETE FROM DomainList WHERE DomainName=@DomainName"
                OnDeleted="OnDomainDeleted">
            </asp:SqlDataSource>
    
            <asp:GridView
                id="GridView1"
                runat="server"
                AutoGenerateColumns="false"
                DataKeyNames="DomainName"
                AutoGenerateEditButton="true"
                AutoGenerateDeleteButton="true"
                DataSourceID="SqlDataSource1"
                AllowSorting="true">
                <columns>
                    <asp:BoundField HeaderText="Domain Name" DataField="DomainName" ReadOnly="true" SortExpression="DomainName" />
                    <asp:BoundField HeaderText="In Use" DataField="InUse" SortExpression="InUse" />
                </columns>
            </asp:GridView>
    Sql Database:
      DomainName (PK, nchar(30), not null)
      InUse (nchar(3), null)
      DomainCost (smallmoney, null)
      ...etc...

    I've been struggling to get this work for days, any help would be greatly appreciated!

    Thursday, January 7, 2016 9:29 PM

Answers

All replies

  • User-1716253493 posted

    You need to declare parameters in SqlDataSource1 to pass values from DataKeys, BoundField, BIND("x")

        <asp:SqlDataSource
                id="SqlDataSource1"
                runat="server"
                ConnectionString="<%$ ConnectionStrings:SQLconnect %>"
                SelectCommand="SELECT DomainName,InUse,DomainCost,TLD,Registrar,ExpirationDate,Status, 
                                        Privacy,ForwardingURL,Notes,DepartmentProgram,ProgramContact FROM DomainList"
                UpdateCommand="UPDATE DomainList SET InUse=@InUse WHERE DomainName=@DomainName"
                DeleteCommand="DELETE FROM DomainList WHERE DomainName=@DomainName"
                OnDeleted="OnDomainDeleted">
            <DeleteParameters>
                <asp:Parameter Name="DomainName" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="InUse" />
                <asp:Parameter Name="DomainName" />
            </UpdateParameters>
            </asp:SqlDataSource>

    Friday, January 8, 2016 12:32 AM
  • User-986267747 posted

    Hi dhayes,

    I've been struggling to get this work for days, any help would be greatly appreciated!

    As oned_gk say, you need to declare parameters in SqlDataSource1 to pass values from DataKeys, BoundField, BIND("x").  I create a sample so that you could understannd it.

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GridViewDelete.aspx.cs" Inherits="GridViewSample.GridViewDelete" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
        <style>
            .hidden { display:none;}
        </style>
    </head>
    <body>
        <form id="form1" runat="server">       
            <asp:Button ID="Button5" runat="server" Text="Button" OnClick="Button5_Click" />
        <div>
             <asp:GridView ID="gvProducts" runat="server" AutoGenerateColumns="False" BackColor="White"  AllowSorting="true" 
                DataKeyNames="ProductID"
                BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" CellPadding="3"  OnRowDataBound="gvProducts_RowDataBound"
                DataSourceID="SqlDataSource1" Font-Size="10pt" AllowPaging="false" PageSize="6" OnPreRender="gvProducts_PreRender"  OnRowUpdating="gvProducts_RowUpdating">
                <FooterStyle BackColor="#B5C7DE" ForeColor="#4A3C8C"  />
                <Columns>
                      <asp:TemplateField>
                        <ItemTemplate>                      
                    
                            <asp:Label ID="Label1" runat="server" Text='<%# Bind("ProductID") %>'></asp:Label>                      
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="ProductName" SortExpression="ProductName">
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("ProductName") %>' onkeypress="return this.value.length<=10" ></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:TextBox ID="TextBox1" runat="server"  onkeypress="return this.value.length<=10" TextMode="MultiLine"  Text='<%# Eval ("ProductName").ToString ().Length > 10 ? Eval ("ProductName").ToString ().Substring (0, 10) : Eval("ProductName").ToString () %>'></asp:TextBox>
                          <%--  <asp:Label ID="Label2" runat="server" Text='<%# Bind("ProductName") %>'></asp:Label>--%>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="UnitPrice" SortExpression="UnitPrice" >
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("UnitPrice") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:TextBox ID="TextBox4" runat="server" ReadOnly="True" Text='<%# Bind("UnitPrice") %>'
                                Width="50px"></asp:TextBox>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="UnitsInStock" SortExpression="UnitsInStock">
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("UnitsInStock") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:TextBox ID="TextBox5" runat="server" ReadOnly="True" Text='<%# Bind("UnitsInStock") %>'
                                Width="50px"></asp:TextBox>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Discontinued" SortExpression="Discontinued">
                        <EditItemTemplate>
                            <asp:CheckBox ID="CheckBox1"  runat="server" Checked='<%# Bind("Discontinued") %>' />
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("Discontinued") %>'
                                Enabled="false" />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField ShowHeader="False">
                        <EditItemTemplate>
                            <asp:Button ID="Button1" runat="server" CausesValidation="True" CommandName="Update"
                                Text="Update" />&nbsp;<asp:Button ID="Button2" runat="server" CausesValidation="False"
                                    CommandName="Cancel" Text="Cancel" />
                        </EditItemTemplate>
                        <ControlStyle BackColor="#FFC0C0" />
                        <ItemTemplate>
                            <asp:Button ID="Button4" runat="server" CausesValidation="False" CommandName="Edit"
                                Text="Edit" OnClientClick="return confirm('Edit?')" />
                             <asp:Button ID="Button3" runat="server" CausesValidation="False" CommandName="Delete"
                                Text="Delete" OnClientClick="return confirm('Delete')" />
                             <asp:Button ID="Button6" runat="server" CausesValidation="False" CommandName="Add"
                                Text="Add" OnClientClick="return confirm('Delete')" />
                            
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
                <RowStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" />
                <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="#F7F7F7" />
                <PagerStyle BackColor="#E7E7FF" ForeColor="#4A3C8C" HorizontalAlign="Right" />
                <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
                <AlternatingRowStyle BackColor="#F7F7F7" />
            </asp:GridView>
        
        </div>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
                ConnectionString="<%$ ConnectionStrings:northwindConnectionString %>" 
                DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @original_ProductID AND [ProductName] = @original_ProductName AND [UnitPrice] = @original_UnitPrice AND [UnitsInStock] = @original_UnitsInStock AND [Discontinued] = @original_Discontinued"
                InsertCommand="INSERT INTO [Products] ([ProductName], [UnitPrice], [UnitsInStock], [Discontinued]) VALUES (@ProductName, @UnitPrice, @UnitsInStock, @Discontinued)"
                OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [ProductID], [ProductName], [UnitPrice], [UnitsInStock], [Discontinued] FROM [Products]"
                UpdateCommand="UPDATE [Products] SET [ProductName] = @ProductName, [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, [Discontinued] = @Discontinued WHERE [ProductID] = @original_ProductID AND [ProductName] = @original_ProductName AND [UnitPrice] = @original_UnitPrice AND [UnitsInStock] = @original_UnitsInStock AND [Discontinued] = @original_Discontinued" OnInserting="SqlDataSource1_Inserting" OnUpdating="SqlDataSource1_Updating">
                <DeleteParameters>
                    <asp:Parameter Name="original_ProductID" Type="Int32" />
                    <asp:Parameter Name="original_ProductName" Type="String" />
                    <asp:Parameter Name="original_UnitPrice" Type="Decimal" />
                    <asp:Parameter Name="original_UnitsInStock" Type="Int16" />
                    <asp:Parameter Name="original_Discontinued" Type="Boolean" />
                </DeleteParameters>
                <UpdateParameters>
                    <asp:Parameter Name="ProductName" Type="String" />
                    <asp:Parameter Name="UnitPrice" Type="Decimal" />
                    <asp:Parameter Name="UnitsInStock" Type="Int16" />
                    <asp:Parameter Name="Discontinued" Type="Boolean" />
                    <asp:Parameter Name="original_ProductID" Type="Int32" />
                    <asp:Parameter Name="original_ProductName" Type="String" />
                    <asp:Parameter Name="original_UnitPrice" Type="Decimal" />
                    <asp:Parameter Name="original_UnitsInStock" Type="Int16" />
                    <asp:Parameter Name="original_Discontinued" Type="Boolean" />
                </UpdateParameters>
                <InsertParameters>
                    <asp:Parameter Name="ProductName" Type="String" />
                    <asp:Parameter Name="UnitPrice" Type="Decimal" />
                    <asp:Parameter Name="UnitsInStock" Type="Int16" />
                    <asp:Parameter Name="Discontinued" Type="Boolean" />
                </InsertParameters>
            </asp:SqlDataSource>
    
            
        </form>
    </body>
    </html>
    

    Besides, you could refer to the following code to learn crud operations in gridview.

    http://www.c-sharpcorner.com/UploadFile/9f0ae2/gridview-edit-delete-and-update-in-Asp-Net/

    http://www.aspsnippets.com/Articles/Simple-Insert-Select-Edit-Update-and-Delete-in-ASPNet-GridView-control.aspx

    I hopt it 's helpful to you.

    Best Regards,

    Klein zhang

    Friday, January 8, 2016 2:17 AM
  • User931280660 posted

    You need to declare parameters in SqlDataSource1 to pass values from DataKeys, BoundField, BIND("x")

    I have tried this twice now, just like your example, but it does not resolve the issue. According to MSDN: "The GridView automatically populates the UpdateParameters collection, inferring the parameters from theBoundField objects, and calls the Update method when the Update link on the editable GridView is selected."

    https://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.updatecommand(v=vs.110).aspx

    I create a sample so that you could understannd it.

    Thank you very much for creating this example. Unfortunately adding specific declarations for parameters doesn't resolve my issue and I have no need (right now) for custom formatting of the GridView.

    Friday, January 8, 2016 5:47 PM
  • User931280660 posted

    SOLVED: We figured it out at SA: http://stackoverflow.com/questions/34684477/gridview-with-sqldatasource-update-does-not-work

    I had an insert form with a validated field but the validation was affecting the entire page if that 1 field was ever blank. Setting the code below into the validated field and button resolved the issue.

    ValidationGroup="Insert"

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 8, 2016 10:34 PM