Null value in a parameter varbinary datatype

Question

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.

Affiliation

I have a little detection strategy for dealing with null instances of byte[] cast as object on a related post: https://mcmap.net/q/822700/-using-dbnull-value-with-sqlparameter-without-knowing-sqldbtype – Conjugation

Answer 1

You can try something like this:-

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

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

Resolvable

Thanks. What is -1 for? – Affiliation

It is the length and SqlDbType.VarBinary with length -1 is the equivalent of VARBINARY(MAX), at least in theory. ;) – Resolvable

How did you know that the datatype lenght is MAX? – Affiliation

I dont know...I just assumed!! ;) – Resolvable

Parameters.Add is deprecated, any better idea? – Lowson

@RonaldinhoLearnCoding Use Parameters.AddWithValue – Precincts

Answer 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;

Gainsborough

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

4 years later and -- This should be the accepted answer! – Deemphasize

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

Answer 3

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

Lucubrate

While this answer may answer the question adding some more detail like why and how this works will improve its quality – Gastrectomy

Answer 4

try this :

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

Kyleekylen

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) – Lowson

This is the best solution as stated by Ronaldinho, thanks for the answer Shamseer – Jamboree

As @RonaldinhoLearnCoding said, it's important to note that byte[0] is not the same as null. – Conjugation

Answer 5

i do it like this without a problem

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

Woorali

but Parameters.Add is deprecated – Lowson

Answer 6

Set null value in your Stored Procedure. Doesn't need to do anything else.

ex. @photo varbinary(max) = null,

ALTER PROCEDURE [dbo].[InsertOurTeam]
    @name nvarchar(50),
    @Sname nvarchar(50),
    @designation nvarchar(50),
    @photo varbinary(max) = null,
    @Pname nvarchar(50)=null,
    @psize bigint=null,
    @id int output
    AS
    BEGIN

        SET NOCOUNT ON;
        insert into OurTeam values (@name,@Sname,@designation,@photo,@Pname,@psize);

        select @id= SCOPE_IDENTITY();
END

Mcatee

Solved my problem ;) – Cellist