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

Question

Hi

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.

 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?

Answer 1

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

Answer 2

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.