ADO.NET converting numerical zero to NULL

[Marc Scheuner [MVP ADSI]]

I am seeing a funny effect with ADO.NET - or am I missing some
documented behavior here??

I have a stored proc that I need to call, and for that purpose, I
create an array of type SQLParameter[], and initialize it with
parameters and their values.

When I fill my parameters, everything seems to work just fine, except
when I need to fill an INT parameter with the value of 0 (zero).

Here's a sample:

SqlParameter[] oParams = new SqlParameter[15];

oParams[0] = new SqlParameter("@lSprachID", 1); // works fine
.......
oParams[9] = new SqlParameter("@lSomeValue", 0); // bombs out

The trouble is - instead of creating a parameter no. 9 with a value of
0 (zero), ADO.NET seems to convert this into a NULL parameter, and my
stored proc then complains that it doesn't allow NULL values in that
particular column.

If I chance it to

oParams[9] = new SqlParameter("@lSomeValue", SqlDbType.Int);
oParams[9].Value = 0;

and then call my stored proc, everything's just fine.

I don't get it - why would ADO.NET convert a parameter which I create
and initialize with a numerical value of "0" (zero) to a NULL
parameter?? Is that works-as-designed (and if so, WHERE is that
documented??). Is there anything I can do (except use the work around)
to change this behaviour??

Thanks!
Marc
================================================================
Marc Scheuner May The Source Be With You!
Bern, Switzerland m.scheuner(at)inova.ch

 

[Miha Markic [MVP C#]]

Hi Marc,

That one is tricky.
Straight from the docs:
Use caution when using 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, as the following C# example demonstrates.

Parameter = new SqlParameter("@pname", Convert.ToInt32(0));
If you do not perform this conversion, the compiler will assume you are
attempting to call the SqlParameter (string, SqlDbType) constructor
overload.

 

[Marc Scheuner [MVP ADSI]]

That one is tricky.

Straight from the docs:
Use caution when using 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, as the following C# example demonstrates.

Interesting, thank you very much !! This does sound like a bug, right?
I mean even if I specify an int value of 0, the compiler should still
be able to figure out that I want to call the overload which takes a
"value" (of type object) as its argument, not the other one........

Marc

================================================================
Marc Scheuner May The Source Be With You!
Bern, Switzerland m.scheuner(at)inova.ch

 

[Jon Skeet [C# MVP]]

Interesting, thank you very much !! This does sound like a bug, right?
I mean even if I specify an int value of 0, the compiler should still
be able to figure out that I want to call the overload which takes a
"value" (of type object) as its argument, not the other one........

No, because there's an implicit conversion from the literal 0 to
SqlDbType, and SqlDbType is more specific than object.