Recently while working on an application created in Visual Studio 2005 (also applies to 2008) on the .NET 2.0 framework I noticed a strange error when running with live customer data. The error was, “Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints”:
And if you’re debugging with all errors thrown, you’ll see something like this:
This error is caused because the constraints of the strongly typed dataset are such that one or more columns contains an invalid value. In this case a customer table had a null value in a foreign-key column which links it with a customer_type / category table. The system had fallen over when calling the TableAdapter Fill method for the customer datatable.
Although the underlying database allows nulls in this column, Visual Studio’s much promoted dataset designer (still in 2008) doesn’t allow the developer to allow null values in any other data type than a System.String.
For example on a product I am currently working on which uses Visual Studio 2008, .NET 2.0 and targets a local Access .accdb data store I have a table called “Folders”. This table is meant to allow the user to customise a Treeview to display a set of hierarchical information. The table has a column “FolderParent_ID” which is a foreign key integer value to the ID of the same table and every “folder” optionally is a child / has a parent. So what do we do with folders that don’t have parents? Leave the FolderParent_ID as null. Only because of this bug in the Dataset Designer, we are forced to throw an exception each time a null is encountered. Result? One very frustrated developer and countless confusing error messages.
The table definition in Access 2007
Note: “Required” == “Do not allow nulls” == No == Allow Nulls
Null values were incorporated using the System.Generics namespace in the .NET 2.0 framework for all datatypes, but someone appeared to have forgotten to tell the Dataset designer team, because when you try to change the NullValue property to anything other than (Throw Exception) you get a Properties Window warning and the property returns to (Throw Exception):
Property value is not valid
The value entered isnot valid for the current data type
If your DataType is a System.String you can use the return: (Empty) or return: (Nothing) value in the designer. But this won’t do, since we’re using strongly typed datasets so that our types and rules are preserved. It seems that adding a table to the Dataset Designer that uses non-string/character columns which allow nulls confuses the DataSet Designer and causes it to assign unwanted exception behaviour to your columns rather than simply returning the nulls. It’s not just Access either. I’ve tested this with SQL Server 2005 & SQL Server Express with the same results. There is absolutely no reason for this and Microsoft have had since November 2005 (release of .NET 2.0) to fix this problem and implement nullable non System.String datatypes in the Dataset Designer.
The first workaround I tried was to edit the code that the Dataset Designer creates in order to check for, and handle null values. This only works until you open the Dataset Designer again. Once you open the Dataset in the Dataset Designer you lose all your hard earned customised code as the Dataset Designer will overwrite your changes each time it is used. Nice one, Microsoft.
You could create your own partial class but this opens up a whole new can of worms. Before you waste your time, it won’t work. Try it and see.
And yet there is hope; open your DataSet .XSD file manually (you can use the Visual Studio XML editor to do this) and find your data column expressed in XML as an element. Add the values: msprop:nullValue=“-1” nillable=“true“. This will cause your column (in this example, integer) to return “-1” in place of the null value and not throw an exception. Amending the XSD directly appears to persist (i.e. not lose the amendment) and seems to do the job ok.
<xs:element name=“FolderParent_ID” msprop:nullValue=“-1” nillable=“true” msprop:Generator_UserColumnName=“FolderParent_ID” msprop:Generator_ColumnVarNameInTable=“columnFolderParent_ID” msprop:Generator_ColumnPropNameInRow=“FolderParent_ID” msprop:Generator_ColumnPropNameInTable=“FolderParent_IDColumn” type=“xs:int” minOccurs=“0” />
If you’re a software developer you have probably found this page by ferociously Googling for the answer to your problem. Please leave me a comment. I’d be interested to find out if you’ve got a better solution for handling nulls or if you can improve on my workaround above. I’d like to be able to return a correct System.DBNull value rather than a hacked “-1”, so if you know how this is done please drop me a comment.