SqlBulkCopy dbnull.value error

Question

I am trying to insert rows from an excel file into sql server 2000 using bulkcopy. In the table there is a 'rowguid' field and its default value is set to (newid()) and cannot except null values. Also RowGUID is set to "Yes".

In my code i remove the column mapping for rowguid . Here is my code.

if (dr.HasRows)
{
    using (SqlBulkCopy bulkCopy =
        new SqlBulkCopy(sqlConnectionString))
        {
           bulkCopy.DestinationTableName = "configtest";
           SqlBulkCopyColumnMapping value = new SqlBulkCopyColumnMapping("rowguid", "rowguid");
           bulkCopy.ColumnMappings.Remove(value);
           bulkCopy.WriteToServer(dr);
         }


 }

I get column 'rowguid does not allow dbnull.value

definition for that field is : rowguid , uniqueidentifier, allow nulls unticked.

Show us the error message please. The table definition would be nice too. — Rui Jarimba, Aug 20 '13 at 07:18

Rui Jarimba · Answer 1 · 2013-08-20T07:32:15.240

0

Instead of

bulkCopy.ColumnMappings.Remove(value);

Try

bulkCopy.ColumnMappings.Add(value);

edited Aug 20 '13 at 07:32

answered Aug 20 '13 at 07:25

Rui Jarimba

11,166
11
56
86

Dont see a difference in answer. – Lucky Luke2 Aug 20 '13 at 07:35

score 0 · Answer 2 · answered May 09 '16 at 09:17

You have to remove the mapping that is referenced. So if you already have ColumnMappings you have to do it like this:

var mapping = bulkCopy.ColumnMappings.Cast<SqlBulkCopyColumnMapping>()
            .Single(x => x.DestinationColumn == "rowguid"));

bulkCopy.ColumnMappings.Remove(mapping);

SqlBulkCopy dbnull.value error

2 Answers2