Add blank first row to an SqlDataSource bound to DropDownList

Well, "best" code would be rather subjective.

I suggest building a simple routine that all such code in the project can use.

For this example we have a “common” cascade of 2 combo boxes.

We select a city, and then cascade (filter) the 2nd combo with hotels in that given city.

So, this markup:

        <div style="float:left">
            <h3>Select City</h3>
            <asp:DropDownList ID="cboCity" runat="server" Width="200px"
                AutoPostBack="true"
                OnSelectedIndexChanged="cboCity_SelectedIndexChanged" >
            </asp:DropDownList>
        </div>

        <div style="float:left;margin-left:25px">
            <h3>Select Hotel</h3>
            <asp:DropDownList ID="cboHotels" runat="server" Width="200px" >
            </asp:DropDownList>
        </div>

And code behind on the web page is this:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            LoadCityCbo();
    }

    void LoadCityCbo()
    {
        SqlCommand cmd = 
            new SqlCommand("Select City FROM City ORDER BY City");
        General.Cbo(cboCity, cmd, "", "City", "Select City");
    }

    protected void cboCity_SelectedIndexChanged(object sender, EventArgs e)
    {
        // cascade
        SqlCommand cmd =
            new SqlCommand(@"SELECT ID, HotelName
                           FROM tblhotelsA WHERE City = @City
                           ORDER BY HotelName");

        cmd.Parameters.Add("@City", SqlDbType.NVarChar).Value = cboCity.SelectedItem.Text;
        General.Cbo(cboHotels, cmd, "ID", "HotelName", "Select Hotel");
    }

So above approach allows setting of the data source for the combo box, the “value” and “text” field settings, and the extra prompt row.

And in our “general” catch all class (used by all web pages and code behind) we have these 2 routines.

public static void Cbo(DropDownList ddl,
                    SqlCommand cmd,
                    string sValue, string sText,
                    string sPrompt)
{
    ddl.DataValueField = sValue;
    ddl.DataTextField = sText;
    ddl.DataSource = General.MyRstP(cmd);
    ddl.DataBind();
    ddl.Items.Insert(0, sPrompt);
}


public static DataTable MyRstP(SqlCommand cmdSQL)
{
    using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
    {
        using (cmdSQL)
        {
            cmdSQL.Connection = conn;
            conn.Open();
            rstData.Load(cmdSQL.ExecuteReader());
        }
    }
    return rstData;
}