SQL Bulk Copy "This String value from the data source cannot be converted to the datetime type of the specified target column" using ASP.NET

Question

I am working on an ASP.NET MVC4 projet and I am trying to export data from an xlsx file (Excel 2010 file) to my database using SQL Bulk Copy. My Excel file contains only 2 columns: the first contains numbers (from 1 to 25), and the second contains characters (consecutive series "a, b, c")

This is how I try to export the data, but I got an error ". The specified value of type String from the data source cannot be converted to the int type of the specified target column" :

public ActionResult Bulk() { string xConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\maarab\Documents\BulkCopy.xlsx;Extended Properties=Excel 12.0;"; using (OleDbConnection connection = new OleDbConnection(xConnStr)) { OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection); connection.Open(); string dbConnection = ((EntityConnection)db.Connection).StoreConnection.ConnectionString; // Create DbDataReader to Data Worksheet using (DbDataReader dr = command.ExecuteReader()) { using (var bulkCopy = new SqlBulkCopy(dbConnection)) { bulkCopy.DestinationTableName = "bm_test" bulkCopy.WriteToServer(dr); //here I got the error } } return RedirectToAction("Index"); } 

Any idea on what causes this error?

Answer 1

SqlBulkCopy.WriteToServer(DataTable) does not work with confusing messages if the column order of the DataTable is different from the column order of the table definitions in your database (when this causes type or length incompatibility). Apparently, the WriteToServer method WriteToServer not display column names.

Answer 2

Mine whined about it until I set the order manually like this:

 SqlBulkCopy sbc = new SqlBulkCopy(ConnectionString, SqlBulkCopyOptions.UseInternalTransaction); sbc.DestinationTableName = "agSoilShapes"; sbc.ColumnMappings.Add("Mukey", "Mukey"); sbc.ColumnMappings.Add("Musym", "Musym"); sbc.ColumnMappings.Add("Shapes", "Shapes"); DataTable dt = new DataTable(); dt.Columns.Add("Mukey", typeof(SqlInt32)); dt.Columns.Add("Musym", typeof(SqlString)); dt.Columns.Add("Shapes", typeof(SqlGeometry)); 

Thanks to others (@subsci) for the comments leading me in this direction :)

Answer 3

I get a similar error when using the EntityFramework.BulkInsert package. In this case, the main reason was the mismatch between the columns of the database table and the generated model metadata columns (first for the database).

Answer 4

The column order of the DataTable columns and the column order of the database tables must be the same. It worked after updating the column order of tables.