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?

Are you sure this is about MySql? Using `SqlCommand` rather suggest to are targeting MS SQL Server. If so, change tag. — Filburt, Jul 11 '17 at 07:10
Since `@myQty` is a `bigint`, probably you need to set proper `SqlDbType`: `cmd.Parameters.Add(new SqlParameter("@myQty", SqlDbType.BigInt)).Value = 0`. — Tetsuya Yamamoto, Jul 11 '17 at 07:16
Speaking of which, that error confused me a bit. My item_qty is an int while my item_type is a tinyint but it said the former was a bigint and the latter was an int. — Mark Brown, Jul 11 '17 at 08:17
See here: https://stackoverflow.com/questions/5356862/sqlparameter-with-default-value-set-to-0-doesnt-work-as-expected — John Gilmer, Jul 15 '20 at 18:43

score 2 · Answer 1 · answered Jul 11 '17 at 07:15

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

Thanks for the answer and the explanation, it cleared up a lot. I've upped your answer as well although it doesn't publicly display since I'm a new user. — Mark Brown, Jul 11 '17 at 07:27

score 0 · Accepted Answer · answered Jul 11 '17 at 07:09

try to set the specific type to your parameter like here; Take a look at a database and set it according to the type which is set to the column.

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

var parameter = new SqlParameter()
parameter.ParameterName = "@myQty";
parameter.SqlDbType = SqlDbType.Int;
parameter.Direction = ParameterDirection.Input;
parameter.Value = 0;


cmd.Parameters.Add(parameter);
cmd.Parameters.Add(new SqlParameter("@myDate", dateNow));
cmd.Parameters.Add(new SqlParameter("@myType", 1));
Con.Open();
cmd.ExecuteNonQuery();
Con.Close();

Sources: List of types: https://msdn.microsoft.com/en-us/library/system.data.sqldbtype(v=vs.110).aspx Configuring query parameters: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/configuring-parameters-and-parameter-data-types

Hope it helps.

Thank you, it works now. Also, thanks for the link, I'll read up on it. I can accept the after 5 mins. — Mark Brown, Jul 11 '17 at 07:14
One liner: `cmd.Parameters.Add(new SqlParameter("@myQty", 0) { SqlDbType = SqlDbType.Int });` — Sinatr, Jul 11 '17 at 07:14
Unfortunately, it displays the same error, I did this thought and it worked: `cmd.Parameters.Add(new SqlParameter("@myQty", 0) { SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = 0 });`. Thanks as well though since it's because of you I tried experimenting how to get it to work with one line. — Mark Brown, Jul 11 '17 at 07:24

Can't insert 0 values using a Parameterized Query

2 Answers2