[Kentico 10] Drop Down List Ignoring Empty Strings in SQL Result

Christopher Conley asked on September 29, 2017 23:38

Building a new page type in Kentico 10, we are trying to populate a drop down list field with the result of a SQL query. The first result in the query is composed of empty strings, and this is intentional. We are able to execute the query and retrieve good data for the drop down list. However...

When Kentico displays the populated drop down list, the row of empty strings is not displayed at all. I'm assuming this is intended behavior? If so, we need a means to override it, or find another means of including an empty row into our drop down list data. Any suggestions are appreciated. Thanks!

Correct Answer

Brenden Kehren answered on September 30, 2017 00:02

Don't include those empty records in your data. In your custom query do a UNION statement.

Select '', '--select one--'
UNION
Select Column1, Column2 from your_table
Order by 1

3 votesVote for this answer Unmark Correct answer

Recent Answers

Peter Mogilnitski answered on October 1, 2017 01:26

I second Brenden, but you might as well need to order your_table

Select '-1', '--Please Select a User'
UNION
Select * from (Select UserID, UserName From CMS_User Order by UserName  ASC)

2 votesVote for this answer Mark as a Correct answer

Christopher Conley answered on October 2, 2017 16:13 (last edited on October 2, 2017 17:49)

Thank you for your answers, but unfortunately as near as I can tell I was already doing that.

My sanitized query:

Code
BEGIN
SELECT '' AS V1, '' as V2

UNION

SELECT  
ls.[V1] as V1, 
ls.[V2] as V2

FROM SERVER.TABLE as ls

WHERE 
ls.[CONDITION1] = X and ls.[CONDITION2] = Y AND ls.[V!] LIKE '%FUBARBAZ%'
END

This comes back in SQL Management Studio with the desired result set. Is there something I'm missing here? Any additional ideas?

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on October 2, 2017 18:51

I see 'SERVER.TABLE' - this means that you connect to another sql instance which is not kentico DB? It could be a permission issue easely, but anyway check Kentico Event Log. If there is any sql error - you should see in there.

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on October 2, 2017 18:54

No matter what, Kentico will automatically strip out the null/empty value at the beginning if there is nothing in it. So right now your value and text are empty strings, when Kentico builds the ddl's items, it will not include that one. So put something like "--select one--" in the text for the first one and it will work every time.

1 votesVote for this answer Mark as a Correct answer

Christopher Conley answered on October 2, 2017 20:39

Thanks for the clarification! I understand now what I need to do.

0 votesVote for this answer Mark as a Correct answer