I am using SqlBulkCopy. So I made a datatable and specifed it's columns then added rows to the datatable and then try to insert it.
System.InvalidOperationException was unhandled by user code Message=The given value of type String from the data source cannot be converted to type int of the specified target
I keep getting this error though. The thing is I have 3 int columns and I have no clue which one it is.
I even put on each int column the type of of it.
Still seems to have problems. So where in the stack trace or int the exception will it say the actual column it dies on?
Check out Bruce Dunwiddie's excellent ValidatingDataReader class. It provides really excellent information in the exception messages when you have this kind of mapping issue.
You can import the data into a table in SQL Server, and then you can use ISNUMERIC to see which is is:
SELECT * FROM YourImportTable WHERE ISNUMERIC(Column1) = 0 OR ISNUMERIC(Column2) = 0 OR ISNUMERIC(Column3) = 0
If you don't want to leave .NET, you can loop through the rows and try to convert them:
For Each dr as DataRow in datatable.Rows If Not IsNumeric(dr.Item(5)) ' It's this column End If If Not IsNumeric(dr.Item(6)) ' It's this column End If Next