SQL value does not populate ASP DropDownList control

Question

I'm trying to populate a dropdown list control. 'Inputs' is the DB. Table is 'ApplicationName', and it has a random value assigned to it.

For some reason, when I click the Test Submit button, nothing is appearing in the control (DropDownList1).

protected void TestSubmit_ServerClick(object sender, EventArgs e)
{
    // Initialize the database connector.
    SqlConnection connectSQL = new SqlConnection();

    // Send the connection string.
    connectSQL.ConnectionString = @"Data Source = localhost\SQLEXPRESS;" + 
        "Initial Catalog = Inputs; Integrated Security = SSPI";

    try
    {
        // Setup our command.
        SqlCommand theCommand = new SqlCommand("SELECT * FROM Inputs", connectSQL);

        // Write the stored value in the text boxes.
        connectSQL.Open();

        SqlDataReader theReader;

        theReader = theCommand.ExecuteReader();
        theReader.Read();

        DropDownList1.Items.Add(theReader["ApplicationName"].ToString());

        theReader.Close();
        connectSQL.Close();
    }
    catch (Exception ee)
    {
        MessageBox("Oopsie: " + ee);
}

Answer 1

I think you want to do a while(reader.read())

Then populate something like a dataset or a List, and then set the drop down lists data source to the dataset or list.

Answer 2

If your table name is "ApplicationName"... shouldn't your SQL statement be SELECT * FROM ApplicationName? Did you try looking to see if your reader had any results?

Answer 3

Have you considered using a SqlDataSource? It'll be far less code for you to maintain and defend against defects for.

     <asp:DropDownList id="DropDownList1"
          runat="server"
          DataTextField="ApplicationName" 
          DataValueField="ApplicationName"
          DataSourceID="AppDataSource">
      </asp:DropDownList>

  <asp:SqlDataSource
          id="AppDataSource"
          runat="server"
          DataSourceMode="DataReader"
          ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
          SelectCommand="SELECT ApplicationName FROM Inputs">
  </asp:SqlDataSource>

Answer 4

If you match the columns selected in your database query with the DataTextField and DataValueField in the definition for your DropDownList, you should just need to do:

DropDownList1.DataSource = theReader;  
DropDownList1.DataBind()

or even

DropDownList1.DataSource = theCommand.ExecuteReader();