Setting NullValue property for typed dataset


 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 (15)

  1. Pete Meyer says:

    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.

  2. Andy Magruder says:

    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?

  3. Jay Lindsay says:

    I am learning TableAdapters now.  My frustration has to do with null values.  Example code:

    Dim employeeAdapter As New ECTableAdapters.EmployeeTableAdapter
    Dim employeesAs EC.EmployeeDataTable
    Dim employeeAs EC.EmployeeRow
    employees = employeeAdapter.GetDataByID(“12345”)
    employee = employees.rows(0)
    Dim x as Object = employee.rating

    The last statement produces an error because rating is null in the database — very serious flaw.

    Could you tell me  the status of having a way to set NullValue for all variables – reference and value types.

    Thanks.

    Jay Lindsay

  4. Mitch says:

    When you do this the designer does not generate a strongly typed property for the columns you manually modified

  5. juulepuul says:

    thanks a million !
    Man how could they have been so *&^*^* ??!!?

    It worked in 2003 and now it is removed…

  6. Jason says:

    I absolutely agree with Andy.  The NullValue property set to throwexception drives me nuts.  First I spent the better part of a morning trying to figure out why I was getting an exception before my isdbnull function ran, then I spent the better part of 30 minutes manually changing all my columns to nullvalue = empty.  They should have just left the nullvalue property default at dbnull.

  7. Very many thanks for a good work. Nice and useful. Like it!

  8. MGGarcia says:

    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.

  9. Jimmy Stuart says:

    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.

  10. Lee Rothman says:

    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.

  11. Sam says:

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

  12. emanlee says:

    问题描述:

    对于未定义为System.String的列,唯一有效的值是(引发异常)。

    ForcolumnsnotdefinedasSystem.String,theonlyva…

  13. stealthkk says:

    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.

  14. 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 ?