Populate ASP.Net DropDownList from single table on another DropDownList selection using C# and VB.Net

Last Reply 8 months ago By pandeyism

Posted 8 months ago

I know about the select country dorpdown and it will select state dropdown, by using relationship country and state ID. But in this case i have the customer phone, name and address in one table, so i have three dropdown like Customer Name, Customer Address and Customer Phone. So i want user to be able to select customer name dropdown and the customer phone and customer address dropdown will be selected. 

code

        if (!this.IsPostBack)
        {
            string Constr1 = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
            using (SqlConnection CONNE = new SqlConnection(Constr1))
            {
                using (SqlCommand C0MMDE = new SqlCommand("SELECT CustomerName FROM CustomerDetail "))
                {
                    C0MMDE.Parameters.AddWithValue("@CustomerName", this.ddlcustomername.SelectedValue.Trim());

                    C0MMDE.Parameters.AddWithValue("@UserName", Session["userName"]);
                    C0MMDE.CommandType = CommandType.Text;
                    C0MMDE.Connection = CONNE;
                    CONNE.Open();
                    ddlcustomername.DataSource = C0MMDE.ExecuteReader();
                    // ddlProductCode.DataValueField = "ProductID";
                    ddlcustomername.DataTextField = "CustomerName";
                    // ddlProductCode.DataValueField = "UserName";

                    ddlcustomername.DataBind();
                    CONNE.Close();
                }
            }
            ddlcustomername.Items.Insert(0, new ListItem("--Select Customer Name--", "0"));
        }

 

<asp:DropDownList ID="ddlcustomername" runat="server" CssClass="btn btn-default dropdown-toggle"
    Width="100%" AutoPostBack="true" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlcustomernameChanged">
    <p class="">
        <asp:DropDownList ID="ddlcustomerphone" runat="server" CssClass="btn btn-default dropdown-toggle"
            AutoPostBack="true" AppendDataBoundItems="true" OnSelectedIndexChanged="CustomerphoneChanged"
            Width="100%">
            <asp:ListItem Text="Select Customer Phone" Value="CustomerPhone" />
        </asp:DropDownList>
    </p>
    <p class="">
        <asp:DropDownList ID="ddlcustomeraddress" runat="server" CssClass="btn btn-default dropdown-toggle"
            Width="100%">
        </asp:DropDownList>
    </p>

 

Posted 8 months ago Modified on 8 months ago

Hey micah,

Please refer below sample.

Database

For this sample I have used of NorthWind database that you can download using the link given below.

Download Northwind Database

HTML

<table>
    <tr>
        <td>
            Name:
        </td>
        <td>
            <asp:DropDownList ID="ddlCountries" runat="server" AutoPostBack="true" OnSelectedIndexChanged="Country_Changed">
            </asp:DropDownList>
        </td>
    </tr>
    <tr>
        <td>
            Address:
        </td>
        <td>
            <asp:DropDownList ID="ddlStates" runat="server">
            </asp:DropDownList>
        </td>
    </tr>
    <tr>
        <td>
            Phone Number:
        </td>
        <td>
            <asp:DropDownList ID="ddlCities" runat="server">
            </asp:DropDownList>
        </td>
    </tr>
</table>

Namespaces

C#

using System.Data.SqlClient;
using System.Configuration;
using System.Data;

VB.Net

Imports System.Data.SqlClient
Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        string query = "SELECT TOP 5 CustomerId, ContactName FROM Customers";
        string conString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
        SqlCommand cmd = new SqlCommand(query);
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                con.Open();
                ddlCountries.DataSource = cmd.ExecuteReader();
                ddlCountries.DataTextField = "ContactName"; ;
                ddlCountries.DataValueField = "CustomerId";
                ddlCountries.DataBind();
                con.Close();
            }
        }
        ddlCountries.Items.Insert(0, new ListItem("Select Country", "0"));
        ddlStates.Items.Insert(0, new ListItem("Select State", "0"));
        ddlCities.Items.Insert(0, new ListItem("Select City", "0"));
    }
}

private void BindDropDownList(string query)
{
    string conString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        cmd.Connection = con;
        using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
        {
            DataTable dt = new DataTable();
            sda.Fill(dt);
            ddlStates.DataSource = dt;
            ddlStates.DataTextField = "Address";
            ddlStates.DataValueField = "CustomerId";
            ddlStates.DataBind();
            ddlCities.DataSource = dt;
            ddlCities.DataTextField = "Phone";
            ddlCities.DataValueField = "CustomerId";
            ddlCities.DataBind();

        }
    }
}

protected void Country_Changed(object sender, EventArgs e)
{
    ddlStates.Items.Clear();
    ddlCities.Items.Clear();
    ddlStates.Items.Insert(0, new ListItem("Select State", "0"));
    ddlCities.Items.Insert(0, new ListItem("Select City", "0"));
    string countryId = ddlCountries.SelectedItem.Value;
    string query = string.Format("SELECT CustomerId,Address,Phone FROM Customers WHERE CustomerID= '{0}'", countryId);
    BindDropDownList(query);

}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        Dim query As String = "SELECT  TOP 5 CustomerId, ContactName FROM Customers"
        Dim conString As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString
        Dim cmd As SqlCommand = New SqlCommand(query)
        Using con As SqlConnection = New SqlConnection(conString)
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Connection = con
                con.Open()
                ddlCountries.DataSource = cmd.ExecuteReader()
                ddlCountries.DataTextField = "ContactName"
                ddlCountries.DataValueField = "CustomerId"
                ddlCountries.DataBind()
                con.Close()
            End Using
        End Using
        ddlCountries.Items.Insert(0, New ListItem("Select Country", "0"))
        ddlStates.Items.Insert(0, New ListItem("Select State", "0"))
        ddlCities.Items.Insert(0, New ListItem("Select City", "0"))
    End If
End Sub

Private Sub BindDropDownList(ByVal query As String)
    Dim conString As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString
    Dim cmd As SqlCommand = New SqlCommand(query)
    Using con As SqlConnection = New SqlConnection(conString)
        cmd.Connection = con
        Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
            Dim dt As DataTable = New DataTable()
            sda.Fill(dt)
            ddlStates.DataSource = dt
            ddlStates.DataTextField = "Address"
            ddlStates.DataValueField = "CustomerId"
            ddlStates.DataBind()
            ddlCities.DataSource = dt
            ddlCities.DataTextField = "Phone"
            ddlCities.DataValueField = "CustomerId"
            ddlCities.DataBind()
        End Using
    End Using
End Sub

Protected Sub Country_Changed(ByVal sender As Object, ByVal e As EventArgs)
    ddlStates.Items.Clear()
    ddlCities.Items.Clear()
    ddlStates.Items.Insert(0, New ListItem("Select State", "0"))
    ddlCities.Items.Insert(0, New ListItem("Select City", "0"))
    Dim countryId As String = ddlCountries.SelectedItem.Value
    Dim query As String = String.Format("SELECT CustomerId,Address,Phone FROM Customers WHERE CustomerID= '{0}'", countryId)
    BindDropDownList(query)
End Sub

Screenshot