relation between two combobox

Question

1.00/5 (1 vote)

See more:

hi my friends.
i have two table im my database. one table is City and one table is State.
in Table State , i have Two field: Id and Name .
in Table city , i have two field : stateid and name that stateid is select from field id in state table.
in vb.net, i have two combobox. one for state and one for city.
first i bind table state to combobox but i don,t know how can set relation between state and city. i writr this code in state combobox but it didn,t work.please help.

SQL

da1 = New SqlDataAdapter("select Name from City where City.StateId ='" + Convert.ToString(cmbstate.SelectedValue) + "'", con)
        ds1.Clear()
        da1.Fill(ds1, "City")
        cmbcity.DataSource = ds1
        cmbcity.DisplayMember = "City.Name"

Posted 30-Aug-14 4:33am

Mohammad Hasanpoor

Add a Solution

2 solutions

Solution 1

In the selectionchanged event of the state combo box add the populate the city dropdown for the state selected.

Change the above lines to the following

VB

da1 = New SqlDataAdapter("select Name from City where City.StateId ='" + Convert.ToString(cmbstate.SelectedValue) + "'", con)
        ds1.Clear()
        da1.Fill(ds1, "City")
        cmbcity.DataSource = ds1
        cmbcity.DisplayMember = "City.Name"

to

VB

da1 = New SqlDataAdapter("select Name from City where City.StateId ='" + Convert.ToString(cmbstate.SelectedValue) + "'", con)
        ds1.Clear()
        da1.Fill(ds1, "City")
        cmbcity.DataSource = ds1
        cmbcity.DisplayMember = "City.Name"
        cmbcity.ValueMember = "City.Name"

Posted 30-Aug-14 5:13am

ChauhanAjay

Updated 30-Aug-14 5:18am

v2

Comments

Mohammad Hasanpoor 30-Aug-14 11:20am

i did that but i have this error :
Conversion failed when converting the varchar value 'System.Data.DataRowView' to data type int.

ChauhanAjay 30-Aug-14 11:22am

on which line do u get the error

Mohammad Hasanpoor 30-Aug-14 11:33am

da1.Fill(ds1, "City")

Mohammad Hasanpoor 30-Aug-14 11:46am

please help. its very necessery.

ChauhanAjay 30-Aug-14 11:47am

better to change your dataset ds1 to a datatable dt1.

Mohammad Hasanpoor 30-Aug-14 11:51am

so what is correct code? which lines i should delete and which lines i should add?

ChauhanAjay 30-Aug-14 12:02pm

Dim dt1 as New DataTable
da1 = New SqlDataAdapter("select Name from City where City.StateId ='" + Convert.ToString(cmbstate.SelectedValue) + "'", con)

da1.Fill(dt1)
cmbcity.DataSource = dt1
cmbcity.DisplayMember = "City.Name"
cmbcity.ValueMember = "City.Name"

Mohammad Hasanpoor 30-Aug-14 12:05pm

again i have error : "Conversion failed when converting the varchar value 'System.Data.DataRowView' to data type int."
in line : da1.Fill(dt1)

ChauhanAjay 30-Aug-14 12:10pm

Dim dt1 as New DataTable
da1 = New SqlDataAdapter("select Name from City where City.StateId ='" + cmbstate.SelectedValue.ToString() + "'", con)

da1.Fill(dt1)
cmbcity.DataSource = dt1
cmbcity.DisplayMember = "City.Name"
cmbcity.ValueMember = "City.Name"

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kbrandwijk · Accepted Answer · 2014-08-30T05:13:00

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

VB

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.