locked
How to pass parameters into SqlDataSource on RowUpdating? RRS feed

  • Question

  • User716467530 posted

    I have a grid view that is bound to SqlDataSource.
    For update row I’m using stored procedure.
    When I’m updating a row I’m getting error: Procedure or function UpsertSiteMap has too many arguments specified.

     

    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
    e.NewValues["UserId"] = 123;
    }
    

     

        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" 
            SelectCommand="SELECT Id, Name, CreatedByUserId FROM [Products]" 
            UpdateCommand=”[UpdateProduct]" UpdateCommandType="StoredProcedure">
            <UpdateParameters>
                <asp:Parameter Name="Id" Type="Int32" />
                <asp:Parameter Name="Name" Type="String" />
                <asp:Parameter Name="UserId" Type="Object" />
            </UpdateParameters>
        </asp:SqlDataSource>
    
    

    Product table:
    Id int,
    Name varchar(50),
    CreatedByUserId int

    e.NewValues doesn't have "UserId". It has "Id", "Name" and "CreatedByUserId" keys

    How to configure asp:SqlDataSource using stored procedure with parameters different from table column names?

    Friday, April 8, 2011 1:50 AM

Answers

  • User16212438 posted

    SqlDataSource can't really be configured for this. Your solution using RowUpdating should work. (You can set e.NewValues["UserId"] = e.NewValues["CreatedByUserId"]) Although, you may have to remove CreatedByUserId as well. (Sql server will try to use all parameters it gets to fill all parameters of the Stored Procedure.)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 11, 2011 3:24 AM
  • User-1499637000 posted

    Hi

    If u want cinfigure the SqlDataSource using stored procedure with parameters different from table column names, then change the UpdateCommand Type to "text" and UpdateCommand to "EXEC sp_name @param1,@param2,@param3".

    Here is the example and srored procedure with the AdventureWorks as database:

    <%@ Page Language="C#" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <script runat="server">
    
        protected void grvEmployees_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            string employeeID = grvEmployees.DataKeys[e.RowIndex].Value.ToString();
    
            string gender = ((DropDownList)grvEmployees.Rows[e.RowIndex].FindControl("DropDownList1")).SelectedValue.ToString();
            e.NewValues["Gender"] = gender;
            e.NewValues["EmployeeID1"] = employeeID;
            empDataSource.Update();
        }
    </script>
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head id="Head1" runat="server">
        <title>Dropdownlist in EditItemTemplate</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:GridView ID="grvEmployees" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID"
                DataSourceID="empDataSource" AllowPaging="true" PageSize="10" AutoGenerateEditButton="True"
                OnRowUpdating="grvEmployees_RowUpdating">
                <Columns>
                    <asp:TemplateField HeaderText="EmployeeID" InsertVisible="False" SortExpression="EmployeeID">
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Eval("EmployeeID") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Eval("EmployeeID") %>'></asp:Label>
                        </EditItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Gender" SortExpression="Gender">
                        <ItemTemplate>
                            <asp:Label ID="Label2" runat="server" Text='<%# Eval("Gender") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:DropDownList ID="DropDownList1" runat="server" SelectedValue='<%# Eval("Gender") %>'>
                                <asp:ListItem Value="M">M</asp:ListItem>
                                <asp:ListItem Value="F">F</asp:ListItem>
                            </asp:DropDownList>
                        </EditItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
    
        </div>
        <asp:SqlDataSource ID="empDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>"
            SelectCommand="SELECT [EmployeeID], [Gender] FROM [AdventureWorks].[HumanResources].[Employee]"
            UpdateCommand="EXEC [HumanResources].[dbo.uspUpdateEmployeeGender] @EmployeeID1,@Gender"
            UpdateCommandType="Text">
            <UpdateParameters>
                <asp:Parameter Name="EmployeeID1"></asp:Parameter>
                <asp:Parameter Type="Char" Name="Gender"></asp:Parameter>
            </UpdateParameters>
        </asp:SqlDataSource>
        </form>
    </body>
    </html>
    

    StoredProcedure:

    Create PROCEDURE [HumanResources].[dbo.uspUpdateEmployeeGender]
    @EmployeeID1 [int], 
    @Gender [nchar](1) 
    AS
    BEGIN
    UPDATE [HumanResources].[Employee] 
    SET [Gender] = @Gender 
    WHERE [EmployeeID] = @EmployeeID1;
    END

    Hope the above solution helps.

    Thnaks...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 11, 2011 5:11 AM

