Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can't insert data table using sqlbulkcopy

Tags:

c#

sqlbulkcopy

This is my code with the following columns and in the DB, those columns are nvarchars.

SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, 
System.Data.SqlClient.SqlBulkCopyOptions.Default, transaction);

bulkCopy.DestinationTableName = "Test";
bulkCopy.ColumnMappings.Add("Number", "Code");
bulkCopy.ColumnMappings.Add("Type", "Type");
bulkCopy.ColumnMappings.Add("Group", "Group");
bulkCopy.ColumnMappings.Add("Short Text", "ShortText");
bulkCopy.ColumnMappings.Add("Text", "Description");
bulkCopy.WriteToServer(dataTable);

I am trying to insert a whole data table in a DB, using bulk copy but I am getting this error:

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

like image 871
Rocshy Avatar asked Feb 14 '12 07:02

Rocshy


2 Answers

It may be caused by a too-short-column. If your ShortText column is defined as NVARCHAR(30) and you have a 40 character string, you may get that error. See here for example: http://forums.asp.net/p/1228890/2212376.aspx

like image 181
zmbq Avatar answered Oct 06 '22 21:10

zmbq


I have another answer for this question, maybe it can come in handy for someone. If you have a String which contains " (quotation mark), you get this error message too. At least I just got this error message, and I searched for the wrong row, and I found this. As I removed the quotation marks, the error disappeared.

like image 24
KatieAmber Avatar answered Oct 06 '22 20:10

KatieAmber