System.InvalidOperationException: The given ColumnMapping does not match up with any column in the source or destination

Question

Hi, Everybody!...

I am encountering the following error:

System.InvalidOperationException: The given ColumnMapping does not match up with any column in the source or destination.
at System.Data.SqlClient.SqlBulkCopy.AnalyzeTargetAndCreateUpdateBulkCommand(BulkCopySimpleResultSet internalResults)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestContinuedAsync(BulkCopySimpleResultSet internalResults, CancellationToken cts, TaskCompletionSource1 source) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalRestAsync(CancellationToken cts, TaskCompletionSource1 source)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternalAsync(CancellationToken ctoken)
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)...

The exception is thrown upon executing sqlBulkCopy.WriteToServer(dataTable). Please see the following code:

    using (var sqlConnection = new SqlConnection(connectionString))  
    {  
        sqlConnection.Open();  
        using (var sqlBulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))  
        {  
            sqlBulkCopy.DestinationTableName = dataTable.TableName;  

            foreach (DataColumn column in dataTable.Columns)  
                sqlBulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);  

            //var i = 0;  

            //foreach (DataColumn column in dataTable.Columns)  
            //{  
            //    SqlBulkCopyColumnMapping columnMapping = sqlBulkCopy.ColumnMappings[i++];  
            //    Console.WriteLine("{0}\t{1}\t{2}", column.ColumnName, column.ColumnName == columnMapping.SourceColumn, column.ColumnName == columnMapping.DestinationColumn);  
            //}  

            sqlBulkCopy.WriteToServer(dataTable);  
        }  
    }  

The commented debug code produces the following output:

I have spent HOURS on this. What am I missing? What am I doing wrong?

Answer 1

The reason for the error is that some column names are inconsistent even though all the columns are displayed as true.

The following piece of code causes this error:

 foreach (DataColumn column in dataTable.Columns)  
          sqlBulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);  

The data of sqlBulkCopy.ColumnMappings comes from Datatable, and then you use it to compare with the column name in Datatable, it will always be equal.

I think you should compare the column names of the Datatable with the column names of the tables in the database.

I suggest you use the following code to check it:

                    using (SqlCommand command = new SqlCommand("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'scoretable'", sqlConnection))  
                    {  
                        DataTable columnNames = new DataTable();  
                        columnNames.Load(command.ExecuteReader());  
  
                        int i = 0;  
                        foreach (DataColumn column in dataTable.Columns)  
                        {  
                            string columnNameInDB = columnNames.Rows[i++]["COLUMN_NAME"].ToString();  
                            Console.WriteLine("{0}\t{1}\t{2}", column.ColumnName, columnNameInDB, columnNameInDB == column.ColumnName);  
                        }  
                    }  

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.