使用存储过程将ID参数从ASP.Net传递给SQL

Question

我正试图通过传递参数来调用SQL中的UPDATE存储过程，它需要在特定的ID值上更新。这是我不断得到的错误：

System.Data.SqlClient.SqlException: Procedure or function 'updateEmployeeRecord' expects parameter '@Id', which was not supplied.

堆栈跟踪；

[SqlException (0x80131904): Procedure or function 'updateEmployeeRecord' expects parameter '@Id', which was not supplied.]
EmployeeClass.Data.EmployeeList.EditEmpInfo(String connectionString, EmployeeList empInfo) in C:UsersnsamuelsDocumentsInduction ExercisesMasterPageTestExercise2EmployeeDataDataEmployeeClass.cs:120
EmployeeData.About.btnAdd_Click(Object sender, EventArgs e) in C:UsersnsamuelsDocumentsInduction ExercisesMasterPageTestExercise2EmployeeDataAbout.aspx.cs:80
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9774694
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +211
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +12
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +15
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1696

ASP.NET:中的代码

public void EditEmpInfo(string connectionString, EmployeeList empInfo)
{
try
{
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
string proc2 = "updateEmployeeRecord";
SqlCommand cmd = new SqlCommand(proc2, con);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@IDNUmber", empInfo.IDNumber));
cmd.Parameters.Add(new SqlParameter("@EmployeeNumber", empInfo.EmployeeNumber));
cmd.Parameters.Add(new SqlParameter("@EmployeeSurname", empInfo.employeeSurname));
cmd.Parameters.Add(new SqlParameter("@EmployeeName", empInfo.employeeName));
cmd.Parameters.Add(new SqlParameter("@NumOfDependants", empInfo.numberOfDependants));
cmd.Parameters.Add(new SqlParameter("@Employee_RaceId", empInfo.RaceId));
cmd.Parameters.Add(new SqlParameter("@Employee_GenderId", empInfo.GenderId));
cmd.ExecuteNonQuery();
con.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}

SQL存储过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[updateEmployeeRecord]
@Id int,
@EmployeeNumber varchar(50),
@IDNumber varchar(50),
@EmployeeSurname varchar(50),
@EmployeeName varchar(50),
@NumOfDependants int,
@Employee_RaceId int,
@Employee_GenderId int
AS
BEGIN
UPDATE dbo.Employees
SET
EmployeeNumber = @EmployeeNumber,
IDNumber = @IDNumber,
EmployeeSurname = @EmployeeSurname,
EmployeeName = @EmployeeName,
NumOfDependants = @NumOfDependants,
Employees_RaceId = @Employee_RaceId,
Employees_GenderId = @Employee_GenderId
WHERE Id =@Id
END

如果我添加下面的行，什么也没发生，它只是返回到Default.aspx。

cmd.Parameters.Add(new SqlParameter("Id", empInfo.id));

我需要它做的基本上是更新特定ID的数据库记录，即使它只有一个值。

编辑：我解决了问题。我不需要引用ID值。

Answer 1

您的参数不一致，因为您的所有其他参数都是"parmName"；，但是你的ID只是"；Id"；而不是"@Id"；。不知道是不是这样。然而，我一直讨厌参数名与表中的列名完全匹配。我通常会让我的参数名称以"@p〃；以指示其PARAMETER变量，而不仅仅是列的名称。防止阅读时出现歧义。所以，这是一个建议。

此外，存储过程可能会阻塞，因为您没有调用识别诸如之类的parm的过程

string proc2 = "updateEmployeeRecord( @Id, @EmployeeNumber, @IDNumber, @restOfParms )"; 

然后定义你的"@"parameters作为命令参数，以匹配updateEmployeeRecord from proc2字符串中的占位符。