All replies

  • User16212438 posted

    SqlDataSource can't really be configured for this. Your solution using RowUpdating should work. (You can set e.NewValues["UserId"] = e.NewValues["CreatedByUserId"]) Although, you may have to remove CreatedByUserId as well. (Sql server will try to use all parameters it gets to fill all parameters of the Stored Procedure.)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 11, 2011 3:24 AM
  • User-1499637000 posted

    Hi

    If u want cinfigure the SqlDataSource using stored procedure with parameters different from table column names, then change the UpdateCommand Type to "text" and UpdateCommand to "EXEC sp_name @param1,@param2,@param3".

    Here is the example and srored procedure with the AdventureWorks as database:

    <%@ Page Language="C#" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <script runat="server">
    
        protected void grvEmployees_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            string employeeID = grvEmployees.DataKeys[e.RowIndex].Value.ToString();
    
            string gender = ((DropDownList)grvEmployees.Rows[e.RowIndex].FindControl("DropDownList1")).SelectedValue.ToString();
            e.NewValues["Gender"] = gender;
            e.NewValues["EmployeeID1"] = employeeID;
            empDataSource.Update();
        }
    </script>
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head id="Head1" runat="server">
        <title>Dropdownlist in EditItemTemplate</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:GridView ID="grvEmployees" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID"
                DataSourceID="empDataSource" AllowPaging="true" PageSize="10" AutoGenerateEditButton="True"
                OnRowUpdating="grvEmployees_RowUpdating">
                <Columns>
                    <asp:TemplateField HeaderText="EmployeeID" InsertVisible="False" SortExpression="EmployeeID">
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Eval("EmployeeID") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Eval("EmployeeID") %>'></asp:Label>
                        </EditItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Gender" SortExpression="Gender">
                        <ItemTemplate>
                            <asp:Label ID="Label2" runat="server" Text='<%# Eval("Gender") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:DropDownList ID="DropDownList1" runat="server" SelectedValue='<%# Eval("Gender") %>'>
                                <asp:ListItem Value="M">M</asp:ListItem>
                                <asp:ListItem Value="F">F</asp:ListItem>
                            </asp:DropDownList>
                        </EditItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
    
        </div>
        <asp:SqlDataSource ID="empDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>"
            SelectCommand="SELECT [EmployeeID], [Gender] FROM [AdventureWorks].[HumanResources].[Employee]"
            UpdateCommand="EXEC [HumanResources].[dbo.uspUpdateEmployeeGender] @EmployeeID1,@Gender"
            UpdateCommandType="Text">
            <UpdateParameters>
                <asp:Parameter Name="EmployeeID1"></asp:Parameter>
                <asp:Parameter Type="Char" Name="Gender"></asp:Parameter>
            </UpdateParameters>
        </asp:SqlDataSource>
        </form>
    </body>
    </html>
    

    StoredProcedure:

    Create PROCEDURE [HumanResources].[dbo.uspUpdateEmployeeGender]
    @EmployeeID1 [int], 
    @Gender [nchar](1) 
    AS
    BEGIN
    UPDATE [HumanResources].[Employee] 
    SET [Gender] = @Gender 
    WHERE [EmployeeID] = @EmployeeID1;
    END

    Hope the above solution helps.

    Thnaks...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 11, 2011 5:11 AM