C# – SQL Bulk Copy “The given value of type String from the data source cannot be converted to type datetime of the specified target column” using ASP.NET

asp.netcexcelsqlbulkcopy

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

This is how I try to do in order to export data but I got the error "The given value of type String from the data source cannot be converted to type int 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 about what's causing this error?

Best Answer

SqlBulkCopy.WriteToServer(DataTable) fails with confusing messages if the column order of the DataTable differs from the column order of the table definition in your database (when this causes a type or length incompatibility). Apparently the WriteToServer method does not map column names.

Related Topic