How to find an offensive column? Unable to convert from string to int32

Question

I am using SqlBulkCopy. So I made the data and pointed the columns, then added the rows to the datatable, and then try to insert it.

Exception System.InvalidOperationException unhandled by user code Message = specified value of type String from data source cannot be converted to int type of specified target

I still get this error. The thing is, I have 3 int columns, and I don't know what it is.

I even put its type in each int column.

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

Apparently a problem. So where in the stack trace or int will the exception indicate the actual column on which it is dying?

Answer 1

Check out the Bruce Dunwiddie excellent ValidatingDataReader class. It provides really great information in exception messages if you have such a display problem.

Answer 2

You can import data into a table in SQL Server, and then you can use ISNUMERIC to see what is:

SELECT *
  FROM YourImportTable
 WHERE ISNUMERIC(Column1) = 0
    OR ISNUMERIC(Column2) = 0
    OR ISNUMERIC(Column3) = 0

If you do not want to leave .NET, you can iterate over the lines and try to convert them:

For Each dr as DataRow in datatable.Rows
   If Not IsNumeric(dr.Item(5))
      ' It this column
   End If
   If Not IsNumeric(dr.Item(6))
      ' It this column
   End If
Next