ADO.NET converting numerical zero to NULL

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
Wed Sep 08 03:18:42 CDT 2004

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.


--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com



"Marc Scheuner [MVP ADSI]" <m.scheuner@inova.SPAMBEGONE.ch> wrote in message
news:8bdtj0t2m62tt7trlo6dln95nrld70lnjs@4ax.com...
>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

Marc
Wed Sep 08 07:30:02 CDT 2004

>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
Wed Sep 08 07:50:34 CDT 2004

Marc Scheuner [MVP ADSI] <m.scheuner@inova.SPAMBEGONE.ch> wrote:
> >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........

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

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too