SQL批量复制＆QUOT;从数据源String类型的给定值不能转换为键入指定的目标列＆QUOT的日期时间;使用ASP.NET [英] 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 MVC4谟和我试图通过使用SQL批量复制数据从XLSX文件（Excel 2010中的文件）导出到我的数据库。我的Excel文件只包含2列：第一个是包含的数字（从1到25），第二包含的字符（连续一系列的A，B，C）

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")

这是我如何努力，以做出口数据，但我得到了错误的从数据源String类型的给定值不能转换为键入指定的目标列的整数

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?

推荐答案

SqlBulkCopy.WriteToServer（数据表）失败，混乱的信息如果列顺序在数据表不同于表定义的数据库列的顺序时（这会导致一个类型或长度不兼容）。显然，在WriteToServer 方法不映射列名。

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.

这篇关于SQL批量复制＆QUOT;从数据源String类型的给定值不能转换为键入指定的目标列＆QUOT的日期时间;使用ASP.NET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！