[SQL Server] Error inserting varbinary(max) with NULL value

[danehnert]

I currently use Dapper and SqlKata for the data access in my application.

My entity class contains a byte[] which should be persisted in the database and might contain NULL values.

When I try to update or insert a varbinary(max) column with this NULL value, I get the following error: Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

When writing the necessary query by hand and using plain Dapper to execute it, everything works without any problems. The underlying entiry class containing the information to persist is in both cases the same.

This is the method to execute the SqlKata query with Dapper:

public int Execute(Query query, int? commandTimeout = null, CommandType? commandType = null)
{
    SqlResult sqlResult = _queryCompiler.Compile(query);
    return _connection.Execute(sqlResult.Sql, sqlResult.NamedBindings, _transaction, commandTimeout, commandType);
}

[chitraportfolio]

Hello,

My advice is you try one step at a time: meaning, try to insert first. If you are trying to insert many data, try doing it one by one. It will certainly cost you a lot of time but at least you will know where the error lies and you will get a better idea. Because, from the error message you are getting: "Implicit conversion from data type nvarchar to varbinary(max) is not allowed.(...)", this is self-explanatory, maybe the error is in the data type in your C# code. Once you know where the issue(s) is/are, try to update one by one. This is the only way to know exactly where the problem is instead of having nightmares because of it!

Goodluck :)

[ahmad-moussawi]

@danehnert too late to reply, can you please first try the latest version to see if the issue persists, if yes, can you post a full reproducible scenario:

• Table schema
• Working insert query with data
• Failed insert query with data

[askovpen]

public class test 
{
  public int id {get; set; }
  public byte[]? data { get; set; } 
}

in sql log (mssql): insert into [table] ([id], [data]) values(0, 'System.Byte[]') error if data=null: Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query.