none
SqlException inserting NULL into a varbinary(MAX) column.

    Question

  • I get the following error when my insert has a DBNull value for a column of type varbinary(MAX).  "Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query."  In my opinion, the .NET SqlDataAdapter is attempting to convert the null value to a nvarchar.  Is it possible to insert a null value in varbinary(max)?  I didn't have this problem with the image datatype.  Is this a bug or is there a work around to this problem?

     

    Any help would be appreciated.

     

    Thursday, July 12, 2007 2:04 PM

Answers

  • When you create the parameter make sure you set it's type as binary and it's length to -1 so that SQLClient will send the parameter as varbinary(max).

     

    So for example:

     

    SqlCommand cmd = ...

     

    cmd.Parameters.Add( "@myVarBinaryParam", SqlDbType.VarBinary, -1 );

    cmd.Parameters["@myVarBinaryParam"].Value = DbNull.Value;

     

    Matt

    Thursday, July 12, 2007 7:46 PM

All replies

  • When you create the parameter make sure you set it's type as binary and it's length to -1 so that SQLClient will send the parameter as varbinary(max).

     

    So for example:

     

    SqlCommand cmd = ...

     

    cmd.Parameters.Add( "@myVarBinaryParam", SqlDbType.VarBinary, -1 );

    cmd.Parameters["@myVarBinaryParam"].Value = DbNull.Value;

     

    Matt

    Thursday, July 12, 2007 7:46 PM
  • Matt,

     

    You the man!  In my code I use reflection to find the objects property and build the parameter list accordingly.  I was handling items for varbinary correctly, but missed items for varbinary(max).  Your example turned the light bulb on for me and now all is good!

     

    Thanks,

    David

    Thursday, July 12, 2007 8:38 PM
  • I knew you were close, just needed a little hint.
    Thursday, July 12, 2007 9:19 PM
  • Hi Matt,

      I know that this post is quite old, but I was wondering whether you could help me...

      I develop a special function to auto-assign the values to the bind variables:

    protected void CreateCommand(string command, params object[] parameters)
            {
                this.currentDb.resetParameters();
                this.currentDb.setStringCommandParams(command);
                foreach (object parameter in parameters)
                {
                    if (parameter is int)
                    {
                        this.currentDb.addParameters(OleDbType.Integer, parameter);
                    }
                    else
                    if (parameter is short)
                    {
                        this.currentDb.addParameters(OleDbType.SmallInt, parameter);
                    }
                    else if (parameter is string)
                    {
                        this.currentDb.addParameters(OleDbType.VarChar, parameter);
                    }
                    else if (parameter is DateTime)
                    {
                        this.currentDb.addParameters(OleDbType.DBTimeStamp, parameter);
                    }
                    else if (parameter is char)
                    {
                        this.currentDb.addParameters(OleDbType.Char, parameter);
                    }
                    else if (parameter is Byte)
                    {
                        this.currentDb.addParameters(OleDbType.Binary, parameter);
                    }
                    else if (parameter is Byte[])
                    {
                        this.currentDb.addParameters(OleDbType.Binary, parameter);
                    }
                    else if (parameter is decimal)
                    {
                        this.currentDb.addParameters(OleDbType.Decimal, parameter);
                    }
                    else if (parameter == null)
                    {
                        this.currentDb.addParameters(OleDbType.Variant, DBNull.Value);
                    }
                    else
                    {
                        int indexOf = Array.IndexOf(parameters, parameter);
                        throw new ArgumentException("Invalid parameter, handle the unexpected value" + indexOf);
                    }
                }
            }

     So when I execute an update including a colum varbinary(max) to be asigned a null a get the same exception:

            public void updateReportGroupByObject(ReportGroup group)
            {
                const string commandStr = "UPDATE A_REPORT_GROUP SET REPORT_GRP_NAME = ?," +
                                          "FLAG = ?,DEFAULT_REPORT = ?,UPDATE_USER = ?," +
                                          "UPDATE_TIME = ?,DELETE_FLAG = ?,STANDARD_LETTER = ?, " +
                                          "ICON_IMAGE_GROUP = ?, "+
                                          "ICON_CONTENT_TYPE = ?,"+
                                          "ICON_DATA=?, "+
                                          "POSITION=? " +
                                          "WHERE A_REPORT_GROUP = ?";
                this.CreateCommand(commandStr, group.ReportGrpName, group.Flag, group.DefaultReportId,
                                    group.UpdateUser, null, group.DeleteFlag,
                                    group.StandardLetter, group.IconImageGroup,group.IconContentType, group.IconData,
                                    group.Position,group.ReportGroupId);
                this.CurrentDb.executeNoQueryParam();
                if (this.CurrentDb.getCurrentStatus() != DbStatus.SuccessfulWrite)
                {
                    throw (new Exception(CurrentDb.getErrorMessage()));
                }
            }


            The varbinary column is ICON_DATA....My question is...... Can the parameter -1 be specify for all the datatypes when they come in null??? if you see the first function or firts block of code, I am asigning all the DBNull values as follow:

                    else if (parameter == null)
                    {
                        this.currentDb.addParameters(OleDbType.Variant, DBNull.Value);
                    }

      Should be like

                    else if (parameter == null)
                    {
                        this.currentDb.addParameters(OleDbType.Variant, DBNull.Value, -1) ;
                    }  ????????


      You mentioned that is to specify varbinary, but this code execute all the queries in my entire application so I was wondering whether including -1 to all datatypes would work.



      The "addParameters" is a overload methor so it would be using the first one below :


            public void addParameters(object paramType, object paramValue, int aSize)
            {
                string sNextParameterName = nextParameterName();
                oleDbCommand.Parameters.AddWithValue(sNextParameterName, paramType);
                oleDbCommand.Parameters[sNextParameterName].Value = paramValue;
                oleDbCommand.Parameters[sNextParameterName].Size = aSize;
            }


          public void addParameters(object paramType, object paramValue)
            {
                oleDbCommand.Parameters.AddWithValue(nextParameterName(), paramType).Value = paramValue;
            }

            public void addParameters(object paramType, object paramValue, ParameterDirection aParameterDireection)
            {
                string sNextParameterName = nextParameterName();
                oleDbCommand.Parameters.AddWithValue(sNextParameterName, paramType);
                oleDbCommand.Parameters[sNextParameterName].Value = paramValue;
                oleDbCommand.Parameters[sNextParameterName].Direction = aParameterDireection;
            }


            public void addParameters(object paramType, object paramValue, string aParamName, ParameterDirection aParameterDireection)
            {
                OleDbParameter param = new OleDbParameter(aParamName, paramType);
                param.Value = paramValue;
                param.Direction = aParameterDireection;
                oleDbCommand.Parameters.Add(param);

            }



    Thanks
    Jorge
    Wednesday, September 09, 2009 1:48 PM