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. — H H, Sep 13 '13 at 14:17
MySqlParameter is part of the MySql .Net API :http://dev.mysql.com/doc/refman/5.0/en/connector-net-ref-mysqlclient.html — Chris, Sep 13 '13 at 14:21
OK, that My* stuff. The we still need to see how MyParams gets pushed to the Command. — H H, Sep 13 '13 at 15:13

Sriram Sakthivel · Accepted Answer · 2013-09-13T15:30:40.387

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

Suraj Singh · Answer 2 · 2013-09-13T14:25:03.730

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 '13 at 14:25

answered Sep 13 '13 at 14:15

Suraj Singh

4,041
1
21
36

score 0 · Answer 3 · answered Sep 13 '13 at 13:53

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 '13 at 13:53

FlemGrem

814
4
9

MyId is part of a PrimaryKey of type INT. In the table schema the column is set to NOT NULL – neildt Sep 13 '13 at 13:56
1

But `0` is not `null`. – H H Sep 13 '13 at 14:05
^agree. Why would you want to insert 0 as a PK? what happens when you insert '1' instead? – FlemGrem Sep 13 '13 at 14:41
@AaronHopkins MyId is PART of a PrimaryKey, not a single column primary key – neildt Sep 13 '13 at 14:55
1

Your database, your rules. No worries. – FlemGrem Sep 13 '13 at 15:09

Why does MySqlParameter Add parameter as 0 convert to null

3 Answers3