Cannot insert null value in varbinary(max) with error : implicit conversion from data type nvarchar to varbinary(max) is not allowed

Question

I want to insert null value in varbinary(max) but it is returning an error.

I am trying the code below to save the photo, when I attach photo it saves without any issue. When there is no photo it throws an error.

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

protected void Button3_Click(object sender, EventArgs e)
{
    newphoto pat = new newphoto();
    pat.id = id.Text;
    byte[] photo = null;
    if (Attch.HasFile)
    {
        Stream fs2 = Attch.PostedFile.InputStream;
        BinaryReader br2 = new BinaryReader(fs2);
        pat.photo = br2.ReadBytes((Int32)fs2.Length);
    }
    else
    {
        pat.photo = null;
    }
    pat.Addfile()
}

public bool Addfile()
{
    Parameters.Clear();
    Parameters.AddWithValue("@pat_id", id);
    if (photo == null)
    {
        Parameters.Add("@photo", SqlDbType.VarBinary, -1);
        Parameters["@photo"].Value = DBNull.Value;
    }
    else
    {
        Parameters.AddWithValue("@photo", photo);
    }
    return FetchNonQuery(@"insert into mr_Info (@pat_id ,@photo)" +
               " Values (@pat_id ,@photo)");
}

protected bool FetchNonQuery(string CmdQuery)
{
    bool result = false;
    using (SqlConnection myConnection = DBConnection)
    {
        SqlCommand myCommand = new SqlCommand(CmdQuery, myConnection);
        myCommand.CommandType = CommandType.Text;
        //Set Parameters       
        foreach (SqlParameter Parameter in _parameters)
        {
            myCommand.Parameters.AddWithValue(Parameter.ParameterName, Parameter.Value);
        }
        //Execute the command
        myConnection.Open();
        if (myCommand.ExecuteNonQuery() > 0)
        {
            result = true;
        }
        myConnection.Close();
    }
    return result;
}

Try to change the line that add the parameters to the command in _myCommand.Parameters.Add(Parameter);_ — Steve, Apr 25 '17 at 20:09
Also the INSERT command is wrong. You use parameters for the field names and for the values. You should write _INSERT INTO (pat_id,photo) VALUES (@pat_id ,@photo)_ — Steve, Apr 25 '17 at 20:12
Possible duplicate of [Null value in a parameter varbinary datatype](http://stackoverflow.com/questions/18170985/null-value-in-a-parameter-varbinary-datatype) — Mad Myche, Apr 25 '17 at 20:18
i am getting this error on replacing with myCommand.Parameters.Add(Parameter); The SqlParameter is already contained by another SqlParameterCollection. — Sultan, Apr 26 '17 at 05:26

Steve · Accepted Answer · 2017-04-25T20:21:18.587

This is a subtle bug caused by the AddWithValue call. (And not the only one).
When AddWithValue is able to correctly identify the type of the parameter you don't have a problem, but when you set the parameter value to DBNull.Value the method tries to convert this to a string and you get the error because the data field expects a VARBINARY.
However, when you build the parameter list, you are able to exactly specify the type expected and thus you can simply pass the parameter to the Add method instead of building another parameter with AddWithValue.

foreach (SqlParameter Parameter in _parameters)
{
    myCommand.Parameters.Add(Parameter);
}

You can even remove the loop with

myCommand.Parameters.AddRange(_parameters.ToArray());

Also, as pointed in my other comment, the INSERT command should be written as

INSERT INTO (pat_id,photo) VALUES (@pat_id ,@photo)

Cannot insert null value in varbinary(max) with error : implicit conversion from data type nvarchar to varbinary(max) is not allowed

1 Answers1