Populate drop down list from SQL Server database

Question

I have an ASPX page that has a drop down on it.

<div>
    <p>Pick a customer to show their order(s)</p>
    <asp:DropDownList ID="customerSelect" AutoPostBack="true" runat="server" OnSelectedIndexChanged="customerSelect_SelectedIndexChanged"></asp:DropDownList>
</div>

I want the options in the drop down to be populated from a database of customer names.

Here is what my database looks like

database

Here is my code behind

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

public void populateDropDown()
{
    SqlCommand cmd = new SqlCommand("SELECT * FROM [Orders]", new SqlConnection(ConfigurationManager.AppSettings["ConnString"]));
    cmd.Connection.Open();

    SqlDataReader ddlValues;
    ddlValues = cmd.ExecuteReader();

    customerSelect.DataSource = ddlValues;
    customerSelect.DataValueField = "OrderID";
    customerSelect.DataTextField = "CustomerName";

    cmd.Connection.Close();
    cmd.Connection.Dispose();

}

I've used this code before. So I must be missing something simple but I'm not sure what it is.

EDIT I am getting no errors. The code compiles. But the drop down list remains blank.

In what way is this not working? When you debug this, where/how does it fail? — David
– David, Commented Dec 29, 2014 at 13:34
I'm sorry. Should have provided that. It does not populate the drop down list. I get no errors. — onTheInternet
– onTheInternet, Commented Dec 29, 2014 at 13:35
"IsPostBack" and "SqlConnection" in the same code block hurts my eyes. — granadaCoder
– granadaCoder, Commented Dec 29, 2014 at 14:13
@granadaCoder why is that? Can you explain? I'm new to this so any advice is appreciated. — onTheInternet
– onTheInternet, Commented Dec 29, 2014 at 15:01
"Separation of Concerns" is the concept. weblogs.asp.net/arturtrosin/… — granadaCoder
– granadaCoder, Commented Dec 29, 2014 at 15:31

Soner Gönül · Accepted Answer · 2014-12-29 13:35:42Z

4

Well, I think you forget to bind your dropdownlist like;

customerSelect.DataBind();

And use using statement to dispose your SqlCommand, SqlConnection and SqlDataReader instead of calling .Dispose() method manually.

using(SqlConnection con = new SqlConnection(connString))
using(SqlCommand cmd = con.CreateCommand())
{
    ...
    ...
    using(SqlDataReader ddlValues = cmd.ExecuteReader())
    {
       ...
    }
}

answered Dec 29, 2014 at 13:35

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

onTheInternet Over a year ago

YEP! Exactly right. Thank you! I will accept answer in 9 minutes.

Collectives™ on Stack Overflow

Populate drop down list from SQL Server database

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related