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#):

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

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

  1. Michael Wang says:

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

  2. Gabera says:

    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.

  3. nobody says:

    Thank you. Very useful, last comment also.

  4. Sampada Patil says:

    Thank you. very useful

  5. Pankaj Sharma says:

    It’s very useful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: