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.