Member Avatar for skran

Hi all!
I am writting beacause I desperately want your help..
I want to connect 3 comboboxes. I have a table which contains Streetnames, Post Codes and Municipalities. I want to choose the Streetname from the first combobox something that I have done. The second Combobox I want to display the possible Post Codes. The problem is that the same streetname may belongs to more than one municipality which means that one streetname should display more than one post code at combobox 2 and more than one municiality in combobox 3. I attach my code till now..I achieved to fill the first combo box and i need your help for the second one.. What I do wrong??

Dim StreetNameDA As New SqlDataAdapter()
        Dim DSstreetName1 As New AMRtoSQLDataSet
        'DSstreetName1.Clear()
        'Select Street Names
        StreetNameDA.SelectCommand = New SqlCommand()
        StreetNameDA.SelectCommand.Connection = conn
        StreetNameDA.SelectCommand.CommandText = "SELECT StreetName_LCL FROM     T_MUNICIPALITY_STREETS order by StreetName_LCL"

        StreetNameDA.Fill(DSstreetName1, "StreetNameTable")


        '-------------Fill combo box StreeName1
        'Dim PC As String
        cboStreetName1.DataSource = DSstreetName1.Tables("StreetNameTable")
        cboStreetName1.DisplayMember = "StreetName_LCL"
        ' PC = cboStreetName1.ValueMember
        cboStreetName1.ValueMember = "StreetName_LCL"
        Me.cboStreetName1.SelectedValue = 0

'-------------fill post code combobox

Dim PostcodeDA As New SqlDataAdapter()
        Dim DSpostcode As New AMRtoSQLDataSet
        ' DSpostcode.Clear()
        ' Dim SN As String
        PostcodeDA.SelectCommand = New SqlCommand()
        PostcodeDA.SelectCommand.Connection = conn
        PostcodeDA.SelectCommand.CommandText = "SELECT Postcode,StreetName_LCL FROM T_MUNICIPALITY_STREETS WHERE StreetName_LCL= '" & cboStreetName1.SelectedIndex.ToString & "'"
        PostcodeDA.Fill(DSpostcode, "PostcodeTable")
        cboPostcode.DataSource = DSpostcode.Tables("PostcodeTable")
        cboPostcode.DisplayMember = "Postcode"
        cboPostcode.ValueMember = "Postcode"
        cboPostcode.SelectedValue = 0
  • 2 Contributors
  • 3 Replies
  • 1K Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Smith5646

Dani AI

Generated

As reported, the immediate bug was the use of SelectedIndex.ToString — use the combo box text or its SelectedValue instead (as noted). Beyond that fix, two practical improvements make the cascaded boxes robust, safe, and easier to maintain: use parameterized queries (to handle apostrophes and avoid SQL injection) and avoid requerying the database on every click if the dataset is small (use a cached DataTable + DataView filter).

Example (populate postcodes safely for the selected street):

Using cmd As New SqlCommand("SELECT DISTINCT Postcode FROM T_MUNICIPALITY_STREETS WHERE StreetName_LCL = @Street ORDER BY Postcode", conn)
    cmd.Parameters.Add("@Street", SqlDbType.NVarChar, 200).Value = cboStreetName1.Text
    Dim dt As New DataTable()
    Using da As New SqlDataAdapter(cmd)
        da.Fill(dt)
    End Using
    cboPostcode.DataSource = dt
    cboPostcode.DisplayMember = "Postcode"
    cboPostcode.ValueMember = "Postcode"
End Using

Troubleshooting / best practices

  • Clear or set DataSource = Nothing on dependent combos when no selection exists (check SelectedIndex < 0) to avoid exceptions.
  • Use SELECT DISTINCT to prevent duplicate entries; sort with ORDER BY.
  • If your full table is reasonably small, load it once and use a DataView.RowFilter = "StreetName_LCL = '...'" to populate dependent lists quickly without DB round-trips.
  • Always dispose connections/commands (use Using) and test values with Debug.WriteLine or MessageBox.Show while developing.

Recommended Answers

All 3 Replies

Member Avatar for Smith5646

Line 28 should be cboStreetName1.Text. .selectedindex.tostring returns the number of the selected index. As a debugging hint, I often use MessageBox.Show to make sure I am getting the value that I am expecting as in MessageBox.Show(cboStreetName1.SelectedIndex.ToString)

I am assuming that you are wrapping lines 22-33 in a SelectedIndexChanged block so it will load the proper values when they select a different street.

Member Avatar for skran

thank you so so much!!!! :D
it works!!

Member Avatar for Smith5646

One other comment. Since you initialize the .SelectedValue to 0, this works as expected.

Be aware that if you do not initialize it, the value is -1 and trying to retrieve the text when ComboBox.SelectedValue = -1 will blow up.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.