Fill multiple textboxes after selecting from a DropDownList in ASP.NET

Question

I have a DropDownList that is linked to a SQL database. It currently shows a list of customers. I am trying to make it so that once a customer is selected, multiple textboxes are automatically filled (such as address, city, etc). I am able to automatically fill the "company name" textbox as the value is the one selected, but I do not know how to fill the other textboxes using the rest of the data in the row. How would I best go about doing this?

in .aspx:

<asp:DropDownList ID="DropDownList1" runat="server" ></asp:DropDownList>

C#:

DataTable customers = new DataTable();
...

SqlDataAdapter adapter = new SqlDataAdapter("SELECT CustomerName FROM Customers.dbo.Customer", connection);
                adapter.Fill(customers);
                DropDownList1.DataSource = customers;
                DropDownList1.DataTextField = "CustomerName";
                DropDownList1.DataValueField = "CustomerName";
                DropDownList1.DataBind();

EDIT: Thanks Karl for your help. Follow his advice if you have a similar issue. Also, make sure to change the dropdownlist so it looks like:

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true" onselectedindexchanged="CompanyChanged"></asp:DropDownList>

EDIT2: For those that are having the same problem.. Here is my code:

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)

    {
        LoadOptions();
    }
}
protected void LoadOptions()
    {
        DataTable customers = new DataTable();

        SqlConnection connection = new SqlConnection(INSERT YOUR CONNECTION STRING HERE);
        using (connection)
        {
            SqlDataAdapter adapter = new SqlDataAdapter("SELECT Column1 FROM Table", connection);

            adapter.Fill(customers);
            DropDownListID.DataSource = customers;

            companyselect.DataTextField = "Column1";
            companyselect.DataValueField = "Column1";
            companyselect.DataBind(); 
        }
    }

protected void SelectionChanged(object sender, EventArgs e)
    {
        string selected= DropDownListID.SelectedItem.Value;

        SqlConnection connection = new SqlConnection(YOUR CONNECTION STRING);
        using (connection)
        {

            SqlCommand command = new SqlCommand("SELECT * FROM Table WHERE Column1= @Column1", connection);
            command.Parameters.AddWithValue("@Column1", selected);
            command.CommandType = CommandType.Text;
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            using (reader)
            {
                if (reader.HasRows)
                {
                    reader.Read();

//add as many as needed to fill your textboxes
                    TextBox1.Text = reader.GetString(1);
                    TextBox2.Text = reader.GetString(2);
                    TextBox3.Text = reader.GetString(3);
                }
                else { }
            }
        }
    }

And this is what my dropdownlist looks like:

<asp:DropDownList  ID="DropDownListID" runat="server" AutoPostBack="true" onselectedindexchanged="SelectionChanged"></asp:DropDownList>

Answer 1

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            SQLconn.Open();

            string req = "select distinct CapaciteTotal from Avion where NomAvion=" + DropDownList1.Text + "";
            SqlCommand com = new SqlCommand(req, SQLconn);
            SqlDataReader dr = com.ExecuteReader();
            if (dr.Read())
            {
                TextBox1.Text = dr.GetValue(1).ToString();
                if (DropDownList4.Text == "Aller Retour")
                {
                    TextBox2.Text = dr.GetValue(1).ToString();
                }

            }
        }
        catch(Exception ex)
        {
            HttpContext.Current.Response.Write("<script>alert('Probleme de la base de donnee : " + ex.Message + "')</script>");
        }
        finally
        {
            SQLconn.Close();
        }

    }

Answer 2

I would highly recommend that you use the CustomerID or equivalent ID field in the database as the DataValueField in your drop down list, as you may want to allow the same customer name in the database, but be able to tell them apart by ID.

Instead, try this:

SqlDataAdapter adapter = new SqlDataAdapter("SELECT CustomerName, CustomerID FROM Customers.dbo.Customer", connection);
adapter.Fill(customers);
DropDownList1.DataSource = customers;
DropDownList1.DataTextField = "CustomerName";
DropDownList1.DataValueField = "CustomerID";
DropDownList1.DataBind();

Note: Change CustomerID to whatever the actual ID column name in your database table actually is.

Now in your drop down list's SelectedIndexChanged event, grab the ID of the selected item and query the database for the single row of data, like this:

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
    string selectedID = DropDownList1.SelectedItem.Value;

    SqlCommand theCommand = new SqlCommand("SELECT * FROM Customers.dbo.Customer WHERE CustomerID = @CustomerID", connection);
    theCommand.Paramters.AddWithValue("@CustomerID", selectedId);
    theCommand.CommandType = CommandType.Text;

    using (SqlDataReader theReader = theCommand.ExecuteReader())
    {
        if (theReader.HasRows)
        {
            // Get the first row
            theReader.Read();

            // Set the text box values
            CustomerName.Text = theReader.GetString(0);
            ...
        }
    } 
}

Note: The retrieval of the single row of customer data uses a parameterized query to avoid SQL Injection vulnerabilities. Also, the code above uses the SqlCommand and SqlDataReader along with using blocks to properly dispose of the reader object.