Click here to Skip to main content
15,887,683 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
C#
An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: Procedure or function spInsertFunction has too many arguments specified.


What I have tried:

Controller action method

C#
public ActionResult Create(FunctionByRole functionByRole)
       {
           FunByRoleFunctions obj = new FunByRoleFunctions();
           obj.AddFunctionByRole(functionByRole);

           return RedirectToAction("Index");
       }





Sql Store Procedure
SQL
create procedure [dbo].[spInsertFunction]
@RoleID int=0 output,
@FunctionID int
as
begin
insert into FunctionByRole values (@RoleID,@FunctionID)
set @RoleID=@@IDENTITY
end






Add Function

C#
public void AddFunctionByRole(FunctionByRole functionByRole)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ChequeVerificationContext"].ConnectionString;
            List<FunctionByRole> functionByRoles = new List<FunctionByRole>();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("[spInsertFunction]", con);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@FunByRoleID", SqlDbType.Int).Value = functionByRole.FunByRoleID;
                cmd.Parameters["@FunByRoleID"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add("@FunctionID", SqlDbType.Int).Value = functionByRole.FunctionID;
                cmd.Parameters["@FunctionID"].Direction = ParameterDirection.Input;


                cmd.Parameters.Add("@RoleID", SqlDbType.Int).Value = functionByRole.RoleID;
                cmd.Parameters["@RoleID"].Direction = ParameterDirection.Input;



                con.Open();
                cmd.ExecuteNonQuery();
                
            }
        }
Posted
Updated 30-Aug-16 4:30am
Comments
[no name] 30-Aug-16 10:22am    
Really? Read the error message. Look at your stored procedure and look at your function. Do you see any correlation at all between the error message, your stored procedure that takes 2 parameters and the function where you are calling the stored procedure with 3 parameters?

You have defined 2 parameters in your stored predure-
SQL
@RoleID int=0 output --output parameter
@FunctionID int --input parameter

But looking at the code, you are passing more parameters/values-
C#
cmd.Parameters.Add("@FunByRoleID", SqlDbType.Int).Value = functionByRole.FunByRoleID;
cmd.Parameters["@FunByRoleID"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@FunctionID", SqlDbType.Int).Value = functionByRole.FunctionID;
cmd.Parameters["@FunctionID"].Direction = ParameterDirection.Input;
cmd.Parameters.Add("@RoleID", SqlDbType.Int).Value = functionByRole.RoleID;
cmd.Parameters["@RoleID"].Direction = ParameterDirection.Input;

@FunByRoleID is not defined in the stored procedure and the error message clearly indicates that. From your code it looks like you don't need that paramter. So try removing that section from the code i.e, following lined of code-
C#
cmd.Parameters.Add("@FunByRoleID", SqlDbType.Int).Value = functionByRole.FunByRoleID;
cmd.Parameters["@FunByRoleID"].Direction = ParameterDirection.Output;


Or do the necessary changes and let me know if that helps or you still need help on this.

Thanks :)
 
Share this answer
 
v2
Look at your code:
SQL
create procedure [dbo].[spInsertFunction]
@RoleID int=0 output,
@FunctionID int
as
begin
Two parameters.
C#
SqlCommand cmd = new SqlCommand("[spInsertFunction]", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@FunByRoleID", SqlDbType.Int).Value = functionByRole.FunByRoleID;
cmd.Parameters["@FunByRoleID"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@FunctionID", SqlDbType.Int).Value = functionByRole.FunctionID;
cmd.Parameters["@FunctionID"].Direction = ParameterDirection.Input;
cmd.Parameters.Add("@RoleID", SqlDbType.Int).Value = functionByRole.RoleID;
cmd.Parameters["@RoleID"].Direction = ParameterDirection.Input;
Three parameters.
Then look at the error message:
Procedure or function spInsertFunction has too many arguments specified.
SQL assumes you have tried to call the wrong function, your function needs to be updated to match your code, or vice versa - and refuses to run it until you decide which change is necessary and make it.
 
Share this answer
 
Your stored procedure has two arguments; @RoleID and @FunctionID. Your code is passing three; @FunByRoleID, @RoleID and @FunctionID. That is why it is saying you are passing too many arguments, so don't pass @FunByRoleID.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900