Stored procedure or function expects parameter whi

问题:

I am trying to insert data into a SQL Server database by calling a stored procedure, but I am getting the error

*Procedure or function 'Insertion' expects parameter '@Emp_no', which was not supplied*

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.

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.

回答1:

You need to use SqlCommand.Parameters.AddWithValue:

cmd.Parameters.AddWithValue("@ParameterName", value);

or SqlCommand.Parameters.Add for other data types:

cmd.Parameters.Add("@ParameterName", SqlDbType.Int, 5);
cmd.Parameters["@ParameterName"].Value = value;

SqlCommand.Parameters.AddWithValue replaces the ambiguous overload of Add that took a string and object parameter. See MSDN for more info.

回答2:

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.

Not setting this property incurs the same message, believe me! Hope it helps someone.

回答3:

Your Insertion stored procedure is expecting @Emp_no (along with about 15 other parameters). You cannot call the stored procedure without passing the parameters.

Take a look at this site for reference:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue.aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-2

Everywhere you're defining variables, use Parameters.AddWithValue instead:

cmd.Parameters.AddWithValue("@Emp_no ", Convert.ToInt32(txtbx_Empno.Text));

回答4:

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.

回答5:

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

回答6:

"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."

http://social.msdn.microsoft.com/Forums/en-US/15bb16a4-0cf1-4289-b677-3b9d98f09298/parametersaddwithvalue-output-parameter-in-adonet-2?forum=adodotnetdataproviders

Not all Parameter.Add methods are depreciated. How are you suppose to make an OUTPUT parameter? You have to use Parameter.Add for this.

回答7:

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.StoredProcedure both are essential.