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
