DataTable.GetChanges() causes NoNullAllowedException when boolean column is NULL

[Aron van Ammers]

Hello all,

While writing this description of my problem I solved it... the classic case
of "try to explain your problem to someone else and you'll solve it along
the way". I'll still post the solution however, maybe it'll be useful to
someone else sometime

I have the following situation. A table in my SQL Server 2000 database,
let's call it "Person", has a column of type BIT called "DriversLicence".
The column indicates whether a Person has a driver's licence. For some
Persons in the database we don't know whether they have a licence or not,
therefore the column is nullable. All three values for the column occur in
the rows of the table: true, false and NULL.

A .NET application accesses the data in this database using a typed DataSet.
The typed DataSet contains an exact representation of the Person table. The
DriversLicence column is of type bool in the dataset. To set it to NULL in
..NET, DBNull.Value is used. Straightforward stored procedures are used for
selecting, inserting, updating and deleting data.

Reading the data with the BIT column works fine: when the DataSet is bound
to a standard DataGrid, records show up like they should: true gets a
checked CheckBox, false gets an unchecked CheckBox and rows with NULL show a
greyed out CheckBox.

Problems arise however when inserting or updating records which have the
DriversLicence column with a NULL value. This produces a
NoNullAllowedException:

System.Data.NoNullAllowedException: Column 'DriversLicence' does not
allow nulls.

The problem only arises when inserting or updating from the .NET
application. Setting NULL values for the column using the same stored
procedure in the SQL Server Query Analyzer works fine. Setting NULL values
using Enterprise Manager works fine as well.

So far the explanation of my problem. I found the solution when I wanted to
include a complete stack trace for the exception in this post. I wrongly
assumed it was DataAdapter.Update() that threw the exception, but it turned
out as follows:

System.Data.NoNullAllowedException: Column 'DriversLicence' does not
allow nulls.
at System.Data.DataTable.InsertRow(DataRow row, Int32 proposedID,
DataRow rowParent)
at System.Data.DataTable.InsertRow(DataRow row, Int32 proposedID)
at System.Data.DataTable.AddRecords(Int32 oldRecord, Int32
newRecord)
at System.Data.DataTable.ImportRow(DataRow row)
at System.Data.DataTable.GetChanges()
at MyApp.BaseClasses.BaseDataAccess.UpdateDataset(DataSet dataSet)
in c:\dev\basedataaccess.cs:line 1749

The dreaded line 1749 contained the following:

Console.WriteLine( "updating table {0}, rows changed: {1}",
myTable.TableName, ( myTable.GetChanges() == null ? -1 :
myTable.GetChanges().Rows.Count) );

Hence, this little adventure tells us the following: calling
DataTable.GetChanges() on a DataTable which has a boolean column and a
changed row which has a (Db)Null value for this column throws a
NoNullAllowedException. Don't use it or try ... catch properly

Best regards,
Aron van Ammers