Saturday, 3 May 2014

ERROR: Operand type clash: sql_variant is incompatible with image

I got this error while insert the image file's byte [] data into Sql server database in image datatype column. I am using SqlParameter to pass input image file's byte array. Since I don't found the DbType.Image, just given DbType.Object as document suggests (DbType.Object-A general type representing any reference or value type not explicitly represented by another DbType value).

ERROR - Operand type clash: sql_variant is incompatible with image:

Fix ERROR: Operand type clash: sql_variant is incompatible with image

Fix Error - Operand type clash: sql_variant is incompatible with image:

The error was fixed when I change the DbType from DbType.Object to DbType.Binary
public static void InsertImageintoSqlDatabaseAsImage(string imageFilePath)
{
    using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
    {
        sqlconnection.Open();

        // create table if not exists 
        string createTableQuery = @"Create Table [UserTable] ( UserID int, [Photo] image)";
        SqlCommand command = new SqlCommand(createTableQuery, sqlconnection);
        command.ExecuteNonQuery();

        // Converts image file into byte[]
        byte[] imgData = File.ReadAllBytes(imageFilePath);

        string insertXmlQuery = @"Insert Into [UserTable] (UserID,[Photo]) Values(1,@Photo)";

        // Insert Image Value into Sql Table by SqlParameter
        SqlCommand insertCommand = new SqlCommand(insertXmlQuery, sqlconnection);
        SqlParameter sqlParam = insertCommand.Parameters.AddWithValue("@Photo", imgData);
        sqlParam.DbType = DbType.Binary;
        insertCommand.ExecuteNonQuery();
    }
}


Thanks,
Morgan
Software Developer

Advertisements
Advertisements

No comments:

Post a Comment