C# 存储过程或函数需要未提供的参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14640094/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Stored procedure or function expects parameter which was not supplied
提问by Pritam
I am trying to insert data into a SQL Server database by calling a stored procedure, but I am getting the error
我正在尝试通过调用存储过程将数据插入 SQL Server 数据库，但出现错误
*Procedure or function 'Insertion' expects parameter '@Emp_no', which was not supplied*
*过程或函数“插入”需要未提供的参数“@Emp_no”*
My stored procedure is called Insertion. I have checked it thoroughly and no parameters is missing also I have checked it by using a label. The label shows the value but I don't know why I am getting the error.
我的存储过程被称为Insertion. 我已经彻底检查过了，没有丢失任何参数，我也使用标签进行了检查。标签显示了该值，但我不知道为什么会收到错误消息。
My code is
我的代码是
try
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.Clear();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Insertion";
cmd.Connection = con;
if (rdb_Male.Checked)
{
int @Emp_no = Convert.ToInt32(txtbx_Empno.Text);
string @Emp_name = txtbx_Emp_Name.Text;
double @phone = Convert.ToDouble(txtbx_Phone.Text);
string @Email = txtbx_Email.Text;
string @Password = txtbx_Pwd.Text;
string @Gender = rdb_Male.Text;
DateTime @Dob = Convert.ToDateTime(dob);
string @Address = txtbx_Address.Text;
string @Designation = txtbx_Designation.Text;
string @Qualification = txtbx_Qual.Text;
double @Experience = Convert.ToDouble(txtbx_Exp.Text);
double @Salary = Convert.ToDouble(txtbx_Sal.Text);
DateTime @Doj = Convert.ToDateTime(doj);
}
else if (rdb_Female.Checked)
{
int @Emp_no = Convert.ToInt32(txtbx_Empno.Text);
string @Emp_name = txtbx_Emp_Name.Text;
double @phone = Convert.ToDouble(txtbx_Phone.Text);
string @Email = txtbx_Email.Text;
string @Password = txtbx_Pwd.Text;
string @Gender = rdb_Female.Text;
DateTime @Dob = Convert.ToDateTime(dob);
string @Address = txtbx_Address.Text;
string @Designation = txtbx_Designation.Text;
string @Qualification = txtbx_Qual.Text;
double @Experience = Convert.ToDouble(txtbx_Exp.Text);
double @Salary = Convert.ToDouble(txtbx_Sal.Text);
DateTime @Doj = Convert.ToDateTime(doj);
}
if (con.State==ConnectionState.Closed)
con.Open();
LABEL.Text = txtbx_Empno.Text;
cmd.ExecuteNonQuery();
lbl_Errormsg.Visible = true;
lbl_Errormsg.Text = "Record Inserted Successfully";
con.Close();
}
and the stored procedure is
和存储过程是
ALTER PROCEDURE dbo.Insertion
(
@Emp_no int,
@Emp_name varchar(30),
@phone numeric(10,0),
@Email varchar(30),
@Password varchar(10),
@Gender varchar(6),
@Dob date,
@Address varchar(100),
@Designation varchar(20),
@Qualification varchar(20),
@Experience numeric(4,2),
@Salary numeric(10,2),
@Doj date
)
AS
Begin
Insert into Register (Emp_no, Emp_name, phone, Email, Password, Gender, Dob, Address, Designation, Qualification, Experience, Salary, Doj)
Values(@Emp_no, @Emp_name, @phone, @Email, @Password, @Gender, @Dob, @Address, @Designation, @Qualification, @Experience, @Salary, @Doj)
End
Please help me. Thanks in advance.
请帮我。提前致谢。
采纳答案by Sumo
You need to use SqlCommand.Parameters.AddWithValue:
您需要使用SqlCommand.Parameters.AddWithValue：
cmd.Parameters.AddWithValue("@ParameterName", value);
or SqlCommand.Parameters.Addfor other data types:
或SqlCommand.Parameters.Add对于其他数据类型：
cmd.Parameters.Add("@ParameterName", SqlDbType.Int, 5);
cmd.Parameters["@ParameterName"].Value = value;
SqlCommand.Parameters.AddWithValuereplaces the ambiguous overload of Addthat took a string and object parameter. See MSDNfor more info.
SqlCommand.Parameters.AddWithValue替换了Add带字符串和对象参数的歧义重载。有关更多信息，请参阅MSDN。
回答by Aniket Inge
You need to use this:
你需要使用这个：
cmd.Parameters.AddWithValue("@Emp_no", @Emp_no);
cmd.Parameters.AddWithValue("@Emp_name", @Emp_name);
cmd.Parameters.AddWithValue("@phone", @phone);
cmd.Parameters.AddWithValue("@Email", @Email);
cmd.Parameters.AddWithValue("@Password", @Password);
cmd.Parameters.AddWithValue("@Gender", @Gender);
cmd.Parameters.AddWithValue("@Dob", @Dob);
cmd.Parameters.AddWithValue("@Address", @Address);
cmd.Parameters.AddWithValue("@Designation", @Designation);
cmd.Parameters.AddWithValue("@Experience", @Experience);
cmd.Parameters.AddWithValue("@Salary", @Salary);
cmd.Parameters.AddWithValue("@Doj", @Doj);
Otherwise, it will throw that exception for each of the parameters.
否则，它将为每个参数抛出该异常。
回答by sgeddes
Your Insertion stored procedure is expecting @Emp_no(along with about 15 other parameters). You cannot call the stored procedure without passing the parameters.
您需要插入存储过程@Emp_no（以及大约 15 个其他参数）。您不能在不传递参数的情况下调用存储过程。
Take a look at this site for reference:
看看这个网站以供参考：
Everywhere you're defining variables, use Parameters.AddWithValueinstead:
在您定义变量的任何地方，请Parameters.AddWithValue改用：
cmd.Parameters.AddWithValue("@Emp_no ", Convert.ToInt32(txtbx_Empno.Text));
回答by user3313809
"There's only one Add method that's obsoleted, the method that accepts a string for the parameter name and an object for the value. As you noted, you should call AddWithValue instead for this scenario."
“只有一个 Add 方法已过时，该方法接受参数名称的字符串和值的对象。正如您所指出的，对于这种情况，您应该调用 AddWithValue。”
Not all Parameter.Add methods are depreciated. How are you suppose to make an OUTPUT parameter? You have to use Parameter.Add for this.
并非所有 Parameter.Add 方法都已折旧。你想如何制作一个 OUTPUT 参数？为此，您必须使用 Parameter.Add。
回答by Morespurs
Just a headsup, it might save someone a lot of time soul searching. If you have followed the recommendation here, like using AddWithValue in order to pass a paramter on, and you have everything verified and yet you are still getting the error message "Not supplied", check whether you have set the CommandType property of the command object to CommandType.StoredProcedure.
只是一个提示，它可能会为某人节省很多时间进行自我反省。如果您遵循了此处的建议，例如使用 AddWithValue 来传递参数，并且您已验证所有内容，但仍然收到错误消息“未提供”，请检查您是否已设置命令对象的 CommandType 属性到 CommandType.StoredProcedure。
Not setting this property incurs the same message, believe me! Hope it helps someone.
不设置此属性会导致相同的消息，相信我！希望它可以帮助某人。
回答by Malick
For others : I just faced the same error because one of my parameters was null. We need to check for it such as :
对于其他人：我只是遇到了同样的错误，因为我的一个参数为空。我们需要检查它，例如：
command.Parameters.AddWithValue("Features", (object)productDb.Features ?? DBNull.Value);
回答by Sameer
This is how it can be done
这是如何做到的
using (var cmd = new SqlCommand("STORED_PROCEDURE_NAME", SqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PARAM_NAME", PARAM_VALUE);
}
Notice that AddWithValue, and CommandType.StoredProcedureboth are essential.
请注意AddWithValue， 和CommandType.StoredProcedure两者都是必不可少的。
回答by Fred Johnson
Same error message still these days scoping multiple problems - my issue was passing a null value to the parameter. The answer is to to
这些天仍然存在相同的错误消息，涵盖多个问题 - 我的问题是将空值传递给参数。答案是
parameter.Value = (object)yourParamVal ?? DBNULL.Value;
