The given value of type String from the data source cannot be converted to type varbinary of the specified target column

c# sqlbulkcopy

Question

I have a datatable with one column which has image file path. I need to replace the file path with byte array of that image. The below code gives me error.

The given value of type String from the data source cannot be converted to type varbinary of the specified target column.

for (int rowIncrement = 0; rowIncrement < dt.Rows.Count; rowIncrement++)
{
    byte[] photo = GetPhoto(dt.Rows[rowIncrement]["image"]);
    dt.Rows[rowIncrement]["image"] = photo;
    dt.AcceptChanges();
}
using (SqlBulkCopy copy = new SqlBulkCopy(sqlCon, SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.KeepIdentity, null))
{
    copy.DestinationTableName = "UserDetails";
    copy.WriteToServer(dt);
}

Expert Answer

The column type should be specified otherwise the columns value is still a string when you need a byte[]

Example:

// ADD a new column since you cannot change the type

dt.Columns.Add("image_tmp", typeof (byte[]));

for (int rowIncrement = 0; rowIncrement < dt.Rows.Count; rowIncrement++)
{
    byte[] photo = GetPhoto(dt.Rows[rowIncrement]["image"]);
    dt.Rows[rowIncrement]["image_tmp"] = photo;
    dt.AcceptChanges();
}

// REMOVE image column and rename the temporary column
dt.Columns.Remove("image");
dt.Columns["image_tmp"].ColumnName = "image";



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why