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();
}
}
Author by
Ye Myat Aung
A n00b with many questions. Interested in Web Development & Photography.
Updated on June 24, 2022Comments
-
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 about 9 yearsThanks, man! This problem has been KILLING ME!! I couldn't figure out why the first loop worked but none of the others did.