0

I want to display some concatenated fields in a webgrid column and receive this error when the concatenated fields have null values

SQL query:

 SELECT *, T.Name AS Title, PT.Name AS PropertyType
 FROM Properties AS P
 LEFT OUTER JOIN Owners_Properties AS OP ON OP.PropertyId = P.PropertyId
 LEFT OUTER JOIN Owners AS O ON O.OwnerId = OP.OwnerId
 LEFT OUTER JOIN PropertyTypes AS PT ON PT.PropertyTypeId = P.PropertyTypeId
 LEFT OUTER JOIN Titles AS T ON T.TitleId = O.TitleId      
 WHERE P.CondoId=@0 AND P.PropertyId=@1

HTML markup:

propertyGrid.Column("Propriétaire", format: (item) => item.Title+ ' ' + item.FirstName+ ' ' + item.LastName)

This problem occurs when ´two consecutives fields´ are NULL

I tried to modify my HTML code in the following way:

 propertyGrid.Column("Owner", format: (item) => @Html.Raw((item.Title=!null) ? (item.Title+ ' ') : "")+ (item.FirstName=!null) ? (item.FirstName+ ' ') : "")+ ((item.LastName=!null) ? item.LastName : "")))

It should also be possible to avoid returning these NULL values but on the SQL query side but I didn't succeed neither. Life's hard ;-)

Jared Farrish
  • 48,585
  • 17
  • 95
  • 104
user1455103
  • 123
  • 2
  • 16
  • Not quite following all this yet but reading it through the first time shouldn't the =! be != in your HTML? – Steve Chambers Sep 29 '12 at 22:35
  • You are right !! Stupid am I ! Do you have an answer on the second part (SQL) ? How to replace a null value by "" on title, firstname and lastname ? Pay attention that Title comes from a joined table (Titles.Name) with TitleId as key – user1455103 Sep 29 '12 at 23:01

2 Answers2

1

I would recommend you to use a view model and add a property on it that will format the value:

public string FormattedOwner
{
    get
    {
        return string.Format("{0} {1} {2}", Title, FirstName, LastName);
    }
}

and now in your view simply use this new view model property to bind the column to:

propertyGrid.Column(columnName: "FormattedOwner", header: "Propriétaire")

If you don't want to follow good practices and use view models you could always transform your views into spaghetti code:

propertyGrid.Column(
    header: "Propriétaire",
    format: @<text>@string.Format("{0} {1} {2}", item.Title, item.FirstName, item.LastName)</text>
)
Darin Dimitrov
  • 1,023,142
  • 271
  • 3,287
  • 2,928
0

Replace =! by != in the HTML markup.

In the SQL you can use ISNULL (SQL Server), IFNULL (MySQL) or similar for other databases to replace NULL with "".

E.g: SELECT ISNULL(T.Name, '') AS Title, ...

Steve Chambers
  • 37,270
  • 24
  • 156
  • 208