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.

This exception happens when you don't specify the command type as well. Not applicable in your case though. — black, Sep 03 '13 at 12:24
It was applicable in MY case, so a big Thank you from me! :) — Sigurd Garshol, Feb 23 '17 at 13:03
If you need set null value, do not write simply `new SqlParamenter { ParamenterName = "@Foo" Value = null}`. This way, your app will send the parameter as defaultvalue. Insted null, you must provide `... Value = DBNull.Value` — Fer R, Aug 31 '18 at 22:28

score 75 · Answer 1 · answered Jul 04 '16 at 12:23

75

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.

answered Jul 04 '16 at 12:23

Muniro

1,989
3
11
9

16

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 – Simon Molloy Dec 12 '16 at 16:55
2

It the simple things in life that truly make us happy. #storedprocedure – Robert Barrueco Jun 12 '18 at 20:20
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. – Mathieu Guindon May 27 '19 at 20:57
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. – Muniro May 28 '19 at 21:16
after 3 hours, you were my saviour!!!, i had CommandType.Text instead of StoredProdc – AlejandroDG Apr 18 '20 at 00:53
Great tip! I ran into this issue and this post helped me quickly identify a missing line: cmd.CommandType = CommandType.StoredProcedure; – RocketMan Jun 15 '20 at 14:32
Glad this is helping so many of you, and thanks for leaving some nice comments! – Muniro Jun 16 '20 at 16:48
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) – RMDev Jun 21 '21 at 14:46
A God amongst men. Ty for this! – Destroigo Dec 17 '21 at 21:14

Sumo · Accepted Answer · 2013-02-01T05:29:02.840

17

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.

edited Feb 01 '13 at 05:29

answered Feb 01 '13 at 05:14

Sumo

4,066
23
40

No, AddWithValue replaces the Add overload that took string and object parameters due to ambiguity. You can still use Add for other purposes. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue.aspx – Sumo Feb 01 '13 at 05:17
http://stackoverflow.com/questions/9999751/difference-between-parameters-add-and-parameters-addwithvalue see this also – Aniket Inge Feb 01 '13 at 05:19
1

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. – Sumo Feb 01 '13 at 05:27

Malick · Answer 3 · 2020-09-10T19:51:13.973

10

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

edited Sep 10 '20 at 19:51

answered Jul 14 '17 at 12:59

Malick

6,252
2
46
59

score 3 · Answer 4 · answered Feb 01 '13 at 05:09

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.

Oh! I have forgotten to write this. Thanks. This has solved my problem. — Pritam, Feb 01 '13 at 05:13
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. — Jeff Reddy, Jul 18 '18 at 17:49

score 3 · Answer 5 · edited Feb 01 '13 at 05:15

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

edited Feb 01 '13 at 05:15

Aniket Inge

25,375
5
50
78

answered Feb 01 '13 at 05:12

sgeddes

62,311
6
61
83

Thanks. I have forgotten to add 'cmd.Parameters.AddWithValue("@Emp_no ", Convert.ToInt32(txtbx_Empno.Text));'. Now it's working fine. – Pritam Feb 01 '13 at 05:17

score 2 · Answer 6 · answered Jun 19 '18 at 18:51

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.

score 0 · Answer 7 · answered Feb 15 '14 at 16:14

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

score 0 · Answer 8 · edited Jun 11 '20 at 20:24

0

Same error message still these days scoping multiple problems - my issue was passing a null value to the parameter. The answer is to null check like so:

parameter.Value = (object)yourParamVal ?? DBNULL.Value;

edited Jun 11 '20 at 20:24

Jeremy Hodge

612
3
14

answered Apr 12 '19 at 12:52

Fred Johnson

2,539
3
26
52

score 0 · Answer 9 · answered Jul 07 '20 at 02:30

0

Avoid parameters that have no value with the parameter set being DBNull.value

answered Jul 07 '20 at 02:30

AgungCode.Com

677
6
9

Stored procedure or function expects parameter which was not supplied

9 Answers9

Linked