C# Stored Procedure function expects parameter '@id' which was not supplied

Question

I'm getting this error and although I know it's been asked many many times, I tried to research my error and still nothing's work.

Is there any error to my code ? I hope someone would be able to help in this matter. I've been stuck on this for a week. I tried differnt opinions of other developer but still nothing solves.

Stored Procedure:

ALTER PROCEDURE dbo.AddProduct

@ID int,
@Image varbinary(max),
@Supplier varchar(50),
@Codeitem varchar(50),
@Itemdescription varchar(50),   
@Date varchar(50),
@Quantity varchar(50),
@Unitcost varchar(50),
@ADD nvarchar(50)

AS
BEGIN
        IF @ADD = 'Insert'
INSERT INTO  dbo.employee_product
(
Image,
Supplier,
Codeitem,
Itemdescription,
Date,
Quantity,
Unitcost    
)
VALUES
(
@Image,
@Supplier,
@Codeitem,
@Itemdescription,
@Date,
@Quantity,
@Unitcost   
)

END

My Code:

        byte[] image = null;
        var stream = new FileStream(this.txt_path.Text, FileMode.Open, FileAccess.Read);
        var read = new BinaryReader(stream);
        image = read.ReadBytes((int)stream.Length);
        using (var con = SQLConnection.GetConnection())
        {


            if (string.IsNullOrEmpty(cbox_supplier.Text) || string.IsNullOrEmpty(txt_code.Text) || string.IsNullOrEmpty(txt_item.Text) || string.IsNullOrEmpty(txt_quantity.Text) || string.IsNullOrEmpty(txt_cost.Text))
            {
                MetroMessageBox.Show(this, "Please input the Required Fields", "System Message:", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
            else
            {
                var selectCommand = new SqlCommand("AddProduct", con);
                selectCommand.CommandType = CommandType.StoredProcedure;
                selectCommand.Parameters.AddWithValue("@ADD", "Insert");
                selectCommand.Parameters.AddWithValue("@ID",SqlDbType.Int ).Value = 0;
                selectCommand.Parameters.AddWithValue("@Image", image);
                selectCommand.Parameters.AddWithValue("@Supplier", cbox_supplier.Text);
                selectCommand.Parameters.AddWithValue("@Supplier", cbox_supplier.Text.Trim());
                selectCommand.Parameters.AddWithValue("@Codeitem", txt_code.Text.Trim());
                selectCommand.Parameters.AddWithValue("@Itemdescription", txt_item.Text.Trim());
                selectCommand.Parameters.AddWithValue("@Date", txt_date.Text.Trim());
                selectCommand.Parameters.AddWithValue("@Quantity", txt_quantity.Text.Trim());
                selectCommand.Parameters.AddWithValue("@Unitcost", txt_cost.Text.Trim());
                selectCommand.ExecuteNonQuery();
                MessageBox.Show("Added successfully", "SIMS", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);
                _view._product();

            }

        }

Why does the stored procedure even take the `@ID` parameter? It is not used. — Crowcoder, Aug 21 '18 at 15:36
@Miracle are you asking me? My guess is no, you do not have to insert it but I don't know your table definition. I'm just saying you do not use the parameter so why is it there? — Crowcoder, Aug 21 '18 at 15:40
You should read this. https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type You have a number of datatypes that are seem like a poor choice here. — Sean Lange, Aug 21 '18 at 15:45
Another thing to try is the square brackets around the Date field. Date is a reserved keyword and you should always put square brackets around these words (albeit the error message should be "Syntax error") — Steve, Aug 21 '18 at 15:45
As @SeanLange says. You have passed all strings even for fields that seems to expect Dates or Numeric values. AddWithValue is famous to wreak havoc in this context [Can we stop using AddWithValue already](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — Steve, Aug 21 '18 at 15:49
@Steve but since they are using stored procs the AddWithValue doesn't have to guess the types. I still prefer to be explicit though. :) — Sean Lange, Aug 21 '18 at 15:51
@Miracle thank you for posting the code. It helps because we can see it is still wrong. Putting the SqlDbType in the second parameter is not right when using AddWithValue. Do it like Philip showed you. — Crowcoder, Aug 21 '18 at 15:53
Possible duplicate of [SqlCommand Parameters Add vs. AddWithValue](https://stackoverflow.com/questions/21110001/sqlcommand-parameters-add-vs-addwithvalue) — Crowcoder, Aug 21 '18 at 15:56
@Miracle why on earth you showed us a piece of code that has nothing to do with the image posted. In that image there is no parameter added to the DataAdapter SelectCommand. — Steve, Aug 21 '18 at 15:59
But the error happens as showed in the previous image now removed, not in the code posted. This makes all this discussion useless. This is the link to the original image https://i.stack.imgur.com/dXKfL.png — Steve, Aug 21 '18 at 16:20

Philip Smith · Answer 1 · 2018-08-21T15:39:45.660

3

Reading an answer to another question it states:

Use caution when you are using this overload of the SqlParameterCollection.Add method to specify integer parameter values. Because this overload takes a value of type Object, you must convert the integral value to an Object type when the value is zero, as the following C# example demonstrates.

parameters.Add("@pname", Convert.ToInt32(0));

If you do not perform this conversion, the compiler assumes that you are trying to call the SqlParameterCollection.Add (string, SqlDbType) overload.

This is most likely your issue.

edited Aug 21 '18 at 15:39

answered Aug 21 '18 at 15:37

Philip Smith

2,741
25
32

But i already assign the data type as int and already incremented it. Still need to convert it as integer ? – Aug 21 '18 at 15:39
Not sure if AddWithValue has the same problem. In any case better to call _Add(name, type).Value = 0;_ – Steve Aug 21 '18 at 15:40
1

Only one way to find out. – Philip Smith Aug 21 '18 at 15:40
I tried your answer but it didn't work – Aug 21 '18 at 15:41
@Miracle can you edit your question to show your updated code so we know exactly what you tried? – Crowcoder Aug 21 '18 at 15:43
Try assigning the zero to an `int` variable and passing that to your `AddWithValue` call. .NET may be seeing the zero as fitting a smaller integral type. – Philip Smith Aug 21 '18 at 15:44
@Crowcoder I don't think i need to rephrase my question sir , because my error is still that – Aug 21 '18 at 15:46
@Miracle just your code if it is different than what you originally posted. – Crowcoder Aug 21 '18 at 15:46
@Crowcoder selectCommand.Parameters.AddWithValue("@ID",SqlDbType.Int ).Value = 0; i change it but still getting this error – Aug 21 '18 at 15:52
1

@Miracle That is wrong. Change the method name to `Add` not `AddWithValue` – Philip Smith Aug 21 '18 at 15:54
sorry about that. already correct it but still nothing changed – Aug 21 '18 at 15:57

score 0 · Answer 2 · edited Aug 21 '18 at 15:35

You're adding ten parameters in your C# code here:

var selectCommand = new SqlCommand("AddProduct", con);
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@ADD", "Insert");
selectCommand.Parameters.AddWithValue("@ID",0);
selectCommand.Parameters.AddWithValue("@Image", image);
selectCommand.Parameters.AddWithValue("@Supplier", cbox_supplier.Text);
selectCommand.Parameters.AddWithValue("@Supplier", cbox_supplier.Text.Trim()); // <== duplicate?
selectCommand.Parameters.AddWithValue("@Codeitem", txt_code.Text.Trim());
selectCommand.Parameters.AddWithValue("@Itemdescription", txt_item.Text.Trim());
selectCommand.Parameters.AddWithValue("@Date", txt_date.Text.Trim());
selectCommand.Parameters.AddWithValue("@Quantity", txt_quantity.Text.Trim());
selectCommand.Parameters.AddWithValue("@Unitcost", txt_cost.Text.Trim());

But your procedure only takes 9 parameters, I think you might have duplicated the Supplier parameter?

Already remove it sir, sorry for that but still getting error — , Aug 21 '18 at 15:32
can you run SQL Profiler trace to see what is actually being sent to the server? — user1443098, Aug 21 '18 at 15:33

C# Stored Procedure function expects parameter '@id' which was not supplied

2 Answers2