错误源表和目标表列不匹配:SqlBulkCopy [英] Error Source and destination tables column mismatch : SqlBulkCopy

问题描述

谁能告诉我我的代码有什么问题
错误消息为"Source and destination tables column mismatch"(SQL批量复制插入)
//================================================ =========

Can anyone tell me what is the wrong in my code
Error message is "Source and destination tables column mismatch" (SQL bulkcopy insert)
//=========================================================

my sql Table name is :dl_Temp_Investigation<br />
Columns are : <br />
GroupId<br />
TestCode<br />
TestName<br />
GroupName<br />
Charge<br />
Discount<br />
testno<br />
<br />
my Datatable name is: dt<br />
column are :<br />
GroupId<br />
TestCode<br />
TestName<br />
Group<br />
Charge<br />
Discount<br />
testno

//================================================ =============
我的代码如下
//======================================

//==============================================================
my code is as follows
//========================================

public int InsertInvestigation(DataTable dt)
    {
        int retVal = 0;
        string connectionString = System.Configuration.ConfigurationSettings.AppSettings["epracticedb_connectstring"];
        SqlConnection sqlConn = null;
        SqlCommand sqlCommand = null;
       
            sqlConn = new SqlConnection(connectionString);
            sqlConn.Open();
            SqlTransaction lTransaction = sqlConn.BeginTransaction();
            string aa = dt.Columns[6].ToString();
            try
            {
                int rowcount = dt.Rows.Count;               
                using (SqlBulkCopy bcopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.Default, lTransaction))
                {
                    bcopy.DestinationTableName = "dl_investigationFinal"; // the table to which data is to be written
                    bcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dt.Columns[6].ToString(), "testno"));
                    bcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dt.Columns[0].ToString(), "GroupId"));
                    bcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dt.Columns[1].ToString(), "TestCode"));
                    bcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dt.Columns[2].ToString(), "TestName"));
                    bcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dt.Columns[3].ToString(), "GroupName"));
                    bcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dt.Columns[4].ToString(), "Charge"));
                    bcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(dt.Columns[5].ToString(), "Discount"));
                    bcopy.BatchSize = rowcount; // records to be written in one batch
                    bcopy.NotifyAfter = 200; // in number of records
                    bcopy.WriteToServer(dt);
                }
                    lTransaction.Commit();
                    retVal = 1;
            }
            catch (Exception e)
            {
                lTransaction.Rollback();
                throw;
                retVal = 0;
            }
            finally
            {
                if (sqlCommand != null) sqlCommand.Dispose();
                if (sqlConn != null)
                {
                    if (sqlConn.State == ConnectionState.Open) sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return retVal;
    }

[edit]添加了代码块-OriginalGriff [/edit]

[edit]Code block added - OriginalGriff[/edit]

推荐答案

确保两个表中的列类型(varchar，int等)也相同.

Make sure the column types (varchar, int etc) in the two tables are the same as well.

这篇关于错误源表和目标表列不匹配:SqlBulkCopy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！