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.net c# excel sqlbulkcopy

Question

I'm attempting to use SQL Bulk Copy to export data from an xlsx file (Excel 2010 file) to my database as part of an ASP.NET MVC4 project. There are just two columns in my Excel file: one includes numbers (from 1 to 25), and the other has characters (successive series of "a, b, c")

I tried using this to export data, but I get the error "The provided value from the data source of type String cannot be translated to type int of the chosen destination column," the error message reads.:

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 ideas as to the root of this error?

1
5
7/19/2013 8:00:34 AM

Popular Answer

SqlBulkCopy.WriteToServer(DataTable) fails with confusing messages if the column order of theDataTable different from the column definition of the table in your database's (when this causes a type or length incompatibility). Evidently, theWriteToServer column names aren't mapped by the procedure.

12
9/8/2014 3:09:33 AM


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