How can I locate the problematic column? Converting from string to int32 is not possible.

.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?

1
3
6/4/2010 9:57:18 PM

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.

3
6/5/2010 2:07:13 AM

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


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow