CODEWITHSUNDEEP
c#sqlbulkcopy
Madonna10
Madonna10

Reputation: 33

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

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);
}

Upvotes: 0

Views: 577

Answers (1)

Jonathan Magnan
Jonathan Magnan

Reputation: 11347

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";

Upvotes: 0

Related Questions