Here’s a clever little solution I would like to add to the book of ‘get it done’.  While this particular example uses ASP.NET controls, this concept really applies to any language that supports DataBinding to a control.

The base concept is using your knowledge of SQL’s UNION operator to add a temp value to the beginning of a list of data from a sql query.  In the past I’ve done this countless times via code, and recently I didn’t have the time to do this, so I updated the query to a UNION, and I was good to go.

Now I’m not selling this as a ‘best practice’, but I do consider this one more reason why it’s good to know SQL.

Problem : Using a SQLDataSource to populate a DropDown component, how do you inject a spacer value in position 0? EX : “- select value -”

Solution : Inject your spacer value in your SelectCommand via sql’s UNION operator

ComboBox

<asp:DropDownList runat="server" ID="meter_manufacturer_dd" DataSourceID="sql_meterManufacturer" DataTextField="Manufacturer" />

DataSource

<asp:SqlDataSource runat="server" ID="sql_meterManufacturer"
    SelectCommand="
    SELECT '- Choose Manufacturer -' as Manufacturer
    UNION
    SELECT DISTINCT Manufacturer FROM Smart_Meter_DEF"
    />

What this solution gets you.

1. Your spacer value shows up in position 0 ( because the first character is – and not alphanumeric )

2. Auto ViewState caching ( EG : going straight .NET solution, .NET handles persisting your dropdown selection between postbacks )

* in this sample, the connectionString for the SqlDataSource is set in code.