CODEWITHSUNDEEP
c#.netsqlbulkcopy
svenv
svenv

Reputation: 323

SqlBulkCopy - InvalidOperationException - Which field is causing this

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?

Upvotes: 2

Views: 1656

Answers (4)

Ian P
Ian P

Reputation: 1724

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

Upvotes: 1

davmos
davmos

Reputation: 9577

You could try to convert the data yourself before adding it to the DataSet. That should highlight the dodgy data.

Upvotes: 0

XN16
XN16

Reputation: 5869

I had a similar issue a few months back. The way I got around it was to make sure that my DataSet only had one record in it for testing and then set all of the DateTime strings to be a simple date (e.g. 01/01/2000). If you are still getting the error then it will be down to the format of the DateTime field in the database versus the format in the string at a general level as opposed to a single date in the string.

In my case it was due to good old fashioned UK and US date formats! My database was using UK, but some of the string dates were using US formatting.

Upvotes: 0

Oscar
Oscar

Reputation: 13960

For this task maybe you should use SQL Integration Services. With it, you can redirect the offending rows to a file for correction, among other features that will make your job more easy.

See this: http://redmondmag.com/articles/2010/04/12/log-error-rows-ssis.aspx

Upvotes: 1

Related Questions