How to find offending column? Can't Convert from string to int32

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 Messa开发者_JAVA技巧ge=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.

 datatable.Columns.Add("Id", typeof(int));

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