Wednesday 10 March 2010

Filtering a DataSet for a DataGridView: DBNulls & StrongTypingException

So... if you got here because you have a DataSet as the DataSource for a DataGridView in .NET and you are trying to filter the DataSet using LINQ... or some other method and it keeps returning this error:
The following Exception occurred in the DataGridView:
System.Reflection.TargetInvocationException: Property accessor 'fieldname' on object 'dataset.myDataSet+qryTable' threw the following exception: 'The value for 'fieldname' in table 'qryTable' is DBNull.' --> System.Data.StrongTypingException: The value for column 'fieldname' in table 'qryTable' is DBNull' --> System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'String'. blah blah blah....
The StrongTypingException text is explicitly set in myDataSet.Designer.cs and has been automatically generated by the DataSet Designer in Visual Studio:
throw new global::System.Data.StrongTypingException("The value for ... etc");
The code for 'fieldname' tries to get the value from the fieldname column in the query's DataSet table and finds a null. When it converts it to a string it fails and the InvalidCastException is caught by a Try..Catch which then throws the StrongTypingException...

So how do I change it...? Open myDataSet in Designer, and click on the offending fieldname in qryTable. In Properties, there is a property 'NullValue'. There are 3 values:
  • (Empty)
  • (Null)
  • (Throw Exception)
I had been setting string values to '(Empty)'. However for integer, date and boolean values and so on, you cannot select any of the options except '(throw exception)', the default...

Today I needed an answer... and discovered that you can type '0' (zero) into the NullValue property box for integers or boolean. For dates you can enter the date in AllXsd date format: 1900-12-20T20:30:59.8999 (for example).

When each column's NullValue has been set, you will no longer see the StrongTypingException.

Unfortunately, I went through all my fields and set them to something other than 'throw exception' and then all sorts of bugs starting appearing in my code... the table could not be found in the dataset - I had to re-create the dataset... maybe that was a bug... luckily it was just a small dataset...

Now I have columns that are partially filled with zeroes... but I'm sure we can hide them...

It's a shame that the Designer can't let us use Nullable ints, dates and booleans etc.. that would solve the problem too...

No comments:

Post a Comment