Procedure or function has too many arguments specified

19,123

I've ran into this error message before when I was calling a stored procedure from within a loop (foreach).

The cause of the exception that I received was due to the fact that I wasn't clearing the Parameter's after each iteration of the loop.

For example: This code throws:

using (SqlCommand cmd = new SqlCommand())
{
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.CommandText = "MySproc";
    foreach (MyClass c in myClasses)
    {
        cmd.Parameters.AddWithValue("@val1", c.val1);
        cmd.Parameters.AddWithValue("@val2", c.val2);
        var result = MyDbManager.instance.ExecuteScalarQuery(cmd);
    }
}

The fix was to clear the parameters:

using (SqlCommand cmd = new SqlCommand())
{
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.CommandText = "MySproc";
    foreach (MyClass c in myClasses)
    {
        cmd.Parameters.AddWithValue("@val1", c.val1);
        cmd.Parameters.AddWithValue("@val2", c.val2);
        var result = MyDbManager.instance.ExecuteScalarQuery(cmd);
        cmd.Parameters.Clear();
    }
}
Share:
19,123
Ye Myat Aung
Author by

Ye Myat Aung

A n00b with many questions. Interested in Web Development & Photography.

Updated on June 24, 2022

Comments

  • Ye Myat Aung
    Ye Myat Aung almost 2 years

    I get an error

    Procedure or function "myStoreProcNameHere" has too many arguments specified.

    Below is my stored procedure and SqlDataSource. I'm using a GridView to edit data.

    Help? :(

    Stored procedure:

    ALTER PROCEDURE UpdateTwoTables 
        (@ID int, 
         @UserID varchar(10), 
         @Pass varchar(50), 
         @Enabled int, 
         @Permission int,
         @Rank int,
         @FName varchar(50),
         @LName varchar(50),
         @Phone varchar(50),
         @Email1 varchar(50),
         @Email2 varchar(50)
        ) 
    AS
    BEGIN TRANSACTION
        UPDATE tbl_user_login 
        SET UserID = @UserID, Pass = @Pass, 
            Enabled = @Enabled, Permission = @Permission, 
            Rank = @Rank 
        WHERE ID = @ID
    
        IF @@ERROR <> 0
        BEGIN
            ROLLBACK 
            RETURN
        END
    
        UPDATE tbl_user_profile 
        SET FName = @FName, LName = @LName, 
            Phone = @Phone, Email1 = @Email1, Email2 = @Email2 
        WHERE ID = @ID
    
        IF @@ERROR <> 0
        BEGIN
            ROLLBACK
            RETURN
        END
    
        COMMIT
    

    ASP.NET SqlDataSource:

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:DBConnString %>" 
            SelectCommand="SELECT tbl_user_login.ID, tbl_user_login.UserID, tbl_user_login.Pass, tbl_user_login.Enabled, tbl_user_login.Permission, tbl_user_login.Rank, tbl_user_profile.ID AS Expr1, tbl_user_profile.FName,
    tbl_user_profile.LName, tbl_user_profile.Phone, tbl_user_profile.Email1, tbl_user_profile.Email2 FROM tbl_user_login INNER JOIN tbl_user_profile ON tbl_user_login.ID = tbl_user_profile.ID" 
            UpdateCommand="UpdateTwoTable" UpdateCommandType="StoredProcedure">
            <UpdateParameters>
                <asp:Parameter Name="ID" />
                <asp:Parameter Name="UserID"/>
                <asp:Parameter Name="Pass"/>
                <asp:Parameter Name="Enabled"/>
                <asp:Parameter Name="Permission"/>
                <asp:Parameter Name="Rank"/>
                <asp:Parameter Name="FName"/>
                <asp:Parameter Name="LName"/>
                <asp:Parameter Name="Phone"/>
                <asp:Parameter Name="Email1"/>
                <asp:Parameter Name="Email2"/>
            </UpdateParameters>        
        </asp:SqlDataSource>
    
  • Macromika
    Macromika about 9 years
    Thanks, man! This problem has been KILLING ME!! I couldn't figure out why the first loop worked but none of the others did.