Updating gridview with SqlDataSource in asp.net

Question

i want to update the records from the gridview using SqlDataSource, here is what i am doing.
below is my gridview markup

<asp:GridView ID="grdManageFaculties" runat="server" AllowPaging="True" AutoGenerateColumns="False"
        DataSourceID="LocalServerDataSource" AutoGenerateDeleteButton="true" AutoGenerateEditButton="true"
        Width="100%" OnRowUpdating="grdManageFaculties_RowUpdating">
        <Columns>
            <asp:TemplateField HeaderText="MANAGE">
                <ItemTemplate>
                    <asp:LinkButton ID="lnkEdit" runat="server" CommandName="Edit" Text="Edit"></asp:LinkButton>
                    <asp:LinkButton ID="lnkDelete" runat="server" Text="Delete"></asp:LinkButton>
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:LinkButton ID="lnkUpdate" runat="server" Text="Update"></asp:LinkButton>
                    <asp:LinkButton ID="lnkCancel" runat="server" Text="Cancel"></asp:LinkButton>
                </EditItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="NAME">
                <ItemTemplate>
                    <asp:Label ID="lblUserName" runat="server" Text='<%# Eval("UserName") %>'></asp:Label>
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:Label ID="lblEditUserName" runat="server" Text='<%# Eval("UserName") %>'></asp:Label>
                </EditItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="EMAIL">
                <ItemTemplate>
                    <asp:Label ID="lblEmail" runat="server" Text='<%# Eval("Email") %>'></asp:Label>
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:TextBox ID="txtEditEmail" runat="server" Text='<%# Bind("Email") %>'></asp:TextBox>
                </EditItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="MOBILE">
                <ItemTemplate>
                    <asp:Label ID="lblMobileNumber" runat="server" Text='<%# Eval("Mobile") %>'></asp:Label>
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:TextBox ID="txtEditMobileNumber" runat="server" Text='<%# Bind("Mobile") %>'></asp:TextBox>
                </EditItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="LOCKED">
                <ItemTemplate>
                    <asp:CheckBox ID="chkIsLocked" runat="server" Enabled="false" Checked='<%# Eval("Locked") %>' />
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:CheckBox ID="chkEditIsLocked" runat="server" Checked='<%# Bind("Locked") %>' />
                </EditItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="CREATED">
                <ItemTemplate>
                    <asp:Label ID="lblCreated" runat="server" Text='<%# Eval("Created") %>'></asp:Label>
                </ItemTemplate>
                <EditItemTemplate>
                    <asp:Label ID="lblEditCreated" runat="server" Text='<%# Eval("Created") %>'></asp:Label>
                </EditItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>

below is my markup for the SqlDataSource

<asp:SqlDataSource ID="LocalServerDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:SiteSqlServer %>"
        SelectCommand="users_GetAllUsers" SelectCommandType="StoredProcedure" UpdateCommand="users_UpdateFaculty" UpdateCommandType="StoredProcedure">
        <UpdateParameters>
            <asp:Parameter Name="EMAIL" Type="String" />
            <asp:Parameter Name="ISLOCKEDOUT" Type="Boolean" />
            <asp:Parameter Name="MOBILENUMBER" Type="Int64" />
            <asp:Parameter Name="USERNAME" Type="String" />
        </UpdateParameters>
    </asp:SqlDataSource>

below is my code-behind for the Row_Updating function

   protected void grdManageFaculties_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        try
        {
            TextBox email = grdManageFaculties.Rows[e.RowIndex].FindControl("txtEditEmail") as TextBox;
            Label username = grdManageFaculties.Rows[e.RowIndex].FindControl("lblEditUserName") as Label;
            CheckBox locked = grdManageFaculties.Rows[e.RowIndex].FindControl("chkEditIsLocked") as CheckBox;
            TextBox mobilenumber = grdManageFaculties.Rows[e.RowIndex].FindControl("txtEditMobileNumber") as TextBox;

            LocalServerDataSource.UpdateParameters["EMAIL"].DefaultValue = email.Text;
            LocalServerDataSource.UpdateParameters["ISLOCKEDOUT"].DefaultValue = locked.Checked.ToString();
            LocalServerDataSource.UpdateParameters["MOBILENUMBER"].DefaultValue = mobilenumber.Text;
            LocalServerDataSource.UpdateParameters["USERNAME"].DefaultValue = username.Text;
            LocalServerDataSource.Update();
        }
        catch { }
    }

below is my Stored Procedure for Update

ALTER PROCEDURE users_UpdateFaculty
    @EMAIL NVARCHAR(100),
    @ISLOCKEDOUT BIT,
    @MOBILENUMBER BIGINT,
    @USERNAME nvarchar(100)
AS
BEGIN
    UPDATE aspnet_Users SET MOBILENUMBER=@MOBILENUMBER where USERNAME=@USERNAME
    UPDATE ASPNET_MEMBERSHIP SET EMAIL = @EMAIL, LOWEREDEMAIL = LOWER(@EMAIL), ISLOCKEDOUT=@ISLOCKEDOUT WHERE USERID = (SELECT USERID FROM ASPNET_USERS WHERE USERNAME=@USERNAME)
END

my records in database is getting updated, but when i click on update button, i gets the below error:

Procedure or function users_UpdateFaculty has too many arguments specified.

Can anyone help me what could be causing this issue, i am using all the parameters properly.

Answer 1

I dont think your solution is OK, since I have to use the atribute OldValuesParameterFormatString="original_{0}" that means that I would have doble parameters, the ones with the original values and the ones with edited values. So there is no way to match the order.

I have 4 parameters, Im getting the right values for 2 of them, an null at the others.

I tried your solution and did not work. Thanks anyway.

Read this: The order of the parameters in the UpdateParameters collection might be important, depending on the ADO.NET provider. The System.Data.OleDb and System.Data.Odbc providers associate the parameters in the collection according to the order that the parameters appear in the parameterized SQL query. The System.Data.SqlClient provider, which is the default ADO.NET provider for the SqlDataSource control, associates the parameters in the collection by matching the name of the parameter with a placeholder alias in the SQL query. For more information about parameterized SQL queries and commands, see Using Parameters with the SqlDataSource Control.

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

Answer 2

Found the solution: The Select Columns and the Update Parameters should match in order to update using SqlDataSource, that means if you select(query or procedure) is returning 3 fields in gridview, then all of them should be the parameter for the update, you can miss out the actual updations in database if not required, but the <UpdateParameters> should have all the fields: say for example if below is my select query

SELECT USERNAME, MOBILENUMBER, EMAIL FROM USERS

then the update parameters should be

<UpdateParameters>
   <asp:Parameter Name="UserName" Type="String" />
   <asp:Parameter Name="MobileNumber" Type="Int64" />
   <asp:Parameter Name="Email" Type="String" />
<UpdateParameters>

you cannot skip any of the parameters, even though you do not intend to update that field Hope this will help others, as i wasted lots of time researching on this.