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.
