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>

IMHO the best way to do it is to make one call to the db like you are doing. Once one has been selected. Do another call to get the details. This way if you have 1000 customers you are not pulling down 10,000 rows (10 fields) — logixologist
– logixologist, Commented Dec 3, 2013 at 17:29
@logixologist Yes, each customer in the database has an ID. How do I make another call to the db for the rest of the details once an option has been selected? — tjboswell
– tjboswell, Commented Dec 3, 2013 at 17:36

Karl Anderson · Accepted Answer · 2013-12-03 17:46:38Z

3

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.

answered Dec 3, 2013 at 17:46

Karl Anderson

34.9k12 gold badges69 silver badges83 bronze badges

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

4 Comments

tjboswell Over a year ago

This seems to work somewhat, but the selectedID returned is always 0. Once an option is selected, a textbox with the CustomerID shows 0 and the DropDownList resets. Any idea why this is happening?

tjboswell Over a year ago

Found the issue! I was refreshing the options of the ddl every time an option was selected. It should look like Page_Load(..){ if(!IsPostBack) {LoadOptions();} }. Thank you for your help!

Karl Anderson Over a year ago

@teabos - no problem, glad it is working for you. Feel free to up-vote the answer when you can. :-)

logixologist Over a year ago

Thanks Karl, Great answer so I +1'd it for you. I had to jump into a meeting so I couldnt finish answering his question ;)

Manfred Radlwimmer · Accepted Answer · 2016-05-29 09:45:17Z

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();
        }

    }

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related