BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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 blog)
Posted by David Wakefield on 31 May 2011
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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
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
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 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.
You can use this SQL either in an SQL View or as the source of your databound control!
- Set the first item in a list type control
- Creating the Union Query for a List Type Control (this blog)