USING SQL UNION QUERIES TO POPULATE THE FIRST ITEM IN A LIST TYPE CONTROL

Part two of a two-part series of blogs

Adding a first or default item to a list control which gets its data from a table can be tricky. This blog gives an elegant solution using UNION queries, which works equally well for ASP.NET dropdown controls, Windows Forms dropdowns or Access combo boxes.

Set the first item in a list type control
Creating the Union Query for a List Type Control (this article)

Posted by David Wakefield on 31 May 2011 | no comments

Creating the Union Query for a List Type Control

We can start with a simple select query:

SELECT PersonId, PersonName

FROM tblSinger

The following query returns the value we want in the top of our list:

SELECT 0 , 'CHOOSE A SINGER'

It does not have a FROM clause so it just returns the hard coded values.

I have chosen 0 as the value for this item, as none of the underlying data has a key field equal to zero. This would allow me to write code which checked the default value had not been selected.

Now we can use a union clause to join the two statements together:

SELECT 0 , 'CHOOSE A SINGER'

union

SELECT PersonId, PersonName

FROM tblSinger

This has two problems: no column headings, and no sort order!

In a UNION query the column names are taken from the first SELECT statement, so we now add column aliases to the first SELECT:

SELECT 0 AS PersonID , 'CHOOSE A SINGER' AS PersonName

union

SELECT PersonId, PersonName

FROM tblSinger

Column headings now appear. I have used the same names as the columns in the underlying table, though names like ID and Description would work just as well and may allow you to standardise your controls.

Now we need to sort the names alphabetically. The following does not work as expected:

SELECT 0 AS PersonID , 'CHOOSE A SINGER' AS PersonName

union

SELECT PersonId, PersonName

FROM tblSinger

ORDER BY PersonName

'CHOOSE A SINGER' is no longer at the top of our list as an ORDER BY clause applies after the tables have been UNIONed.

To order items correctly we need to add another column. I have called this MySortOrder, and it does not exist in the underlying table. This will have constant values of either 0 for the first value and 1 for every value from the table.

SELECT 0 AS PersonID , 'CHOOSE A SINGER' AS PersonName, 0 as MySortOrder

union

SELECT PersonId, PersonName, 1 as MySortOrder

FROM tblSinger

ORDER BY MySortOrder, PersonName

The final ORDER BY clause now sorts by MySortOrder which will put the one record with 0 at the top of the result list and all the others below sorted by PersonName.

We now have three columns correctly sorted, with column headings.

You can use this SQL either in an SQL View or as the source of your databound control!

USING SQL UNION QUERIES TO POPULATE THE FIRST ITEM IN A LIST TYPE CONTROL

Part two of a two-part series of blogs

Creating the Union Query for a List Type Control

USING SQL UNION QUERIES TO POPULATE THE FIRST ITEM IN A LIST TYPE CONTROL

Part two of a two-part series of blogs

BLOGS BY TOPIC

BY AUTHOR

BY YEAR