Error: Failed to convert parameter value from a SqlBinary to a Byte[]

Question

public class MyClass 
{
    public int ID { get; set; }
    public string Name { get; set; }
    public byte[] Photo { get; set; }
}    

public class Program
{
    static void Main(string[] args)
    {
        string imageFileName = @"D:\Demure.jpg";
        string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\db1.mdb;Persist Security Info=False";
        string providerName = @"System.Data.OleDb";            
        string SQL_UPDATE = @"update  Word SET Name=@Name, Photo=@Photo where ID=@ID"; 

        DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);

        IDbConnection Connection = factory.CreateConnection();
        Connection.ConnectionString = connString;
        Connection.Open();

        IDbTransaction Transaction = Connection.BeginTransaction();

        IDbCommand Command = factory.CreateCommand();
        Command.Connection = Connection;
        Command.Transaction = Transaction;

        Command.CommandText = SQL_UPDATE;            

        IDbDataParameter param2 = Command.CreateParameter();
        param2.ParameterName = "Name";
        param2.Value = "Hello?";
        param2.DbType = DbType.String;
        Command.Parameters.Add(param2);

        IDbDataParameter param3 = Command.CreateParameter();
        param3.ParameterName = "Photo";
        param3.Value = SqlBinary.Null;//ConvertImage.ToByteArray(Bitmap.FromFile(imageFileName));
        param3.DbType = DbType.Binary;
        Command.Parameters.Add(param3);

        IDbDataParameter param1 = Command.CreateParameter();
        param1.ParameterName = "ID";
        param1.Value = 1000;
        param1.DbType = DbType.Int32;
        Command.Parameters.Add(param1);

        IDataReader dataReader = Command.ExecuteReader();            

        // read data
        IList<MyClass> list = null;
        MyClass item = null;
        while (dataReader.Read())
        {
            if (list == null)
            {
                list = new List<MyClass>();
            }

            item = new MyClass();
            item.ID = dataReader.GetInt32(0);
            item.Name = dataReader.GetString(1);

            list.Add(item);
        }

        dataReader.Close();

        Transaction.Commit();

        string str = string.Empty;
    }
}

public static class ConvertImage
{
    public static byte[] ToByteArray(string imageLocation)
    {
        byte[] bytes = null;

        try
        {
            bytes = File.ReadAllBytes(imageLocation);
        }
        catch
        {
        }

        return bytes;
    }

    public static byte[] ToByteArray(System.Drawing.Image imageIn)
    {
        MemoryStream ms = new MemoryStream();
        imageIn.Save(ms, System.Drawing.Imaging.ImageFormat.Gif);

        return ms.ToArray();
    }

    public static Image ToImage(string imageLocation)
    {
        byte[] bytes = ToByteArray(imageLocation);

        return ToImage(bytes);
    }

    public static Image ToImage(byte[] byteArrayIn)
    {
        Image returnImage = null;

        try
        {
            if (byteArrayIn != null)
            {
                MemoryStream ms = new MemoryStream(byteArrayIn);
                returnImage = Image.FromStream(ms);
            }
        }
        catch (Exception ex)
        {
            string str = String.Empty;
        }

        return returnImage;
    }
}

This program is giving an error at IDataReader dataReader = Command.ExecuteReader();

Error message:

An unhandled exception of type 'System.InvalidCastException' occurred in System.Data.dll

Additional information: Failed to convert parameter value from a SqlBinary to a Byte[].

I also tried SqlBytes.Null. Gives the same message.

How can I solve this issue?

P.S. That field already holds an image data. I am trying to update that using Null value.

What data type did you use for the `Photo` column in the database? — Ashley Pillay, Aug 30 '17 at 05:55
I haven't used Access as a DB in years, but I see that you are call `ExecuteReader` on an `Update` statement & then trying to read the results. `Update` statements don't usually return results, not unless you use something like SQL Server's `OUTPUT` clause. Are you sure the JET engine returns the updated row when you do an `UPDATE`? — Ashley Pillay, Aug 30 '17 at 06:06
Instead of using `ExecuteReader`, you may want to use `ExecuteNonQuery` for updating data ( `ExecuteReader` primarily designed to use with `SELECT` queries). Also try using `BinaryReader` to convert image data into byte array with this resource: https://stackoverflow.com/questions/23461320/storing-and-retrieving-images-from-an-access-database-in-c-sharp. — Tetsuya Yamamoto, Aug 30 '17 at 06:09
Try replacing `param3.Value = SqlBinary.Null;` with `param3.Value = (new byte[0]);` Do you still get the same conversion error? — Ashley Pillay, Aug 30 '17 at 06:18
@AshleyPillay, `param3.Value = (new byte[0]);` works. But, question remains. What is the `SqlXXX.Null` equivalent of this statement? — user366312, Aug 30 '17 at 07:05

score 1 · Accepted Answer · answered Aug 30 '17 at 07:41

1

The equivalent of param3.Value = SqlBinary.Null; should be param3.Value = DBNull.Value;

answered Aug 30 '17 at 07:41

Ashley Pillay

868
4
9

Error: Failed to convert parameter value from a SqlBinary to a Byte[]

1 Answers1