Why does MySqlParameter Add parameter as 0 convert to null

Question

I'm adding a parameter to be called with a MySQL stored procedure like

List<MySqlParameter> MyParams = new List<MySqlParameter>();    
MyParams.Add(new MySqlParameter("MyId", 0));

But for some reason when I look at MyParams, MyId value when stepping through my code, it is converted to null. Does anyone know why this is because if I assign the value from a int variable like below it is fine

int id = 0;
List<MySqlParameter> MyParams = new List<MySqlParameter>(); 
MyParams.Add(new MySqlParameter("MyId", id));

Post the code for MySqlParameter, without it this question is unanswerable. — Henk Holterman
– Henk Holterman, Commented Sep 13, 2013 at 14:17
MySqlParameter is part of the MySql .Net API :dev.mysql.com/doc/refman/5.0/en/… — Chris
– Chris, Commented Sep 13, 2013 at 14:21
OK, that My* stuff. The we still need to see how MyParams gets pushed to the Command. — Henk Holterman
– Henk Holterman, Commented Sep 13, 2013 at 15:13

Sriram Sakthivel · Accepted Answer · 2013-09-13 15:30:40Z

10

Well, You fell into the corner case of c# that literal 0 can be converted to Enum implicitly

An implicit enumeration conversion permits the decimal-integer-literal 0 to be converted to any enum-type

Reference

So, new MySqlParameter("MyId", 0) is compiled into MySqlParameter(string,MySqlDbType) rather than MySqlParameter(string,object) as the result your value 0 is ignored.

new MySqlParameter("MyId", id) this works because implicit conversions to enum works only when the value is literal not for variables. So It is clear that this gets compiled into MySqlParameter(string,object) resulting the expected results.

new MySqlParameter("MyId", (object)0)//this solves the problem

or this

New MySqlParameter("MyId", MySqlDbType.Int).Value = 0

BTW as @Suraj Singh pointed you may have to use @MyId instead of MyId.

Hope this helps

edited Sep 13, 2013 at 15:30

answered Sep 13, 2013 at 15:23

Sriram Sakthivel

73.8k7 gold badges118 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Suraj Singh · Accepted Answer · 2013-09-13 14:25:03Z

2

Use caution when you use this overload of the SqlParameter constructor 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 ---MSDN Hope it's applicable for MySql too.

MyParams.Add(New MySqlParameter("@MyId", MySqlDbType.int)).Value = 0;

or try

Parameters.AddWithValue("@MyId", 0);

edited Sep 13, 2013 at 14:25

answered Sep 13, 2013 at 14:15

Suraj Singh

4,0871 gold badge23 silver badges37 bronze badges

Comments

FlemGrem · Accepted Answer · 2013-09-13 13:53:39Z

0

In your database schema, Is MyId a Primary Key of type int? with allow null set to yes?

assigning a value of 0 to a PK with allow null, will result in NULL being assigned. I suspect its the database setup and not the code at fault.

answered Sep 13, 2013 at 13:53

FlemGrem

8144 silver badges9 bronze badges

5 Comments

neildt Over a year ago

MyId is part of a PrimaryKey of type INT. In the table schema the column is set to NOT NULL

Henk Holterman Over a year ago

But 0 is not null.

FlemGrem Over a year ago

^agree. Why would you want to insert 0 as a PK? what happens when you insert '1' instead?

neildt Over a year ago

@AaronHopkins MyId is PART of a PrimaryKey, not a single column primary key

FlemGrem Over a year ago

Your database, your rules. No worries.

Collectives™ on Stack Overflow

Why does MySqlParameter Add parameter as 0 convert to null

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related