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();
}
}
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;
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With