How to solve “SQLBulkCopy column does not allow dbnull.value” error

I had a recent need of populating a bunch of rows in a database table. Instead of doing the usual For Each… Insert routine, which is slow as heck, I tried the SQLBulkCopy.WriteToServer method.

I’ve prepared a datatable as my copy source, but when it tried to execute .WriteToServer method, I came across an exception “column ‘****’ does not allow dbnull.value”

I looked into my datatable and saw nothing wrong! After doing some Googling and Stackoverflowing I found out my source datatable I’ve prepared came from copying a dataset tableadapter’s schema, which had different column order than the actual database table’s, because I’ve made updates to the database table, but the column order got screwed up when tableadapter got re-configured.

Here is a write-up on how to mirror the tableadapter’s column order to the database’s datatable.

Get the order correct, then this exception should go away.

How to reorder dataset columns in Visual Studio designer

I use tableadapters whenever I need to talk to a SQL database.

There will be times when I needed to add/remove a field in my table and whenever you “refresh” the tableadapter, the newly added field tend to become the last column in the column order. For example, I added step_type_id field into my existing table:

Database Diagram in Microsoft SQL Server Management
Database Diagram in Microsoft SQL Server Management
Dataset designer in Visual Studio

As you can see, I inserted a new column where I want it to be in tblTreeStep in MSSQL Server Management, and instead of doing the same in Visual Studio’s dataset designer, I take the lazy man’s approach of updating my tableadapter by going thru “Configure” again (I just don’t like doing duplicate work of adding the same column in my dataset designer, setting the type, nullable properties again).

When you do that, Visual Studio tags on the new columns at the end of your table, thus not reflecting the true column order of your actual database table.

A quick way to fix this would be to delete all columns but your 1st column (in this example, my obj_id column), go through “Configure” again and add back all your columns.

Your dataset tableadpter should take on the same column order as your database table’s.