Null value in a parameter varbinary datatype

c# winforms sql-server-2008 parameters dbnull

25,486

Solution 1

You can try something like this:-

cmd.Parameters.Add( "@Image", SqlDbType.VarBinary, -1 );

cmd.Parameters["@Image"].Value = DBNull.Value;

Solution 2

I dont know the reason why "DBNull.Value" does not work for me. And I figure out another solution can solve this problem.

cmd.Parameters["@Image"].Value = System.Data.SqlTypes.SqlBinary.Null;

Solution 3

sqlCommand.Parameters.AddWithValue("@image", SqlBinary.Null);

Solution 4

try this :

mySqlCommand.Parameters.AddWithValue("@Image", new byte[]{});

Solution 5

i do it like this without a problem

SqlParameter image= new SqlParameter("@Image", SqlDbType.VarBinary, System.DBNull.Value);
mySqlCommand.Parameters.Add(image);

View more solutions

25,486

Author by

Karlx Swanovski

Updated on July 09, 2022

Comments

Karlx Swanovski almost 2 years

How can I add a null value in a parameter varbinary datatype?

When I execute the following code:

using (SqlConnection myDatabaseConnection1 = new SqlConnection(myConnectionString.ConnectionString))
{
    using (SqlCommand mySqlCommand = new SqlCommand("INSERT INTO Employee(EmpName, Image) Values(@EmpName, @Image)", myDatabaseConnection1))
    {
        mySqlCommand.Parameters.AddWithValue("@EmpName", textBoxEmpName.Text);
        mySqlCommand.Parameters.AddWithValue("@Image", DBNull.Value);
        myDatabaseConnection1.Open();
        mySqlCommand.ExecuteNonQuery();
    }
}

I get the following System.Data.SqlClient.SqlException:

Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

Karlx Swanovski almost 11 years

Thanks. What is -1 for?
Rahul Tripathi almost 11 years

It is the length and SqlDbType.VarBinary with length -1 is the equivalent of VARBINARY(MAX), at least in theory. ;)
Karlx Swanovski almost 11 years

How did you know that the datatype lenght is MAX?
Rahul Tripathi almost 11 years

I dont know...I just assumed!! ;)
Ronaldinho Learn Coding almost 9 years

Parameters.Add is deprecated, this answer maybe better, only thing to be noticed is that, it will not insert a NULL data (in the table you see the word NULL) but an EMPTY data (in the table you will see nothing)
Ronaldinho Learn Coding almost 9 years

Parameters.Add is deprecated, any better idea?
Ronaldinho Learn Coding almost 9 years

but Parameters.Add is deprecated
Eliseo almost 9 years

This is the best solution as stated by Ronaldinho, thanks for the answer Shamseer
Jobokai over 8 years

@RonaldinhoLearnCoding Use Parameters.AddWithValue
NathanOliver over 7 years

While this answer may answer the question adding some more detail like why and how this works will improve its quality
jason about 7 years

This worked for me and fit in with the helper method that was previously sending DBNull.Value if the byte[] value was null.
jocull about 7 years

As @RonaldinhoLearnCoding said, it's important to note that byte[0] is not the same as null.
Zeek2 over 4 years

Solved my problem ;)
Jan 'splite' K. almost 4 years

4 years later and -- This should be the accepted answer!
David Mancini about 3 years

SqlTypes.SqlBinary.Null for the win. Thank you, kind individual. Take my upvote.