Parameterized Queries and varbinary(max) problems (and fixes)

Posted by Blake on 1/22/2013
)

These are the exceptions that you may see occur:

    “Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query."
    “The parameterized query expects the parameter '@file_data', which was not supplied.”

The above exceptions may occur when you try to insert a null value into a varbinary field. There are a few things to keep in mind here. First, use DbNull.Value as the null value and not “null” or “Nothing”. Second, if you are using the AddWithValue method to add the parameter in with the value know that it has issues with varbinary fields. For whatever reason, you have to specifically setup the varbinary parameter with a length of -1 and then add it in so it sends to the server. Here is an example:

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