在执行存储过程时，将数据类型nvarchar转换为int类型的错误

I have a stored procedure that performs Create, Edit, Delete and Read operations.

我有一个执行创建、编辑、删除和读取操作的存储过程。

ALTER PROCEDURE [dbo].[CURDOpSP]    
@operation varchar(50), 
@regid int = 0,
@fname varchar(50) = null,
@lname varchar(50) = null,
@phone varchar(50) = null,
@email varchar(50) = null

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
if(@operation = 'Insert')
BEGIN
    insert into Register(fname,lname,phone,email,status)values(@fname,@lname,@phone,@email,0);
END

else if(@operation = 'Select')
BEGIN
    select * from Register where status = 0;
END

else if(@operation = 'Edit')
BEGIN
    update Register set fname= @fname, lname = @lname, phone = @phone, email = @email where RegisterId = @regid;

END

else if(@operation = 'Delete')
BEGIN
    Update Register set status = 1 where RegisterId = @regid;
END
END

And I have called this procedure on Controller.

我在控制器上调用了这个过程。

// GET: /Employee/Create
public ActionResult Create()
{
    return View();
}

// POST: /Employee/Create
[HttpPost]
public ActionResult Create(Register register)
{           
    if (ModelState.IsValid)
    {
        db.Database.ExecuteSqlCommand("dbo.CURDOpSP @p0, @p1, @p2, @p3, @p4", "Insert", register.fname, register.lname, register.phone, register.email);
        db.SaveChanges();
        return RedirectToAction("Index");
    }
    return View(register);   
}   

// POST: /Employee/Delete/5        
public ActionResult Delete(int id)
{
    Register register = db.Registers.Find(id);
    if (register == null)
    {
        return HttpNotFound();
    }           
    db.Database.ExecuteSqlCommand("dbo.CURDOpSP @p0, @p1", "Delete",register.RegisterId); 

    db.SaveChanges();
    return RedirectToAction("Index");
}

When I try to create a new record, I'm getting this error:

当我尝试创建一个新记录时，我得到了这个错误:

Error converting data type nvarchar to int.

将数据类型nvarchar转换为int的错误。

Source Error:

源错误:

Line 40: if (ModelState.IsValid)

40行:如果(ModelState.IsValid)

Line 41: {

41行:{

Line 42: db.Database.ExecuteSqlCommand("dbo.CURDOpSP @p0, @p1, @p2, @p3, @p4", "Insert", register.fname, register.lname, register.phone, register.email);

42:db.Database.ExecuteSqlCommand(“dbo。@p0， @p1， @p2， @p3， @p4"， "插入"，寄存器。帧,注册。lname,注册。电话,register.email);

Line 43: db.SaveChanges();

43行:db.SaveChanges();

Line 44: return RedirectToAction("Index");

44行:返回RedirectToAction(“指数”);

Other three operations are working fine.

其他三个操作运行良好。

If I place the parameter @regid in stored procedure, at the end of parameter declarations like:

如果我将参数@regid放在存储过程中，在参数声明的末尾，如:

ALTER PROCEDURE [dbo].[CURDOpSP]    
@operation varchar(50),     
@fname varchar(50) = null,
@lname varchar(50) = null,
@phone varchar(50) = null,
@email varchar(50) = null,
@regid int = 0

Then the creation will be fine. But now delete function will not work.

那么创造就会好起来。但是现在删除功能将不起作用。

1 个解决方案

#1

You need to pass other parameters as well when you call it in Delete method like this. I suppose the paramerter @regid is at end like you have posted in end.

在像这样的Delete方法中调用它时，还需要传递其他参数。我想paramerter @regid就像你最后发布的那样。

db.Database.ExecuteSqlCommand("dbo.CURDOpSP @p0, @p1,@p2,@p3@,@p4,@p5", "Delete","","","","",register.RegisterId);