Column does not allow DBNull.Value

Question

I've noticed that after running the following code, I receive a 'column X does not allow DBNull.Value' error

I've been considering adding an if else statement which relates to a column being null or white space, in order to populate a NULL string field with "NULL".

As somebody brand new to development, who did not write the original code, I was curious to know if this is a solution that you would agree with or whether there is a better/simpler way to eliminate the DBNull error?

using (sqlConn)
            {
                sqlConn.Open();

                dropUpdateCmd.ExecuteNonQuery();

                createUpdateCmd.ExecuteNonQuery();

                using (var bulkCopy = new SqlBulkCopy(sqlConn))
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    }

                    bulkCopy.BulkCopyTimeout = 600;
                    bulkCopy.DestinationTableName = "Destination.GenericTable";
                    bulkCopy.WriteToServer(dt);
                }

                mergeCmd.ExecuteNonQuery();

                dropUpdateCmd.ExecuteNonQuery();

                sqlConn.Close();
            }

No no no, don't put the string value "NULL" in place of actual NULLs. That's data mangling. — madreflection, Jan 06 '22 at 21:37
Please ad your source DataTable. That's probably where the issue is. — ojonasplima, Jan 06 '22 at 21:37
If the column doesn't allow null but you're trying to set null, seems like some input validation is in order. What should happen if the source is null but the target does not allow null? Please don't put "NULL" as a string, that's terrifying =) — Nikki9696, Jan 06 '22 at 21:39
@charlotte.emily: Did you know that there are actually [people with the last name "Null"](https://www.bbc.com/future/article/20160325-the-names-that-break-computer-systems)? Don't make their lives harder. — madreflection, Jan 06 '22 at 21:40
@madreflection, that's no problem. Just do a case sensitive comparison and you are good to go. :D (it's a joke, of course) — Andrew, Jan 06 '22 at 21:47
check this answer https://stackoverflow.com/questions/27027013/sqlbulkcopy-into-table-that-default-column-values-fails-when-source-datatable-ro/27044891#27044891 — Obada Saada, Jan 06 '22 at 21:47
@Andrew: Funny. It doesn't change the fact that the data is mangled. What scares me is that someone might miss the ":D" at the end and takes you seriously. — madreflection, Jan 06 '22 at 21:49
There's also this answer that might help. Though it's about dates there's some interesting info in it about how bulk copy might blow up for certain situations https://stackoverflow.com/questions/54115837/column-does-not-allow-dbnull-value-no-keepnulls-proper-column-mappings — Nikki9696, Jan 06 '22 at 21:49
You have a table that does not allow nulls, but your data has nulls... Consider modifying your `Destination.GenericTable` table to allow nulls in those columns. Either that or make sure your initial datatable `dt` contains actual useful data in the datacolumns — Cleptus, Jan 06 '22 at 21:50
agree with Cleptus. Examine the `dt.Columns` collection for which ones have AllowDbNull false — Caius Jard, Jan 07 '22 at 00:26

score 0 · Answer 1 · answered Jan 06 '22 at 22:19

It is always best to put in the real data. So if it should be NULL and not some other value then you need to talk to whoever is in charge of the database to get it changed. However, I would assume that NULL is not being allowed for a reason. In either case, if you are not sure I would recommend that you talk to someone that knows how it was intended to be. If the table not allowing NULL was intended as I assume it was then we can assume that the data you are trying to insert is not supposed to contain NULL and measures should be taken to either take out the NULL values, change them or prevent them from being added the first place.

Column does not allow DBNull.Value

1 Answers1