Command parameters, DBNull and SQL Server image field

Hi!

This code (using the System.Data.SqlClient namespace)...

SqlCommand c = myConnection.CreateCommand();
c.CommandText = "INSERT INTO myTable (myField) VALUES (@myParameter)";
c.Parameters.Add("@myParameter", myValue);
c.ExecuteNonQuery();

...usually works perfectly fine for all kinds of myFields and all
kinds of myValues, UNLESS

- myField is of (SQL Server) data type "image" AND
- myValue is DBNull.Value

In that case I get an SQL Server error message stating that nvarchar
is incompatible with image ("Operandentypkollision: nvarchar ist
inkompatibel mit image").

I think I understand what is happening behind the scenes: ADO.NET
cannot infer a useful data type from DBNull.Value so it assumes that
it's an nvarchar, which supports implicit conversion into a lot of
other data types, excluding (unfortunately) image.

Is there an easy solution to this problem? I need this for a library
function, i.e. the data type of myField is not known at run-time. Of
course, I could string-replace @myParameter with NULL if myValue is
DBNull.Value but that seems like a rather ugly workaround to me...

Greetings,
Heinzi

PS: I'm using .net 1.0/1.1.

Morten
Fri Jul 27 02:55:12 CDT 2007

On Fri, 27 Jul 2007 00:13:32 +0200, Heinrich Moser <usenet@heinzi.at> wr=
ote:

> Hi!
>
> This code (using the System.Data.SqlClient namespace)...
>
> SqlCommand c =3D myConnection.CreateCommand();
> c.CommandText =3D "INSERT INTO myTable (myField) VALUES (@myParameter)=
";
> c.Parameters.Add("@myParameter", myValue);
> c.ExecuteNonQuery();
>
> ...usually works perfectly fine for all kinds of myFields and all
> kinds of myValues, UNLESS
>
> - myField is of (SQL Server) data type "image" AND
> - myValue is DBNull.Value
>
> In that case I get an SQL Server error message stating that nvarchar
> is incompatible with image ("Operandentypkollision: nvarchar ist
> inkompatibel mit image").
>
> I think I understand what is happening behind the scenes: ADO.NET
> cannot infer a useful data type from DBNull.Value so it assumes that
> it's an nvarchar, which supports implicit conversion into a lot of
> other data types, excluding (unfortunately) image.
>
> Is there an easy solution to this problem? I need this for a library
> function, i.e. the data type of myField is not known at run-time. Of
> course, I could string-replace @myParameter with NULL if myValue is
> DBNull.Value but that seems like a rather ugly workaround to me...
>
> Greetings,
> Heinzi
>
> PS: I'm using .net 1.0/1.1.
>

Hi Heinzi,

Try specifying SqlDbType.Image as the parameter type. You might want to=
specify type for all field/value pairs that are nullable as filling it =
with a DBNull.Value doesn't tell the parameter what type it should be.

-- =

Happy coding!
Morten Wennevik [C# MVP]

Heinrich
Fri Jul 27 12:00:56 CDT 2007

Hi!

"Morten Wennevik [C# MVP]" <MortenWennevik@hotmail.com> writes:
> On Fri, 27 Jul 2007 00:13:32 +0200, Heinrich Moser <usenet@heinzi.at> wrote:
>> This code (using the System.Data.SqlClient namespace)...
>>
>> SqlCommand c = myConnection.CreateCommand();
>> c.CommandText = "INSERT INTO myTable (myField) VALUES (@myParameter)";
>> c.Parameters.Add("@myParameter", myValue);
>> c.ExecuteNonQuery();
>>
>> ...usually works perfectly fine for all kinds of myFields and all
>> kinds of myValues, UNLESS
>>
>> - myField is of (SQL Server) data type "image" AND
>> - myValue is DBNull.Value
>
> Try specifying SqlDbType.Image as the parameter type. You might
> want to specify type for all field/value pairs that are nullable as
> filling it with a DBNull.Value doesn't tell the parameter what type
> it should be.

Thank you for your answer. Of course, this is a valid solution, but it
does not work with the following requirement stated in my original
posting:

>> I need this for a library function, i.e. the data type of myField
>> is not known at run-time.

So, at this point of the code I don't know whether myField is "image"
or "nvarchar" or "bit" or whatever. Of course, I could force the
developers to pass the data types of the field all the way from the
application into my library, but I'd rather avoid this since this
information is usually not necessary (except for this one special
image/DBNull) case.

Technically, the information should not be needed by ADO.NET or SQL
Server: If myValue is not DBNull, the data type can be infered (which
works perfectly fine). If myValue is DBNull, the data type should not
matter, since all ADO.NET and SQL Server are supposed to do is to set
the field to NULL.

Greetings,
Heinzi

Morten
Fri Jul 27 12:32:37 CDT 2007

On Fri, 27 Jul 2007 19:00:56 +0200, Heinrich Moser <usenet@heinzi.at> wr=
ote:

> Hi!
>
> "Morten Wennevik [C# MVP]" <MortenWennevik@hotmail.com> writes:
>> On Fri, 27 Jul 2007 00:13:32 +0200, Heinrich Moser <usenet@heinzi.at>=
wrote:
>>> This code (using the System.Data.SqlClient namespace)...
>>>
>>> SqlCommand c =3D myConnection.CreateCommand();
>>> c.CommandText =3D "INSERT INTO myTable (myField) VALUES (@myParamete=
r)";
>>> c.Parameters.Add("@myParameter", myValue);
>>> c.ExecuteNonQuery();
>>>
>>> ...usually works perfectly fine for all kinds of myFields and all
>>> kinds of myValues, UNLESS
>>>
>>> - myField is of (SQL Server) data type "image" AND
>>> - myValue is DBNull.Value
>>
>> Try specifying SqlDbType.Image as the parameter type. You might
>> want to specify type for all field/value pairs that are nullable as
>> filling it with a DBNull.Value doesn't tell the parameter what type
>> it should be.
>
> Thank you for your answer. Of course, this is a valid solution, but it=

> does not work with the following requirement stated in my original
> posting:
>
>>> I need this for a library function, i.e. the data type of myField
>>> is not known at run-time.
>
> So, at this point of the code I don't know whether myField is "image"
> or "nvarchar" or "bit" or whatever. Of course, I could force the
> developers to pass the data types of the field all the way from the
> application into my library, but I'd rather avoid this since this
> information is usually not necessary (except for this one special
> image/DBNull) case.
>
> Technically, the information should not be needed by ADO.NET or SQL
> Server: If myValue is not DBNull, the data type can be infered (which
> works perfectly fine). If myValue is DBNull, the data type should not
> matter, since all ADO.NET and SQL Server are supposed to do is to set
> the field to NULL.
>
> Greetings,
> Heinzi
>

I see, the obvious solution to your problem is specifying the datatype. =
If the data type isn't known you could query the table and get it, and =
if the field is of type image, set the type on the parameter as well (or=
even better, set the type at all times).

I've seen some threads claiming to have solved this by setting the colum=
n's nullValue to "" or [], in which case nvarchar should be an acceptabl=
e type.

You could try googling yourself on the error message "Operand type clash=
: nvarchar is incompatible with image"

-- =

Happy coding!
Morten Wennevik [C# MVP]

Heinrich
Fri Jul 27 13:26:17 CDT 2007

"Morten Wennevik [C# MVP]" <MortenWennevik@hotmail.com> writes:
> On Fri, 27 Jul 2007 19:00:56 +0200, Heinrich Moser <usenet@heinzi.at> wrote:
>> "Morten Wennevik [C# MVP]" <MortenWennevik@hotmail.com> writes:
>>> On Fri, 27 Jul 2007 00:13:32 +0200, Heinrich Moser <usenet@heinzi.at> wrote:
>>>> This code (using the System.Data.SqlClient namespace)...
>>>>
>>>> SqlCommand c = myConnection.CreateCommand();
>>>> c.CommandText = "INSERT INTO myTable (myField) VALUES (@myParameter)";
>>>> c.Parameters.Add("@myParameter", myValue);
>>>> c.ExecuteNonQuery();
>>>>
>>>> ...usually works perfectly fine for all kinds of myFields and all
>>>> kinds of myValues, UNLESS
>>>>
>>>> - myField is of (SQL Server) data type "image" AND
>>>> - myValue is DBNull.Value
>>>
>>> Try specifying SqlDbType.Image as the parameter type. You might
>>> want to specify type for all field/value pairs that are nullable as
>>> filling it with a DBNull.Value doesn't tell the parameter what type
>>> it should be.
>>
>> Thank you for your answer. Of course, this is a valid solution, but it
>> does not work with the following requirement stated in my original
>> posting:
>>
>>>> I need this for a library function, i.e. the data type of myField
>>>> is not known at run-time.
>>
>> So, at this point of the code I don't know whether myField is "image"
>> or "nvarchar" or "bit" or whatever. Of course, I could force the
>> developers to pass the data types of the field all the way from the
>> application into my library, but I'd rather avoid this since this
>> information is usually not necessary (except for this one special
>> image/DBNull) case.
>>
>> Technically, the information should not be needed by ADO.NET or SQL
>> Server: If myValue is not DBNull, the data type can be infered (which
>> works perfectly fine). If myValue is DBNull, the data type should not
>> matter, since all ADO.NET and SQL Server are supposed to do is to set
>> the field to NULL.
>
> I see, the obvious solution to your problem is specifying the
> datatype. If the data type isn't known you could query the table
> and get it, and if the field is of type image, set the type on the
> parameter as well (or even better, set the type at all times).

Indeed, a very flexible idea. However, apart from the overhead of an
additional query this also requires more permissions in the database
(datareader and datawriter roles are not sufficient to query table
structures).

> I've seen some threads claiming to have solved this by setting the
> column's nullValue to "" or [], in which case nvarchar should be an
> acceptable type.

Thanks for the hint, I will investigate this further.

> You could try googling yourself on the error message "Operand type
> clash: nvarchar is incompatible with image"

Ah, great, thanks for the translation! Working with a localized
development system is a real pain if you want to google for help...

Greetings,
Heinzi

Heinrich
Fri Jul 27 15:54:58 CDT 2007

Hi!

Heinrich Moser <usenet@heinzi.at> writes:
> This code (using the System.Data.SqlClient namespace)...
>
> SqlCommand c = myConnection.CreateCommand();
> c.CommandText = "INSERT INTO myTable (myField) VALUES (@myParameter)";
> c.Parameters.Add("@myParameter", myValue);
> c.ExecuteNonQuery();
>
> ...usually works perfectly fine for all kinds of myFields and all
> kinds of myValues, UNLESS
>
> - myField is of (SQL Server) data type "image" AND
> - myValue is DBNull.Value
>
> In that case I get an SQL Server error message stating that nvarchar
> is incompatible with image ("Operandentypkollision: nvarchar ist
> inkompatibel mit image").
[...]
> Is there an easy solution to this problem? I need this for a library
> function, i.e. the data type of myField is not known at run-time. Of
> course, I could string-replace @myParameter with NULL if myValue is
> DBNull.Value but that seems like a rather ugly workaround to me...

I was able to solve this problem and would like to share the solution.

The problem was already identified:

> I think I understand what is happening behind the scenes: ADO.NET
> cannot infer a useful data type from DBNull.Value so it assumes that
> it's an nvarchar, which supports implicit conversion into a lot of
> other data types, excluding (unfortunately) image.

So the challenge was just finding an SQL data type that *can*
implicitly be converted to image (and the other important data types
of course).

In my research I stumbled upon the following chart (in the middle of
the page, Section "remarks"):
http://msdn2.microsoft.com/en-us/library/ms187928.aspx

It shows that nvarchar (the data type assumed by ADO.NET when passed a
DBNull parameter without an explicit type) does not implicitly convert
to image, *but varchar does*.

So, the solution is:

SqlCommand c = myConnection.CreateCommand();
c.CommandText = "INSERT INTO myTable (myField) VALUES (@myParameter)";
if (myValue.equals(DBNull.Value))
c.Parameters.Add("@myParameter", SqlDbType.VarChar).Value = DBNull.Value;
else
c.Parameters.Add("@myParameter", myValue);
c.ExecuteNonQuery();

Thank you Morten for your help!

Greetings,
Heinzi