Can't insert 0 values using a Parameterized Query

Question

I have an inventory system and this code is for when a user creates a new item. It's supposed to insert a 0 value in the inventory table since it's a new item. My code is:

string queryAdd4 = "INSERT INTO [inventory]([item_id],[item_qty],[item_date],[item_type]) VALUES(@myID,@myQty,@myDate,@myType)";
using (SqlCommand cmd = new SqlCommand(queryAdd4, Con))
{
cmd.Parameters.Add(new SqlParameter("@myID", item_id));
cmd.Parameters.Add(new SqlParameter("@myQty", 0));
cmd.Parameters.Add(new SqlParameter("@myDate", dateNow));
cmd.Parameters.Add(new SqlParameter("@myType", 1));
Con.Open();
cmd.ExecuteNonQuery();
Con.Close();
}

With that code, i'm getting an error saying:

The parameterized query '(@myID int,@myQty bigint,@myDate datetime,@myType int)
INSERT INT' expects the parameter '@myQty', which was not supplied

Out of curiosity, I tried replacing the 0 beside the @myQty with 1 and the query worked without problems. I also tried manually running the query through the Server Explorer and that worked as well. So I'm guessing 0 is not a valid number to insert when using parameterized queries? If so, how would I go about doing it?

Answer 1

When using two parameters with SqlParameter Constructor, there are two choices:

SqlParameter(string parameterName, SqlDbType dbType)
SqlParameter(string parameterName, object value)

When using an integer, the first choice is used. If you want to use the two parameter constructor, you have to cast 0 to an object:

cmd.Parameters.Add(new SqlParameter("@myQty", (object)0));

Also regard the oneliner from Sinatr in the comments:

cmd.Parameters.Add(new SqlParameter("@myQty", 0) { SqlDbType = SqlDbType.Int });