Heinrich Moser
7/27/2007 6:26:00 PM
"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