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.

I have a little detection strategy for dealing with null instances of `byte[]` cast as `object` on a related post: http://stackoverflow.com/a/42905940/97964 — jocull, Mar 20 '17 at 14:50

score 13 · Answer 1 · answered May 12 '16 at 03:10

13

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;

answered May 12 '16 at 03:10

NoName

877
12
28

1

This worked for me and fit in with the helper method that was previously sending DBNull.Value if the byte[] value was null. – jason Feb 17 '17 at 18:08
4 years later and -- This should be the accepted answer! – Jan 'splite' K. Jun 09 '20 at 15:17
`SqlTypes.SqlBinary.Null` for the win. Thank you, kind individual. Take my upvote. – David Mancini Mar 20 '21 at 05:39

kekotek · Answer 2 · 2016-10-17T18:04:05.103

7

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

edited Oct 17 '16 at 18:04

answered Oct 17 '16 at 17:57

kekotek

81
1
2

4

While this answer may answer the question adding some more detail like why and how this works will improve its quality – NathanOliver Oct 17 '16 at 18:05

score 5 · Accepted Answer · edited Apr 07 '15 at 06:28

5

You can try something like this:-

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

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

edited Apr 07 '15 at 06:28

JumpingJezza

5,498
11
67
106

answered Aug 11 '13 at 10:36

Rahul Tripathi

168,305
31
280
331

Thanks. What is -1 for? – Karlx Swanovski Aug 11 '13 at 10:44
It is the length and SqlDbType.VarBinary with length -1 is the equivalent of VARBINARY(MAX), at least in theory. ;) – Rahul Tripathi Aug 11 '13 at 10:52
How did you know that the datatype lenght is MAX? – Karlx Swanovski Aug 11 '13 at 10:58
I dont know...I just assumed!! ;) – Rahul Tripathi Aug 11 '13 at 10:59
Parameters.Add is deprecated, any better idea? – Ronaldinho Learn Coding Jul 28 '15 at 15:31
1

@RonaldinhoLearnCoding Use Parameters.AddWithValue – Jobokai Sep 14 '15 at 15:31

Shamseer K · Answer 4 · 2015-01-15T15:52:19.203

5

try this :

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

edited Jan 15 '15 at 15:52

answered Jan 15 '15 at 15:26

Shamseer K

4,964
2
25
43

3

`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 Jul 28 '15 at 15:30
This is the best solution as stated by Ronaldinho, thanks for the answer Shamseer – Eliseo Aug 03 '15 at 22:52
1

As @RonaldinhoLearnCoding said, it's important to note that `byte[0]` is not the same as `null`. – jocull Mar 20 '17 at 14:51

score 1 · Answer 5 · answered Aug 11 '13 at 10:36

1

i do it like this without a problem

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

answered Aug 11 '13 at 10:36

Ehsan

31,833
6
56
65

but Parameters.Add is deprecated – Ronaldinho Learn Coding Jul 28 '15 at 15:31

score 1 · Answer 6 · edited Jan 28 '18 at 05:06

1

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

edited Jan 28 '18 at 05:06

Sunil

3,404
10
23
31

answered Jan 27 '18 at 18:54

yodhraj patil

11
1

Solved my problem ;) – Zeek2 Feb 10 '20 at 14:00

Null value in a parameter varbinary datatype

6 Answers6

Linked