I'm using the SqlBulkCopy class to insert a DataSet into a pair of tables on my SQL Server database.
Sometimes the input data cannot be readily converted into the data type required by the target column. This results in an InvalidOperationException with the following description:
The given value of type String from the data source cannot be converted to type smalldatetime of the specified target column.
The InnerException says:
Failed to convert parameter value from a String to a DateTime.
I have multiple DateTime fields in my table; how can I find out which field is causing the exception?
You could try to convert the data yourself before adding it to the
DataSet. That should highlight the dodgy data.
The only solution is to divide an conquer. Do test inserts with missing columns untill you have isolated the column or create a select using the isdate function (and any others if it might be an out of date range issue to find which columns or rows are at issue