Share via

Setting NullValue property for typed dataset

  • Article

 In Whidbey DataSet designer, you are able to set Typed DataColumn's NullValue property to the following values if the DataType is string

1 (Empty)                    

2 (Nothing) (null for C#)

3 (Throw exception)

4 any string you can type in.

The first three options are noticeable in the designer's property browser dropdown.

(Select a DataColumn and press F4 to show the property browser, select the NullValue property)

Apart from the dropdown options, you are allowed to type in anything directly to NullValue's combobox.

 

For example, you can type in NoValue for the NullValue property of Column1 and you will get this code generated in (code behind file XXX.designer.vb)

 

 Public Property Column1() As String

Get

If Me.IsColumn1Null Then

Return "NoValue"

Else

Return CType(Me(Me.tableDataTable1.Column1Column),String)

End If

End Get

Set

Me(Me.tableDataTable1.Column1Column) = value

End Set

End Property

 

 

Nice hum!

 

Unfortunately, users are not able to set values freely for other data types with the dataset designer. Therefore I wrote this FAQ as a workaround for the advanced users to set the NullValues as desired. We will look into this feature again in next version. 

 

Example 1:  If you have a Column, whose DataType is System.Data.DataSet. Now you want to set its NullValue to Nothing.

You can open the dataset file with a text editor (e.g. XmlEditor), find the column's msprop:nullValue and replace the value to msprop:nullValue="_null". If there is no msprop:nullValue attribute for the column, then add msprop:nullValue="_null".

 

The whole column would looks like this:

<xs:element name="Column1" type="..." msprop:nullValue="_null"   .../>

 

Save the DataSet file and verify that there is no build error and the code behind file is generated correctly.

 

Example 2: If you have a Column, whose DataType is Int32 and you want to set its NullValue to -1.

You can open the dataset file with text editor and replace or add msprop:nullValue with  msprop:nullValue="-1"

 

Warning: after you save the dataset file, if you get this error:

Custom tool error: Failed to generate code. Failed to generate code. Exception has been thrown by the target of an invocation.

That means your NullValue is not valid (For example, you set a NullValue to be "ABC" for a Integer type). You should modify the NullValue again to make it a valid value.

 

 

Note that these are the reserved values an you should use them appropriately when editing the dataset file through text editor.

 

msprop:nullValue="_empty"  -- (Empty)

msprop:nullValue="_null" -- (Nothing)

msprop:nullValue="_throw" -- (Throw exception)

 

msprop:nullValue="_throw" the default value and it is not persisted by default.

 

Hope this would help you in some advanced usages.

 

JohnChen (Smart Client Data Dev).

Comments

  • Anonymous
    December 27, 2005
    I talked with Steve Lasker in Orlando at length about how null values were handled in the new dataset designer. Although they are handled much better, there still is a lot of problems. For example, could you not make a setting that allows me to set the default null values for all tables in the dataset rather than one by one? The problem with the solution above is that once you go back in to modifiy something in the dataset, all the changes you put in for handling the null's are trashed and you have to re-do them all and hope you didn't make a mistake. Just seems like MS shouldn't always assume we want an exception thrown if a null is encountered and allow us, the developer, to define the default behavior.

  • Anonymous
    April 21, 2006
    When you build a datset by dragging SQL Server tables into it, the design of the dataset should pick up the default values for the columns from the SQL database instead of always setting the default to DBNull. If the SQL Server field allows nulls, the dataset field's NullValue property should be set to (nothing). This should happen for columns of all datatypes, not just strings.

    The way Microsoft has it now is ridiculous. The default value is DBNull, but if the field has a null in it and you access it, throw an exception? Whose idea was that?

  • Anonymous
    May 15, 2006
    The comment has been removed

  • Anonymous
    June 16, 2006
    When you do this the designer does not generate a strongly typed property for the columns you manually modified

  • Anonymous
    July 18, 2006
    thanks a million !
    Man how could they have been so &^^* ??!!?

    It worked in 2003 and now it is removed...

  • Anonymous
    August 03, 2006
    The comment has been removed

  • Anonymous
    August 26, 2006
    Very many thanks for a good work. Nice and useful. Like it!

  • Anonymous
    September 03, 2006
    The work around I used was not to use the strong type dataset and it works ok. But, I agree, Microsoft should fix this on the next bug release.

  • Anonymous
    September 05, 2006
    Please be sure and fix this glitch for VS2005 SP1. This renders an otherwise very usefull and timesaving tool completely useless in anything but the most basic scenarios. If you're trying to do RAD for small business saving countless hours writing ADO.NET code is EXTREMELY valuable.

  • Anonymous
    January 04, 2007
    If anybody else is having problems with null dates (as you can with .DBF files) you need to set the null value in the format of msprop:nullValue="0001-01-01T00:00:00+00:00". Is there any news on when/if we will be able to set other values for non string types, as this is causing a fair amount of work and as Peter Meyer has already pointed out any changes are overwritten when you make chages to the dataset and you need to do them all again.

  • Anonymous
    July 06, 2007
    As others have said, this issue makes an otherwise valuable tool virtualy useless.  I hope within the next few years it gets fixed.  

  • Anonymous
    August 29, 2007
    问题描述： 对于未定义为System.String的列，唯一有效的值是(引发异常)。 ForcolumnsnotdefinedasSystem.String,theonlyva...

  • Anonymous
    February 27, 2009
    I agree with everyone here. I've spent the better part of 2 WEEKS trying to get a null value to pass to the database into a datetime column that allows null values. This one issue has completely negated any RAD benefits that Visual Studio has afforded me in the past on my current project. I could have released my beta already but instead I'm having to reinvent the wheel here.

  • Anonymous
    October 12, 2014
    msprop:nullValue="_empty"  -- (Empty) msprop:nullValue="_null" -- (Nothing) msprop:nullValue="_throw" -- (Throw exception) how can set custom nullValue For Code Generation? how can this part customized via Partial Class Access ?