Exception Passing DBNull.Value to a Varbinary Using Parameters.AddWithValue

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.

Be Sociable, Share!
This entry was posted in C#. Bookmark the permalink.

One Response to Exception Passing DBNull.Value to a Varbinary Using Parameters.AddWithValue

  1. Michael Wang says:

    Thanks a lot!!! This post saved my life…

Leave a Reply

Your email address will not be published. Required fields are marked *