Handling NULL in a DataColumn expression

[BrownFingers]

I have a datacolumn with the following expression

FirstName + ' ' + Surname

I now need to add Job Role to then end so I have something like

Joe Bloggs, Managing Director

so I my expression now looks like :

FirstName + ' ' + Surname + ', ' + JobRole

My problem is that JobRole will not always be known so sometimes it will contain a NULL value. In such an instance I want to display the original expression

FirstName + ' ' + Surname

I can't seem to find anywhere that shows me what statements I can use in a DataColumn expression.

I would presume it needs to be something like

If JobRole Is NULL then FirstName + ' ' + Surname Else FirstName + ' ' + Surname + ', ' + JobRole

Can anyone give me correct syntax?

 

[cjard]

DataColumn.Expression Property (System.Data) curiously enough

I'd suggest:

IIF(ISNULL([JobRole]),[SN]+' '+[FN],[SN]+' '+[FN]+', '+[JobRole])

 

[MattP]

DataColumn.Expression Property (System.Data) curiously enough

I'd suggest:

IIF(ISNULL([JobRole]),[SN]+' '+[FN],[SN]+' '+[FN]+', '+[JobRole])

When looking into this yesterday I couldn't get IsNull to work inside the IIF statement. Kept getting an error about converting JobRole to Boolean.

IsNull requires a replacement value as well: IsNull([JobRole], 'N/A'). The only thing I could come up with before work called was setting a default value of JobRole to an empty string and checking if the length of the column was 0.

IIF(LEN([JobRole]) = 0,[SN]+' '+[FN],[SN]+' '+[FN]+', '+[JobRole])

If someone has a better solution I'm all for hearing it.

 

[cjard]

Of course.. I forgot that expression imitates SQLServer's rather retarded IsNull method.. And that far from it being a boolean test, it's a more useless version of COALESCE

Try:

IIF(ISNULL([JobRole], '<NULL>') = '<NULL>',[SN]+' '+[FN],[SN]+' '+[FN]+', '+[JobRole])

 

[BrownFingers]

Thanks for your help. So basically what your saying is the Expression uses SQL syntax.

I ended up using the following format and it works fine

FIRSTNAME + ' ' + SURNAME + ISNULL(' ( ' + JOB_ROLE+ ') ','')

So I get

Joe Bloggs ( Managing Director )

or when no job role is set up ...

Joe Bloggs

 

[cjard]

Thanks for your help. So basically what your saying is the Expression uses SQL syntax.

No, what I'm saying is it uses the syntax as written in the documentation found at DataColumn.Expression Property (System.Data)

I ended up using the following format and it works fine

FIRSTNAME + ' ' + SURNAME + ISNULL(' ( ' + JOB_ROLE+ ') ','')

So I get

Joe Bloggs ( Managing Director )

or when no job role is set up ...

Joe Bloggs

I wasn't aware that any null in .Expression syntax will cause an entire concatenation operation to return null. In Oracle, VB and most other languages I've worked with, concatenating ' (' + NULL + ')' together would result in ' ()' which is why I didnt suggest the simpler solution you have provided here. Knowing that anything + NULL in .Expression syntax reduces the entire thing to null is helpful, if counter intuitive