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:
StrongTypingException was unhandled
The value for column ‘FolderParent_ID’ in table ‘Folders’ is DBNull
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 Dataset Designer
Column properties shown (see NullValue on the right)
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
NullValue
(Throw exception)
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.
Workaround
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.
Example
<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” />
Comments Please
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.
Mate just go dataset.EnforceConstraints = false
Worked for me.
But that turns off the dataset constraints, a bit like doing a “On Error Resume Next” in VB6, it can cause quite a lot more problems than it solves.
Thank you very much for this, I thought we were doing something wrong with the dataset designer, turns out it is a ‘feature’… ><
Reassuringly, Microsoft have been aware of this since VS2005, and have blogged on it:
http://blogs.msdn.com/smartclientdata/archive/2005/08/13/NullValueForTypedDataSet.aspx
I can’t believe (well I can, but you know what I mean) that this hasn’t been fixed, good database design surely relies on the correct use of null for optional FK relationships. Our DBs use them everywhere as the alternative (creating zero value rows) is terrible (and wrong IMO).
We tried this and it didn’t work for us in VS2008 – the property was correctly changed in the designer ‘NullValue’ combo, but the field (column) we had adjusted in the xsd was no longer available as an intellisense option and the code wouldn’t compile.
However, we then discovered that if you declare a data table row object, and have set the ‘AllowDBNull’ to true in the designer (without having changed the xsd), then you will find that VS2008 automatically generates a IsNameOfFieldNull field (where NameOfField is whatever the column with the nulls in is called).
You can then just use this field to test for nulls and handle appropriately, and most importantly this won’t throw an exception, even though the behaviour is still set to ‘throw exception’ in the null values column.
I’m not sure this will help in your scenario, but it worked for ours (we were looping the datatable in code and building a dynamic webpage menu)
Hope that helps someone.
Hello Tom,
Thank you for your kind words, and especially for bringing up the .IsXNull property.
I’ve noticed that property too, on strongly typed datasets. But in my particular case I was using databound controls.
Unfortunately the binding fires before you can check for .IsXNull, so where you might have null values in foreign key relationships and you’re using databinding, you are effectively screwed.
Microsoft provide data binding and go to the extreme lengths to provide masses of support for this rapid application development feature, but then screw us all with the error handling and event order. In actual fact the only real solution is to trap this error and clear it. But then it makes the project a pain to debug properly because these dataset errors keep being thrown when you don’t want to know about them.
I really hope Microsoft fix this. It’s absolutely and clearly not a feature.
Thanks again, Tom.
Eureka!
I struggled quite a bit with this today when I finally found the answer:
I have an Item Table and a foreign key called ManufacturerID, which was giving me exactly the same problem. The answer is to retrieve the record in a Row and then test it for null as you can see below:
:
:
ItemTable = ItemAdapter.GetItemByID(InventoryItemTable.Item(0).ItemID)
ItemRow = ItemTable.Item(0)
If Not (ItemRow.IsModelNull) Then
ItemModelLabel.Text = ItemTable.Item(0).Model
End If
If Not (ItemRow.IsManufacturerIDNull) Then
ManufacturerTable = ManufacturerAdapter.GetManufacturerByID(ItemTable.Item(0).ManufacturerID)
ItemMfrLabel.Text = ManufacturerTable.Item(0).ManufacturerName
End If
:
:
I hope this helps!
I’ve been struggling with this and kinda found a work around. I could have gone with .IsNull(), but I had lots of instances of decimal and datetime nullable fields in my table. So I didn’t want to do a lot of:
——————————————
with myCollection
if .IsNull() then
else
end if
if .IsNull() then
else
end if
….
….
end with
——————————————
statements for each field.
Instead, this is what I did:
——————————————-
Private Sub someSub()
Dim tbl_adapter_Collections As New DataSet1TableAdapters.p_GetCollectionbyIDTableAdapter()
Dim tbl_Collections As DataSet1.p_GetCollectionbyIDDataTable = tbl_adapter_Collections.GetData_CollectionbyID(DropDownListAppName.SelectedValue)
Dim myCollection As DataSet1.p_GetCollectionbyIDRow = tbl_Collections.Rows(0)
with myCollection
txtField1.text = dsNullCheck(myCollection, “myFieldName_1”)
txtField2.text = dsNullCheck(myCollection, “myFieldName_2”)
txtField3.text = dsNullCheck(myCollection, “myFieldName_3”)
…
…
end with
End Function
Private Function dsNullCheck(ByVal _appsCollection As DataSet1.p_GetCollectionbyIDRow, ByVal fieldName As String) As Object
Dim _tmpVar As Object
‘Gets the value of current record through its fieldname and this doesn’t throw an exception
_tmpVar = _appsCollection(DirectCast(DirectCast(_appsCollection.Table, System.Data.DataTable).Columns, System.Data.DataColumnCollection).Item(fieldName).Ordinal)
If IsDBNull(_tmpVar) Then
Return “”
Else
Return _tmpVar
End If
End Function
——————————————-
Hope this helps!
Howdy,
Found this while searching. I found it easier to modify my SQL query to something along ISNULL(MyColumn, -1) than to modify the generated code which seems pretty fragile to me.
Best,
Ben
Ah, Null, we love you and hate you.
Data typing in general is a fabulous issue. I’m building something that is trying to be ultra flexible, and I started out thinking that I would use some sort of database underneath it all, started messing with ADO.net etc. , but quickly realized the data-type dictatorship (null or not-null being a key tool of the secret police of the regime) permeates the database and programming world. I ended up (and it remains to be seen if it was a horrible mistake) deciding to avoid all of it, and I store data as objects, so they are nullable, and can be… well, anything. So far so good, although I must admit making sure that null and type is handled gracefully all the time does take work. 🙂
My suggestion is do NOT use datasets, they appear really cool, neat and easy to use at first but then become horribly cumbersome as your application matures. I am using LinQ with Strong Entities that are similar to Datasets visually but allow Null Integers. However because they are similar I worry.
Hi Louis,
Datasets are very useful, but I do agree that they can become cumbersome. Business objects are far more maintainable. Although ultimately, you need a data layer and Datasets still have their place.
An Entity Relationship Model (if that’s what you mean as strong entities), is pretty cool. I’m experimenting with them in one of my new projects.
Peace 🙂
Only three votes, come on guys and girls – if you like the article, rate it * * * * * 🙂
If you were helped by the article, a submission to Digg (use the shareit link on the article) would go a long way to repaying my work 🙂
Thanks for all the great posts everyone. I have tried the various workarounds here and have not been ‘fully’ successful. I did put the msprop:nullValue=“-1” in my XSD file but later regenerated the method and the change was gone. Since you can set the value in the properties for string types only, those are not a problem. My workaround has been to enhance my SQL code to check for a null value and to pass out a -1 that I trap for later in my application code. Hope this helps.
Let’s hope Microsoft fixes this in the next release of Visual Studio.
Having decided to migrate from access, I’ve only just started using vs and the learning curve is realy killing me. This Null thing has taken an aggregate number of days and I’m still NULL the wiser. Well maybe in the failings of isdbnull, but this isnlt the durection I wanted to take. Why oh why is something some basic in other langauges so f*&%$ difficult using vs.
I’ve also to my utter frustration found that If I set the visible property of a textbox to false I can no longer retrieve, from code, any data that it might contain.
What the hell’s going on at MS, have all the cleaver people died off!
Hi there Smalltime,
It sounds like you’re using data binding.
You might want to try checking the data on the textbox by interrogating the bindingsource object instead of the actual form field. Although I thought that even if invisible you should still be able to check the value of an .visible=false textbox.
Once you’re over the learning curve it is worth it but in the meantime I can share in your frustration. The various commenters have some different solutions to the NULL issue which you could explore, depending on what’s best for you.
Best,
Mike
You are of course right about looking for data in the underlying source rather then throwing it on the form then trying to reference it. However, the null thing is making this a little more difficult then I expected. Also F1 help is so cryptic that I end up swallowing MAX painkillers and the left side of my chair is now showing distinct scratch marks inflicted out of F1 frustration.
I used a linq to dataset to access the current row (as described in the VB How to Video’s by the lovely Beth) but then trying to check for nulls in the same current row is, at the moment, proving a task to far. I’ve now resorted to the final plan, behind closed curtains I’ve started reading the VB 2008 for Dummies book and oddly enough find it quite useful, albeit slow.
Do you think it’s ever going to be possible that after spending so many years at honing their skills writing gibberish that MS might one day write a help file in plain English?
NB
To anyone at MS that might come across this, please, please consider spending a little time on making your helps helpful.
Oh yes, I’ve tested and tested again .visible = false textbox.text can’t be referenced. Well at least not in the conventional way
Hello,
Is it possible to create a custom strongly typed dataset designer and code generator ?
As an add-in maybe ?
It is very stupid that after 4 years since Visua Studio 2005 was released, Microsoft does not allow to set the field’s (NullValue) property to Null for anything else than strings ?!
Hello,
did someone try to workarond that issue by writing a pre-build
event to modify all the “Add…Row” and getter/setter in the
DataSet’s “.Designer.cs”?!
Best regards, Uwe
Hi,
I went for your workaround mike, but then I realized that there was a way to not have a workaround but a more reliable solution maybe as we won’t fear to loose our changes:
Instead of changing the code in the xsd and adding msprop:nullValue=“-1” nillable=“true” , we can just set the value -1 to the NullValue parameter via the designer. The difference is that we don’t have this part added in the xsd: nillable=“true” . But for me it seems to be working the same.
Am I missing something then?
Regards,
Julien
Hi,
i did not have that problem although i’m using data binding on winforms including binding sources and dgvs.
I ‘ve set the AllowDBNull = true in the XSD Designer and set the NullValue to -1. That’s a funny thing: MS does not allow the predefined valued but allows customized expressions. Well then…:)
When using the dgv i’ve no problems when i load and store the data using generic table adapters with provider factories.
Nevertheless, ms should fix that otherwise one would not really suggest to enable constraints in strongly typed ds.
Future dev would use Linq or the new entity framework in .net 4.0.(new one)
KR Chris
This is maddening. Seems like the best way to use it in a datatable is to just not allow null and set it to something else.
What about changing the datatype to a string, returning an empty nullvalue, checking for an emptrystring, and if its not empty, cast it back to what you want it to be (e.g. a date)?
On the table being filled by the fill method of the adapter (dt_SampleTable) use the “contraints.clear” method.
ex. dt_SampleTable.Contraints.Clear();
(using c#)
and it continues….. In 2010, there was Visual Studio 2010 and lo, it had many errors. One of which is this, still.
Ugh, I hate datasets like I hate Crystal Reports.
For a textbox that is bound try this:
TextBox1.Databindings(“Text”).NullValue = “”
I haven’t worked out how to do it with comboboxes in Datagridviews but the above works with textboxes.
I realise this is a bit off to the side but it is somewhat related.
This is absolutely not a bug from MS, and a feature.
The reason you can set the value to NULL is that the DataType of the row in this dataTable is System.Int32 by default, which does not allow NULL value. The expected way to do it is to change the DataType of a column where a NULL value is possible in the database to the dataType: System.Object, and set the NullValue attribute to (Null). This way, in your C# code, when you consume this field, you declare the variable of type, Nullable, and a possible NULL value of the variable will be passed into and from the dataSet.
A lot of programmers take the default settings for granted, and get all the crashes because of this. So I suggest MS to change the default setting to: Datatype: System.Object, NullValue: (null).
Sorry, I’m saying in my post above, The reason you can NOT set the value to NULL is that the DataType of the row in this dataTable is System.Int32 by default, which does not allow NULL value. ..
I’m having the same problem and need help finding a solution. But using VS2005, there isn’t a System.Object????
Leon thanks but thank you very much helped me a lot of your comment … two days of head pains have eased … 🙂
Mike – yes MS have made this so needlessly difficult. It seems not to be documented anywhere and the only progress I’ve made has been via hours of research on the web and trial and error within VS. Because you are binding directly to a dataset, you are actually conflating two different issues – moving nulls between the dataset and the database, and moving them between the ds and the bound control. I’m using business objects, so I see these as two separate steps.
Leon – you’re actually being too forgiving of Microsoft; yes, if you do as you suggest and change the type within the ds to system.object, you can then write the db null value into a property of type nullable of(T). If you look at the code that the datasetgenerator has produced for you, in the designer.vb file, you’ll see that it has used the IsXnull to achieve this.
Furthermore, if you look at the tableadapter Insert code produced by the dataset generator, you’ll see that it uses .HasValue to seamlessly cope with updating from a nullable type.
Therefore the generator is aware of nullable types, and it could use the same kind of code to pull data into them – but it doesn’t. Maybe the fact that AFAIK nullable types are part of the language and not the underlying .net framework is the reason behind this.
However, although it’s clumsy, the IsXNull does give us a way to turn a dbnull in the database into a nothing in our object; and the tableadapter db-direct Insert /update methods do the same in the other direction – so that’s problem 1 sort of solved.
Turning to problem 2, the databinding to nullable types: the ‘null value’ setting in the ‘advanced’ databinding dialog only works one way – from the var to the control. Lets say you have an object with a property type of nullable (of date) and leave the ‘null value’ property blank. When displaying a value of nothing, the textbox is blank as it should be. You can also use text – eg UNKNOWN if you wish. However, you’ll find that it doesn’t round-trip – you can’t display a date for editing, then delete it (or overtype UNKNOWN) and have it save back to the object as Nothing. The only way I’ve found to make this work is to add a line of code like this for each control, to run when the form loads:
AddHandler _txtdisb_8.DataBindings(0).Parse, AddressOf NullableParser
and the nullableparser itself is simply this:
Sub NullableParser(ByVal sender As Object, ByVal e As ConvertEventArgs)
If e.Value = “” Then e.Value = Nothing
End Sub
and this sits in a module of code that I share between most of my projects.
Well I hope something in this long post will help save someone else the hours I’ve spent on it!!
pg
I noticed that this work-around becomes null and void (no pun intended) when the data type in SQL Server 2005 is “tinyint”… which can only contain values from 0 – 255.
You can actually over-type the “(Throw Exception)” in the NullValue field with this -1 value in the designer rather than having to go through the XML.
Just don’t try it on tinyints as it will wipe your datasource (as I no-doubt found out the hard way). If this does happen to you (as it has me) then dig your M$ deleted Dataset.Designer.## file (vb or cs) our of the Recycle bin and place it back in the project source folder.
The DataGridView handles nulls internally and can substitute a string when a bound column value is null.
To get programmatic access with a stongly-typed dataset, there was a workaround suggested on connect.microsoft.com by Danila Korablin in the article, Nullable types for typed dataset (https://connect.microsoft.com/VisualStudio/feedback/details/284009/nullable-types-for-typed-dataset).
Basically, open the context menu of your table in the DataSet Designer, select View Code. This opens the xxxDataSet.cs file in a partial class named for your data table. Because from VS2005 on, datasets are implemented as partial classes, the “code” file can be customized to extend the classes. In the case of nullable columns, you can add properties that have the correct types. Nullable columns can be handled as properties in the appropriate Row class (initially, edit the class name from xxxDataTable to xxxRow).
For example, I have a Product table with a nullable CommentDate column of type DateTime. I can create a property, NullableCommentDate in my ProductRow class, that returns and accepts null as a valid value from my code. Example:
partial class ProductRow
{
public System.DateTime? NullableCommentDate
{
get { return (this.IsCommentDateNull() ? null : (System.DateTime?)this.CommentDate); }
set { if (value == null) this.SetCommentDateNull(); else this.CommentDate = (System.DateTime)value; }
}
}
Now, from my event handler for a DataGridView, for example, I can read and write null values as expected:
// If the user added a comment, default timestamp to today.
System.Data.DataRowView viewRow =
(System.Data.DataRowView)view.Rows[e.RowIndex].DataBoundItem;
myDataSet.ProductRow row =
(myDataSet.ProductRow)viewRow.Row;
if (row.NullableCommentDate == null) row.NullableCommentDate = DateTime.Now;
The advantage of doing it this way is that the DataSet Designer won’t overwrite changes made here. It’s a royal pain to have to do this, but it works.
Thanks, it works (even in 2013)! Actually, I looks quite clean as compared to many other workarounds 🙂
BRA-VO Suncat! You saved the day with this nullable-nightmare!!
Suncat, thanks for you contribution. that’s the way we followed and it sure works!
Check this guys:
//row is datarow
//MyCol is a column from row
if (row.IsMyColNull())
{
…
}
Another quick hack workaround:
private static void AllowNulls(DataTable table_)
{
foreach (DataColumn col in table_.Columns)
{
if (!col.Unique)
{
col.AllowDBNull = true;
}
}
}
I’m not that much of a online reader to be honest but
your sites really nice, keep it up! I’ll go ahead and bookmark your website to come back later. Cheers
It’s very easy to find out any matter on web as compared to textbooks, as I found this article at this web page.
With this kit, you will be getting the Ugg protector, the shampoo,
and a sponge that has been designed especially for
cleaning your Uggs. Buying Uggs on the internet can be
a little more complicated. The sheepskin breathes really
well plus the air areas formed subsequent for your leg by the
looseness of the wool indicates that your lower leg and foot is going to be comfy in the
boot even in warm weather.
Thank you for this post and for all the comments. It was all very useful to me and saved me a lot of grief.
Cheers,
.pd.
It’s not intuitive but the properties window has always allowed you to simply type the desired null value into the NullValue field. Most people think it is just a drop down and see the error occur when they choose (Empty) or (Nothing) for a System.Int32 column. But you can actually type a value instead. I usually type -1 for my NullValue on System.Int32 datatype columns. I have yet to find a similar workaround for dates though.
Cheers.
I was wrong about the date. You can type in a date but I just want to be able to enter an empty string or something for null dates.
Thanks it works for me.