In .Net it seems reasonable to try to pass a NULL to a SQL Server stored procedure using the following syntax (C#):
[sourcecode language=”csharp”]
myCommand.Parameters.AddWithValue(“@MyParameter”, DBNull.Value);
[/sourcecode]
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:
[sourcecode language=”csharp”]
myCommand.Parameters.Add(“@MyParameter”, SqlDbType.VarBinary, -1);
myCommand.Parameters[“@MyParameter”].Value = DBNull.Value;
[/sourcecode]
My thanks go to dnagelhout and Matt Neerincx for their invaluable posts on this subject.