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

.net c# datatable sqlbulkcopy

Question

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?

Accepted Answer

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.


Popular Answer

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



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why