CODEWITHSUNDEEP
sqlinsertdbnullvarbinarymax
Pascal
Pascal

Reputation: 12855

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

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();
        }
     }

Upvotes: 18

Views: 83861

Answers (4)

Chima Israel
Chima Israel

Reputation: 1

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

when I had that error, I was able to trace it to my table structure where I had defined different data types for nvarchar and varbinary for a common columns say 'X' in two tables.

here's my solution I used in the stored procedure where these tables were being referenced:

insert into tablename (X,Y,Z) values (convert(varbinary,@X),@Y,@Z)

let me know if this works

Upvotes: 0

Jeremy Hodge
Jeremy Hodge

Reputation: 662

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;

Upvotes: 2

gyousefi
gyousefi

Reputation: 351

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;

Upvotes: 27

Sean
Sean

Reputation: 15144

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.

Upvotes: 8

Related Questions