Expression column with nulls

[Earl]

When I create an expression column, I get the concatenation I'm looking for,
except for entries which have nulls. For example:

FullName.Expression = "FirstName + ' ' + MiddleInitial + ' ' + LastName"

If all of these columns return non-null values, I get the FullName as I
wish. But, if ANY of these columns have nulls, I get a null for FullName.
This is somewhat as I would expect, since an SQL query would also return
null if ISNULL were not used, but how do I handle the nulls in ADO.Net?

 

[Joyjit Mukherjee]

Hi,

can you try it like this: -

FirstName = FirstName + "";
MiddleInitial = MiddleInitial + "";
LastName = LastName + "";

this will append empty strings for the respective variables and then you can
use the Expression column. However, this is simply a shortcut and not the
preferred solution. The recommended approach is to use the System.DbNull
method.

Regards
Joyjit

 

[Kevin Yu [MSFT]]

Hi Earl,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you're getting null value for FullName
when either of the FirstName or LastName is null. If there is any
misunderstanding, please feel free to let me know.

Based on my research, this is a known issue in the current version of .net
framework and might be fixed in the next version. The workaround is to add
extended properties manually in the code of typed dataset like the
following:

dataTable.FullNameColumn.ExtendedProperties.Add("nullValue", "_empty");

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Earl]

Thanks Kevin and JoyJit. It seems like what you are saying is that I have to
define a custom column for each of the original columns, then set
ExtendedProperties on EACH one of those. However that did not work. So I
also tried to add the empty strings to each. That did not work either. All
in all, whatever the solution is seems like a lot of work to do a simple
null replacement.

Here's some code to fire at (tried this both with and without the
ExtendedProperties and also with and without the empty strings being added):

Dim dcFirst As New DataColumn
dcFirst.DataType = System.Type.GetType("System.String")
dcFirst.ColumnName = "FirstName"
dcFirst.Expression = "FirstName" + ""
dcFirst.ExtendedProperties.Add("nullValue", "_empty")

Dim dcMiddle As New DataColumn
dcMiddle.DataType = System.Type.GetType("System.String")
dcMiddle.ColumnName = "MiddleInitial"
dcMiddle.Expression = "MiddleInitial" + ""
dcMiddle.ExtendedProperties.Add("nullValue", "_empty")

Dim dcLast As New DataColumn
dcLast.DataType = System.Type.GetType("System.String")
dcLast.ColumnName = "LastName"
dcLast.Expression = "LastName" + ""
dcLast.ExtendedProperties.Add("nullValue", "_empty")

Dim dcFullName As New DataColumn
dcFullName.DataType = System.Type.GetType("System.String")
dcFullName.ColumnName = "FullName"
dcFullName.Expression = "FirstName + ' ' + MiddleInitial + ' ' + LastName"
ds.Tables("dtEmployees").Columns.Add(dcFullName)

 

[Kevin Yu [MSFT]]

Hi Earl,

What I meant was not adding a space to the end of the column. I think we
can set the null value of the column to empty string, so that the column
will not return null for data.

We can add the nullValue both in code or in DataSet schema. For C# code,
use the following:

dataTable.FullNameColumn.ExtendedProperties.Add("nullValue", "_empty");

Or you can edit the DataSet schema in the DataSet design view, select the
column element and set nullValue to empty in the property window. HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Hi earl,

if i understand ur problem correctly try this,

FullName.Expression = "isnull(FirstName,' ') + ' ' +isnull( MiddleInitial,'
') + ' ' + isnull(LastName,' ')"

Hope this helps!

suresh

 

[Earl]

Thanks Kevin, but I am not using a typed dataset. This is in CF.

 

[Earl]

Ta-da ... that was the easiest solution. Thanks Suresh! Now how come someone
didn't just say that I could use isnull the same way as on the server....?
Hehe.

 

[Kevin Yu [MSFT]]

Hi Earl,

It seems that Suresh's suggestion has had the problem resolved.

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."