Command parameters, DBNull and SQL Server image field

[Heinrich Moser]

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 Wennevik [C# MVP]]

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.

Hi Heinzi,

Try specifying SqlDbType.Image as the parameter type. You might want tospecify 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.

 

[Heinrich Moser]

Hi!

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:

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 Wennevik [C# MVP]]

Hi!



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:

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 (oreven better, set the type at all times).

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.

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

 

[Heinrich Moser]

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 Moser]

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"). [...]
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