Stored procedure or function expects parameter which was not supplied

Question

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.

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

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

Solution 3

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("@phone", (object)phone?? DBNull.Value);

Solution 4

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

Solution 5

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.

View more solutions

Comments

[Pritam]

Oh! I have forgotten to write this. Thanks. This has solved my problem.

[Pritam]

Thanks. I have forgotten to add 'cmd.Parameters.AddWithValue("@Emp_no ", Convert.ToInt32(txtbx_Empno.Text));'. Now it's working fine.

[Sumo]

No, AddWithValue replaces the Add overload that took string and object parameters due to ambiguity. You can still use Add for other purposes. msdn.microsoft.com/en-us/library/…

[Aniket Inge]

stackoverflow.com/questions/9999751/… see this also

[Sumo]

Again, it is for the overload of Add that takes string and object as parameters as if you pass an int or an enum for the 2nd parameter it doesn't know whether you are specifying the type or a value. It is still perfectly valid to use. Only the overload is deprecated. Add(Object), Add(SqlParameter), Add(String, SqlDbType, Int32), and Add(String, SqlType, Int32, String) are still valid.

[Simon Molloy]

Wish I could give this +10. Couldn't see anything wrong with the code and, much head scratching later, this is why. I did sort of know this but just forgot so this reminder is a HUGE time saver

[Robert Barrueco]

It the simple things in life that truly make us happy. #storedprocedure

[Jeff Reddy]

This should have been marked as the answer. The user was declaring variables in his code (that matched the name of his SQL Parameters), but he never used the variables or their underlying variables.

[Mathieu Guindon]

Too bad the author of this answer hasn't logged into Stack Overflow for over 3 years.... I was ready to give it a +100 bouty on the spot.

[Muniro]

Well, I am glad it helped someone. Sorry, my health got in the way of me, so never checked back until I got notified just now.

[AlejandroDG]

after 3 hours, you were my saviour!!!, i had CommandType.Text instead of StoredProdc

[RocketMan]

Great tip! I ran into this issue and this post helped me quickly identify a missing line: cmd.CommandType = CommandType.StoredProcedure;

[Muniro]

Glad this is helping so many of you, and thanks for leaving some nice comments!

[RMDev]

Thank you SO much, this worked for me, but once I changed it to be command.CommandType = CommandTye.StoredProcedure, then I had to change the sqlCommand to be just the SP name (without EXEC in the beginning of the command)

[Destroigo]

A God amongst men. Ty for this!