can't add datatable to sqlbulkcopy

Question

This is my code

public static void InsertToDatabase(DataTable dataTable) {
    using (SqlBulkCopy sbc = new SqlBulkCopy(GetConnectionString())){
        sbc.DestinationTableName = tableName;
        sbc.WriteToServer(dataTable);
    }
}

This is the exception:

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

Is there any way to know on which column the exception belongs?

How you are creating the datatable? define table column exact as target table. — Rahul, Jul 29 '14 at 20:39
@Rahul it is a long story, I am using CsvHelper library to read data from about 500 csv file and load that data into a datatable. Anyway, that works fine and I don't think that the datatable has any mistake — Agnieszka Polec, Jul 29 '14 at 20:40
@Rahul yes sure, the table columns are exactly as the datatable columns. 100% sure of that. I am aware to this point — Agnieszka Polec, Jul 29 '14 at 20:41
OK. In that case, there must be size mismatch. I mean the particular column in exception is having a size which is not able to hold the size of data in datatable. Can you check and confirm that. — Rahul, Jul 29 '14 at 20:54
@Rahul I don't know if I understood you correctly, but the database table has 39 column and my csv files has 38. However the remaining 1 column is nullable . is that what you mean? — Agnieszka Polec, Jul 29 '14 at 20:56
@Rahul the input data in all csv file is one char (which is number). — Agnieszka Polec, Jul 29 '14 at 20:57
No, not exactly. what I mean is if column in exception is defined @ DB end as say `nchar(10)` and you are trying to insert a data of size > 10 chars then also you will hit this exception. — Rahul, Jul 29 '14 at 20:58
@Rahul this is my data `1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38` so simple. there is no field in database that has nchar(1) or nchar(2), at least I have nchar (15) — Agnieszka Polec, Jul 29 '14 at 21:01
last suggestion; Well if this your data then make 2 changes. 1. create the datatable's datacolumn of type `INT` and @DB end, ALTER the table to make the column as INT type too. for integer type it doesn't at make sense to store them as string/nchar. — Rahul, Jul 29 '14 at 21:05
@Rahul I do have string values, but now I am just testing that is why I am using integer values. thanks for your suggestions — Agnieszka Polec, Jul 29 '14 at 21:10
@Rahul you right, there is a column that has nchar(1) by mistake. it should be nchar(10). write an answer to accept it please. many thanks — Agnieszka Polec, Jul 30 '14 at 06:35
You'r welcome. Was not thinking but anyways posted it as answer if it helps future readers. — Rahul, Jul 30 '14 at 15:11

score 2 · Accepted Answer · answered Jul 30 '14 at 15:09

There must be size mismatch. I mean the particular column in exception is having a size which is not able to hold the size of data in datatable.

what I mean is if column in exception is defined @DB end as say nchar(10) and you are trying to insert a data of size > 10 chars then also you will hit this exception.

Check and make sure the same.

can't add datatable to sqlbulkcopy

1 Answers1