In .Net it seems reasonable to try to pass a NULL to a SQL Server stored procedure using the following syntax (C#):
myCommand.Parameters.AddWithValue("@MyParameter", DBNull.Value);
Unfortunately, if the sproc is expecting a parameter of type Varbinary(max), the following exception will be thrown:
Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.
This appears to be a bug in the .Net SqlDataAdapter.
The work-around is to be explicit about the type of data you are passing:
myCommand.Parameters.Add("@MyParameter", SqlDbType.VarBinary, -1);
myCommand.Parameters["@MyParameter"].Value = DBNull.Value;
My thanks go to dnagelhout and Matt Neerincx for their invaluable posts on this subject.
Advertisements
Thanks a lot!!! This post saved my life…
There’s another way to do this. You can still use AddWithValue, however use SqlBinary.Null instead of DBNull.Value:
c.Parameters.AddWithValue(“@cfp”, SqlBinary.Null);
Don’t forget to import System.Data.SqlTypes into your project.
Gabera, you are the man. Your trick works like a charm.
Thank you. Very useful, last comment also.
Thank you. very useful
It’s very useful.