The given ColumnMapping does not match up with any

问题:

I dont know why I am getting the above exception, please someone look at it ....

DataTable DataTable_Time = new DataTable("Star_Schema__Dimension_Time");

DataColumn Sowing_Day = new DataColumn();
Sowing_Day.ColumnName = "Sowing_Day";

DataColumn Sowing_Month= new DataColumn();
Sowing_Month.ColumnName = "Sowing_Month";      

DataColumn Sowing_Year = new DataColumn();
Sowing_Year.ColumnName = "Sowing_Year";

DataColumn Visit_Day= new DataColumn();
Visit_Day.ColumnName = "Visit_Day";

DataColumn Visit_Month = new DataColumn();
Visit_Month.ColumnName = "Visit_Month";

DataColumn Visit_Year = new DataColumn();
Visit_Year.ColumnName = "Visit_Year";

DataColumn Pesticide_spray_day = new DataColumn();
Pesticide_spray_day.ColumnName = "Pesticide_spray_day";

DataColumn Pesticide_spray_Month = new DataColumn();
Pesticide_spray_Month.ColumnName = "Pesticide_spray_Month";

DataColumn Pesticide_spray_Year = new DataColumn();
Pesticide_spray_Year.ColumnName = "Pesticide_spray_Year";

DataTable_Time.Columns.Add(Pesticide_spray_Year);
DataTable_Time.Columns.Add(Sowing_Day);
DataTable_Time.Columns.Add(Sowing_Month);
DataTable_Time.Columns.Add(Sowing_Year);
DataTable_Time.Columns.Add(Visit_Day);
DataTable_Time.Columns.Add(Visit_Month);
DataTable_Time.Columns.Add(Visit_Year);
DataTable_Time.Columns.Add(Pesticide_spray_day);
DataTable_Time.Columns.Add(Pesticide_spray_Month);

adapter.SelectCommand = new SqlCommand(
    "SELECT SowingDate,VisitDate,PesticideSprayDate " +
    "FROM Transformed_Table " + 
    "group by SowingDate,VisitDate,PesticideSprayDate", con);

adapter.SelectCommand.CommandTimeout = 1000;

adapter.Fill(DataSet_DistinctRows, "Star_Schema__Dimension_Time");

DataTable_DistinctRows = DataSet_DistinctRows.Tables["Star_Schema__Dimension_Time"];

int row_number = 0;
int i = 3;

foreach(DataRow row  in DataTable_DistinctRows.Rows)
{
    DataRow flatTableRow = DataTable_Time.NewRow();

    string[] Sarray= Regex.Split(row[0].ToString()," ",RegexOptions.IgnoreCase);
    string[] finalsplit = Regex.Split(Sarray[0], "/", RegexOptions.IgnoreCase);
    string[] Sarray1 = Regex.Split(row[1].ToString(), " ", RegexOptions.IgnoreCase);
    string[] finalsplit2 = Regex.Split(Sarray1[0], "/", RegexOptions.IgnoreCase);
    string[] Sarray2= Regex.Split(row[2].ToString(), " ", RegexOptions.IgnoreCase);
    string[] finalsplit3 = Regex.Split(Sarray2[0], "/", RegexOptions.IgnoreCase);             

    flatTableRow["Sowing_Day"] = int.Parse(finalsplit[0]);
    flatTableRow["Sowing_Month"] = int.Parse(finalsplit[0]);
    flatTableRow["Sowing_Year"] = int.Parse(finalsplit[0]);

    flatTableRow["Visit_Day"] = int.Parse(finalsplit2[0]);
    flatTableRow["Visit_Month"] = int.Parse(finalsplit2[0]);
    flatTableRow["Visit_Year"] = int.Parse(finalsplit2[0]);

    flatTableRow["Pesticide_spray_day"] = int.Parse(finalsplit3[0]);
    flatTableRow["Pesticide_spray_Month"] = int.Parse(finalsplit3[0]);
    flatTableRow["Pesticide_spray_Year"] = int.Parse(finalsplit3[0]);

    DataTable_Time.Rows.Add(flatTableRow);

    i++;
}

con.Open();

using (SqlBulkCopy s = new SqlBulkCopy(con))
{
    s.DestinationTableName = DataTable_Time.TableName;

    foreach (var column in DataTable_Time.Columns)
        s.ColumnMappings.Add(column.ToString(), column.ToString());

    s.BulkCopyTimeout = 500;

    s.WriteToServer(DataTable_Time);
}

回答1:

It's important to keep in mind sqlBulkCopy columns are case sensitive for some versions of SQL. I think MSSQL 2005. Hope it helps

回答2:

One reason is that SqlBulkCopy is case sensitive. Follow steps:

1. Find your column in the source table by using Contains method in C#.
2. Once your destination column is matched with the source column, get the index of that column and give its name to SqlBulkCopy.

For Example:

//Get Column from Source table 
string sourceTableQuery = "Select top 1 * from sourceTable";

// i use sql helper for executing query you can use corde sw
DataTable dtSource 
    = SQLHelper.SqlHelper
        .ExecuteDataset(transaction, CommandType.Text, sourceTableQuery)
        .Tables[0];

for (int i = 0; i < destinationTable.Columns.Count; i++)
{
    string destinationColumnName = destinationTable.Columns[i].ToString();

    // check if destination column exists in source table 
    // Contains method is not case sensitive    
    if (dtSource.Columns.Contains(destinationColumnName))
    {
        //Once column matched get its index
        int sourceColumnIndex = dtSource.Columns.IndexOf(destinationColumnName);

        string sourceColumnName = dtSource.Columns[sourceColumnIndex].ToString();

        // give column name of source table rather then destination table 
        // so that it would avoid case sensitivity
        bulkCopy.ColumnMappings.Add(sourceColumnName, sourceColumnName);
    }                               
}

bulkCopy.WriteToServer(destinationTable);
bulkCopy.Close();

回答3:

1. ENSURE to provide a ColumnMappings

2. ENSURE all values for source column name are valid and case sensitive.

3. ENSURE all values for destination column name are valid and case sensitive.

4. MAKE the source case insensitive

回答4:

The problem is with the s.ColumnMappings.Add(column.ToString(), column.ToString()); and mapping of your destination and source tables. At least one of the columns in your DataTable doesn't match the destination table.

There are many reasons but one of them can be datatype mismatch. So if you try to insert text into an integer column.

回答5:

I had this same error and it turned out to be that I was mapping to a column that did not exist in my destination database. Make sure that your columns do exist if you are going to map them.

回答6:

In my case, I added a column twice to the ColumnMappings. I removed the duplicate and everything worked ok.

回答7:

For other people with the same error (but not applicable in this case, as SqlBulkCopy gets newed up), another cause may be if you are trying to reuse a SqlBulkCopy instance for multiple operations, as the column mappings will persist from operation to operation. In which case, instantiate a new instance of SqlBulkCopy for each operation that requires different column mappings.

回答8:

I also faced the same issue. In my case, I was getting the Logs table generated from seriLog and it was missing the additional columns in the table creation which was causing the above error.

Once the create the logs table on my own with the required additional columns, this error went away.

回答9:

Other than the case sensitivity mentioned in the various answers above. Check that you actually have the same columns and you have not missed any by chance. This happened to one of my colleagues and he was missing one column out of 87 columns. So just double check you have every column from source in your destination as well.

回答10:

In my case, it was the wrong database name in "Initial Catalog=" in the connection string.

回答11:

I ran into the same error. For me, I was accidentally mapping 2 different columns from the source datatable to the same column in the target database table.