You need a couple of things:
- Getting the States and binding them to the first combo
- Responding to the SelectedIndexChanged event of the first combo
- Loading the relevant Cities and binding them to the second combo
Private Sub Form1_Load(sender As Object, e As EventArgs)
FillStates()
End Sub
Private Sub FillStates()
Dim con As New SqlConnection(strConn)
Dim cmd As New SqlCommand()
cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT StateId, StateName FROM State"
Dim objDs As New DataSet()
Dim dAdapter As New SqlDataAdapter()
dAdapter.SelectCommand = cmd
con.Open()
dAdapter.Fill(objDs)
con.Close()
cmbCountry.ValueMember = "StateId"
cmbCountry.DisplayMember = "StateName"
cmbCountry.DataSource = objDs.Tables(0)
End Sub
Private Sub cmbState_SelectedIndexChanged(sender As Object, e As EventArgs)
If cmbCountry.SelectedValue.ToString() <> "" Then
Dim CountryID As Integer = Convert.ToInt32(cmbCountry.SelectedValue.ToString())
FillStates(CountryID)
cmbCity.SelectedIndex = 0
End If
End Sub
Private Sub FillStates(countryID As Integer)
Dim con As New SqlConnection(strConn)
Dim cmd As New SqlCommand()
cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT StateID, StateName FROM State WHERE CountryID =@CountryID"
cmd.Parameters.AddWithValue("@CountryID", countryID)
Dim objDs As New DataSet()
Dim dAdapter As New SqlDataAdapter()
dAdapter.SelectCommand = cmd
con.Open()
dAdapter.Fill(objDs)
con.Close()
If objDs.Tables(0).Rows.Count > 0 Then
cmbState.ValueMember = "StateID"
cmbState.DisplayMember = "StateName"
cmbState.DataSource = objDs.Tables(0)
End If
End Sub
Private Sub cmbState_SelectedIndexChanged(sender As Object, e As EventArgs)
Dim stateID As Integer = Convert.ToInt32(cmbState.SelectedValue.ToString())
FillCities(stateID)
End Sub
Private Sub FillCities(stateId As Integer)
Dim con As New SqlConnection(strConn)
Dim cmd As New SqlCommand()
cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT CityID, CityName FROM City WHERE StateId =@StateId"
cmd.Parameters.AddWithValue("@StateId", stateID)
Dim objDs As New DataSet()
Dim dAdapter As New SqlDataAdapter()
dAdapter.SelectCommand = cmd
con.Open()
dAdapter.Fill(objDs)
con.Close()
If objDs.Tables(0).Rows.Count > 0 Then
cmbCity.DataSource = objDs.Tables(0)
cmbCity.DisplayMember = "CityName"
cmbCity.ValueMember = "CityId"
End If
End Sub
It should also be possible to do something more intelligent with a DataSet with two tables and defining the relationship between them but that would require you to load all cities at once. This solution resembles a lazy loading option.