CODEWITHSUNDEEP
c#sqldatatable
Wilrod
Wilrod

Reputation: 47

Using SqlbulkcopycolumnMapping to copy data from datatable to sql database

I'm trying to map the columns from a Datatable to an SQL database.Table, while mapping the columns. But i keep getting this error

"System.InvalidOperationException: 'The given ColumnName 'FobID' does not match up with any column in data source.'"

see the code im using bellow.

using(SqlBulkCopy sqlcopyTFobs = new SqlBulkCopy(destCNS))
                {
                    sqlcopyTFobs.DestinationTableName = "[dbo].[TFobs]";

                    SqlBulkCopyColumnMapping mapFodID = new SqlBulkCopyColumnMapping("FobID", "FobID");
                    sqlcopyTFobs.ColumnMappings.Add(mapFodID);

                    sqlcopyTFobs.WriteToServer(dtTPositions);
                }

If i copy all columns using the following code all data is copied. But i need to only copy certain columns.

using(SqlBulkCopy sqlcopyTFobs = new SqlBulkCopy(destCNS))
                {
                    sqlcopyTFobs.DestinationTableName = "[dbo].[TFobs]";

                    sqlcopyTFobs.WriteToServer(dtTPositions);
                }

Upvotes: 0

Views: 806

Answers (1)

Yuri
Yuri

Reputation: 2900

Please be aware that column mapping is case sensitive. Some guidance you can get from MS site. Here is the short paragraph from there

Column mappings define the mapping between data source and the target table.

If mappings are not defined - that is, the ColumnMappings collection is empty - the >columns are mapped implicitly based on ordinal position. For this to work, source and >target schemas must match. If they do not, an InvalidOperationException will be thrown.

If the ColumnMappings collection is not empty, not every column present in the data >source has to be specified. Those not mapped by the collection are ignored.

You can refer to source and target columns by either name or ordinal. You can also mix >by-name and by-ordinal column references in the same mappings collection.

Looks like your mapping is not accurate.

If you are trying to remove some fields from being copied look at this article on stackoverflow Skip some columns in SqlBulkCopy

Upvotes: 1

Related Questions