SqlBulkCopy - The given ColumnName does not match up with any column in the source or destination

c# sqlbulkcopy sql-server

Question

I'm trying to use SqlBulkCopy to copy data into an SQL database table however it is (wrongly) saying that the columns don't match. They do match. If I use a breakpoint to see the names of the columns being mapped, they're correct. The error message shows the name of the column, and it is correct.

This is my method. I have an identical method that does work and the only difference is where it gets the column names from. The strings containing the column names, however, are EXACTLY identical.

    public static bool ManualMapImport(DataTable dataTable, string table)
    {
        if(dataTable != null)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlBulkCopy import = new SqlBulkCopy(connection);
            import.DestinationTableName = "[" + table + "]";
            foreach (string s in Global.SelectedColumns)
            {                    
            /* The s string variable here is the EXACT same as
               the c.ToString() in the other method below */

                if (ColumnExists(table, s))
                    import.ColumnMappings.Add(s, s); 
                else
                    return false;
            }

            connection.Open();
            import.WriteToServer(dataTable); //Error happens on this line
            connection.Close();

            return true;
        }
        else
        {
            return false;
        }
    }

This is the almost identical, working method:

    public static bool AutoMapImport(DataTable dataTable, string table)
    {
        if (dataTable != null)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlBulkCopy import = new SqlBulkCopy(connection);
            import.DestinationTableName = "[" + table + "]";           
            foreach (DataColumn c in dataTable.Columns)
            {
                if (ColumnExists(table, c.ToString()))
                    import.ColumnMappings.Add(c.ToString(), c.ToString());
                else
                    return false;
            }

            connection.Open();
            import.WriteToServer(dataTable);
            connection.Close();

            return true;
        }
        else
        {
            return false;
        }
    }

If it helps, the column names are: ACT_Code, ACT_Paid, ACT_Name, ACT_Terminal_Code, ACT_TCustom1, ACT_TCustom2. These are exactly the same in the database itself. I'm aware that SqlBulkCopy mappings are case sensitive, and the column names are indeed correct.

This is the error message:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Additional information: The given ColumnName 'ACT_Code' does not match up with any column in data source.

Hopefully I'm just missing something obvious here, but I am well and truly lost.

Many thanks.

EDIT: For anyone happening to have the same problem as me, here's how I fixed it.

Instead of having the ManualMapImport() method be a near-clone of AutoMapImport(), I had it loop through the columns of the datatable and change the names, then called AutoMapImport() with the amended datatable, eliminating the need to try and map with plain strings at all.

Popular Answer

According to MSDN (here), the DataColumn.ToString() method returns "The Expression value, if the property is set; otherwise, the ColumnName property.".

I've always found the ToString() method to be wonky anyway (can change based on current state/conditions), so I'd recommend using the ColumnName property instead, as that's what you are actually trying to get out of ToString().


OK, failing that, then I'd have to guess that this is a problem with case-sensitivity in the names of the columns in the source datatable, as SQLBulkCopy is very case-sensitive even if the SQL DB is not. To address this, I would say that when you check to see if that column exists, then you should return/use the actual string from the datatable's column list itself, rather than using whatever string was passed in. This should be able to fix up any case or accent differences that your ColumnsExist routine might be ignoring.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why