Add Item to DropDownList on selection of another using C# and VB.Net in ASP.Net

Last Reply 3 days ago By dharmendr

Posted 4 days ago

I have a dropdown location see markup below

<asp:DropDownList ID="ddllocation" runat="server" AppendDataBoundItems="true"  AutoPostBack="true" 
    CssClass="form-control pro-edt-select form-control-primary" Width="100%" >
    <asp:ListItem Text="--Select Location--" />
    <asp:ListItem>Branch1</asp:ListItem>

 <asp:LinkButton ID="btlocation" runat="server" CssClass="btn btn-default" OnClick="btlocation_Click" >Call Location</asp:LinkButton>

The dropdown ddllocation when selected it supposed to pull names from database base on location and then display the name on ddlprocessedby but is not working 

<asp:DropDownList ID="ddlprocessedby" runat="server" AppendDataBoundItems="true"
    CssClass="form-control pro-edt-select form-control-primary" Width="100%" >
</asp:DropDownList>

see code below

        protected void btlocation_Click(object sender, EventArgs e)
        {
            string constr = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand command = new SqlCommand("SELECT  Name FROM User3 WHERE [Location]='@Branch1'", con);
                command.Parameters.AddWithValue("@Branch1", ddllocation.Text);
                con.Open();
                SqlDataReader sdr = command.ExecuteReader();
                if (sdr.Read())
                {                    
                    ddlprocessedby.SelectedItem.Text = sdr["Name"].ToString();
                }
            }
        }

In database table i have this below

UserId Name, Location
------------------------------
1      Jake  Branch1
---------------------------------
2      Mary  Branch2
-------------------------------------
3      John  Branch1
-----------------------------------------

So the idea is when location dropdown is selected and button pull location is clicked the names in those locations will be displayed on the dropdown ddlprocessedby

Posted 4 days ago Modified on 3 days ago

Hi micah,

Check this example. Now please take its reference and correct your code.

Database

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

HTML

<asp:DropDownList ID="ddlCustomers" runat="server">
</asp:DropDownList>
<asp:LinkButton ID="btnSet" runat="server" OnClick="OnSet">Set Country</asp:LinkButton>
<hr />
<asp:DropDownList ID="ddlCountry" runat="server">
</asp:DropDownList>

Namepsaces

C#

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

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.ddlCustomers.DataSource = this.GetData("SELECT DISTINCT Name FROM Customers");
        this.ddlCustomers.DataTextField = "Name";
        this.ddlCustomers.DataValueField = "Name";
        this.ddlCustomers.DataBind();
        this.ddlCustomers.Items.Insert(0, new ListItem { Text = "Select Customer", Value = "0" });

        this.ddlCountries.DataSource = this.GetData("SELECT DISTINCT Country FROM Customers");
        this.ddlCountries.DataTextField = "Country";
        this.ddlCountries.DataValueField = "Country";
        this.ddlCountries.DataBind();
        this.ddlCountries.Items.Insert(0, new ListItem { Text = "Select Country", Value = "0" });
    }
}

protected void OnSet(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        SqlCommand command = new SqlCommand("SELECT Country FROM Customers WHERE Name=@Name", con);
        command.Parameters.AddWithValue("@Name", this.ddlCustomers.SelectedItem.Text.Trim());
        con.Open();
        string country = Convert.ToString(command.ExecuteScalar());
        con.Close();
        if (!string.IsNullOrEmpty(country))
        {
            if (this.ddlCountries.Items.FindByText(country) != null)
            {
                this.ddlCountries.ClearSelection();
                this.ddlCountries.Items.FindByText(country).Selected = true;
            }
        }
    }
}

private DataTable GetData(string query)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        SqlCommand cmd = new SqlCommand(query);
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);

                return dt;
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.ddlCustomers.DataSource = Me.GetData("SELECT DISTINCT Name FROM Customers")
        Me.ddlCustomers.DataTextField = "Name"
        Me.ddlCustomers.DataValueField = "Name"
        Me.ddlCustomers.DataBind()
        Me.ddlCustomers.Items.Insert(0, New ListItem With {.Text = "Select Customer", .Value = "0"})

        Me.ddlCountries.DataSource = Me.GetData("SELECT DISTINCT Country FROM Customers")
        Me.ddlCountries.DataTextField = "Country"
        Me.ddlCountries.DataValueField = "Country"
        Me.ddlCountries.DataBind()
        Me.ddlCountries.Items.Insert(0, New ListItem With {.Text = "Select Country", .Value = "0"})
    End If
End Sub

Protected Sub OnSet(ByVal sender As Object, ByVal e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Dim command As SqlCommand = New SqlCommand("SELECT Country FROM Customers WHERE Name=@Name", con)
        command.Parameters.AddWithValue("@Name", Me.ddlCustomers.SelectedItem.Text.Trim())
        con.Open()
        Dim country As String = Convert.ToString(command.ExecuteScalar())
        con.Close()
        If Not String.IsNullOrEmpty(country) Then
            If Me.ddlCountries.Items.FindByText(country) IsNot Nothing Then
                Me.ddlCountries.ClearSelection()
                Me.ddlCountries.Items.FindByText(country).Selected = True
            End If
        End If
    End Using
End Sub

Private Function GetData(ByVal query As String) As DataTable
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        Dim cmd As SqlCommand = New SqlCommand(query)
        Using sda As SqlDataAdapter = New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Using dt As DataTable = New DataTable()
                sda.Fill(dt)
                Return dt
            End Using
        End Using
    End Using
End Function

Screenshot


Posted 3 days ago
dharmendr says:
        if (!string.IsNullOrEmpty(country))
        {
            if (this.ddlCountries.Items.FindByText(country) != null)
            {
                this.ddlCountries.ClearSelection();
                this.ddlCountries.Items.FindByText(country).Selected = true;
            }
        }

Replace with below code.

if (!string.IsNullOrEmpty(country))
{
    this.ddlCountries.Items.Clear();
    this.ddlCountries.Items.Insert(0, new ListItem { Text = country, Value = country });
}