Implicit conversion from data type nvarchar to varbinary(max) is not allowed

Question

I get this exception when I try to insert a DBNull.Value into a nullable varbinary(max) field:

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

This is my code:

insertCMD.Parameters.AddWithValue("@ErrorScreenshot", SqlDbType.VarBinary).Value = DBNull.Value;

I know there exist duplicate questions on SO, but I do NOT use any String like the others do.

What do I wrong?

UPDATE:

using (var insertCMD = new SqlCommand("INSERT INTO TestplanTeststep (TeststepId,TestplanId,CreatedAt,ErrorText,ErrorScreenshot,TestState) VALUES (@TeststepId, @TestplanId,@CreatedAt,@ErrorText,@ErrorScreenshot,@TestState)", con))
{
 var p1 = insertCMD.Parameters.Add("@TeststepId", SqlDbType.Int);
 var p2 = insertCMD.Parameters.Add("@CreatedAt", SqlDbType.DateTime);
 insertCMD.Parameters.AddWithValue("@TestplanId", testplan.Id);
 insertCMD.Parameters.AddWithValue("@ErrorText", (object) DBNull.Value);
 insertCMD.Parameters.AddWithValue("@ErrorScreenshot", (object) DBNull.Value);
 insertCMD.Parameters.AddWithValue("@TestState", (int)Teststep.TeststepTestState.Untested);
        
       foreach (Teststep step in teststeps)
        {
           p1.Value = step.Id;
           p2.Value = step.CreatedAt;
           insertCMD.ExecuteNonQuery();
        }
     }

Doesn't look like the right line of code. UniqueIdentifier is not mentioned at all. Maybe provide the stored procedure definition too? — Sean, Jul 10 '12 at 11:01
The second parameter of `AddWithValue` is the value. Not the datatype. — Martin Smith, Jul 10 '12 at 11:17
This bug seems to have returned in .NET5+. The -1 size trick no longer works. Anyone know any other solutions? — Emperor Eto, Oct 07 '21 at 18:32

score 25 · Answer 1 · edited Feb 06 '13 at 05:41

25

I had the same problem while insertion DBNull.Value for a Varbinary(Max) column. After Googling I found a solution that may help you as well:

You need to set size -1 which means Max length for varbinary column when adding your sql parameter:

this.cmd.Parameters.Add("@Photo", SqlDbType.VarBinary, -1).Value = DBNull.Value;

So in your case:

insertCMD.Parameters.Add("@ErrorScreenshot", SqlDbType.VarBinary,-1).Value = DBNull.Value;

edited Feb 06 '13 at 05:41

Dhara

4,093
2
36
69

answered Feb 06 '13 at 05:20

gyousefi

306
3
9

This basically worked for me. Thank you! I had to make a couple changes so it looks like this: _params.Add("Value", reportCache.Value, DbType.Binary, ParameterDirection.Input, - 1); – Don Rolling Aug 29 '18 at 15:29
Thank you! that did it. I was at a loss why it was giving this error. – AntiqTech Mar 05 '19 at 15:55
1

This bug seems to have returned in .NET5+. The -1 size trick no longer works. Anyone know any other solutions? – Emperor Eto Oct 07 '21 at 18:32

Sean · Accepted Answer · 2012-07-10T12:40:48.730

Why not change your SQL to:

INSERT INTO TestplanTeststep
(TeststepId,TestplanId,CreatedAt,ErrorText,ErrorScreenshot,TestState) 
VALUES 
(@TeststepId, @TestplanId,@CreatedAt,NULL,NULL,@TestState)

or just

INSERT INTO TestplanTeststep
(TeststepId,TestplanId,CreatedAt,TestState) 
VALUES 
(@TeststepId, @TestplanId,@CreatedAt,@TestState)

...and omit the two parameters?

If it's always NULL, that will have the same effect.

Otherwise, try it in two lines:

var binary1 = insertCMD.Parameters.Add("@ErrorScreenshot", SqlDbType.VarBinary, -1);
binary1.Value = DBNull.Value;

Otherwise, in your original SQL insert statement, you're not defining the parameter type but passing in varbinary, hence the error.

ah you were faster just found the -1 trick hehe. I want to explicitly set to null this way others or I know about the default behavior of the table. — Pascal, Jul 10 '12 at 12:49

score 0 · Answer 3 · answered Aug 07 '23 at 12:15

0

I had the same issue and it was resolved using Abdülkadir MAVİ's post here.

I used the line below instead of DBNull.Value:

param.Value = Thumbnail is null ? System.Data.SqlTypes.SqlBinary.Null : Thumbnail;

answered Aug 07 '23 at 12:15

Jeremy Hodge

612
3
14

Implicit conversion from data type nvarchar to varbinary(max) is not allowed

3 Answers3