我只想在数据库中插入一条记录，但它无法正常工作。我应该如何改变？ [英] I just want to insert a record into database and it is not working. How changes should I make?

问题描述

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

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

           return RedirectToAction("Index");
       }

Sql Store程序

Sql Store Procedure

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

添加功能

Add Function

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();
                
            }
        }

推荐答案

您已经在存储的预测中定义了2个参数 -

You have defined 2 parameters in your stored predure-

@RoleID int=0 output --output parameter
@FunctionID int --input parameter

但是查看代码，你传递的参数/值更多 -

But looking at the code, you are passing more parameters/values-

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 未在存储过程中定义，错误消息清楚地表明了这一点。从您的代码看起来您​​不需要那个参数。因此，请尝试从代码中删除该部分，即下面的代码 -

@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-

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 :)

查看代码：

Look at your code:

create procedure [dbo].[spInsertFunction]
@RoleID int=0 output,
@FunctionID int
as
begin

两个参数。

Two parameters.

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假设您尝试调用错误的函数，您的函数需要更新以匹配您的代码，反之亦然 - 并且在您决定需要进行哪些更改之前拒绝运行它并使它成为。

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.

您的存储过程有两个参数; @RoleID和@FunctionID。你的代码通过三个; @FunByRoleID，@ RoleID和@FunctionID。这就是为什么它说你传递了太多的参数，所以不要传递@FunByRoleID。

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.

这篇关于我只想在数据库中插入一条记录，但它无法正常工作。我应该如何改变？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！