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

c# sqlbulkcopy sql-server

Question

SqlBulkCopy, which I'm attempting to utilize to transfer data into a SQL database table, is reporting (erroneously) that the columns do not match. They do coincide. The names of the columns that are being mapped are right when I use a breakpoint to see them. The name of the column is shown in the error notice, and it is accurate.

This is how I work. The only difference between my way and does's is where the column names come from. But the columns' names' respective strings are EXACTLY the same.

    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 works, almost same approach is:

    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;
        }
    }

The column names are: ACT Code, ACT Paid, ACT Name, ACT Terminal Code, ACT TCustom1, and ACT TCustom2, if that is any assistance. In the database itself, they are precisely the same. Since the column names are right, I am aware that SqlBulkCopy mappings are case sensitive.

The error message is as follows:

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.

I hope I'm simply missing something apparent since I'm completely confused at this point.

Thank you.

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.

1
6
8/24/2015 8:08:57 AM

Popular Answer

MSDN (here) claims that theDataColumn.ToString() returns "If the property is set, the Expression value; if not, the ColumnName property." when used.

I've always discovered theToString() approach to be weird anyhow (may alter depending on the state or circumstances), therefore I'd suggest utilizing theColumnName instead of property, since that is what you are genuinely attempting to escape fromToString() .


In the event that it doesn't work, I'd have to assume that there is an issue with the source datatable's case-sensitivity in the names of the columns, asSQLBulkCopy is case-sensitive despite the SQL DB not being. To fix this, I would suggest returning/using the actual text from the datatable's column list itself, rather than using whatever string was handed in, when you check to verify whether that column exists. Any case or accent discrepancies that your ColumnsExist routine may have missed may be fixed with this.

3
8/19/2015 7:53:06 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow