Member Avatar for kampao00

hi! kindly help meeeee :D
Populating the combo box with the list of items from the one of the column in table from database.
Based on the selection of item in the combo box, the another combobox should be populated with value corresponding to the selected item in the first combo box.

Here's my code :

 Imports System.Data.Sql
 Imports System.Data.SqlClient
 Imports System.Data.OleDb


 Public Class Form2
    Sub fillcombo()
    strsql = "select distinct YearLevels from Schedulings"
    Dim acscmd = New OleDb.OleDbCommand
    acscmd.CommandText = strsql
    acscmd.Connection = asconn
    acsdr = acscmd.ExecuteReader()
    While (acsdr.Read())
        ComboBox1.Items.Add(acsdr("YearLevels"))

    End While
    acscmd.Dispose()
    acsdr.Close()
    End Sub


Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    EnlistmentModule.connect()
    Me.fillcombo()


    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
    strsql = "select * from Schedulings where YearLevels = '" & ComboBox1.Text & "'"
    Dim acscmd As New OleDb.OleDbCommand
    acscmd.CommandText = strsql
    acscmd.Connection = asconn
    acsdr = acscmd.ExecuteReader()
    If acsdr.Read = True Then
        ComboBox2.Items.Add(acsdr("Sections"))

    End If
    ComboBox2.SelectedIndex = -1
    acscmd.Dispose()
    acsdr.Close()

       End Sub


    End Class

The problem with this code only the first row that is populate in the 2nd combo box.
help me pls. this is urgent. thank you.

  • 3 Contributors
  • 6 Replies
  • 1K Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by kampao00

Dani AI

Generated

Nice catch by on reading all rows, and on clearing before repopulating. To make this robust in WinForms VB.NET, bind both controls instead of pushing items manually. Binding avoids duplicates, is easier to refresh, and lets you control what is shown with DisplayMember/ValueMember. How to bind a ComboBox to data.

Example that loads the data once and filters the second combo when the user commits a new selection:

' Form-level cache so we do not requery on every change
Private dt As DataTable

Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    ' TODO: set your real connection string
    Dim connString = "Provider=...;Data Source=...;"
    Using con As New OleDb.OleDbConnection(connString)
        Using da As New OleDb.OleDbDataAdapter(
            "SELECT YearLevels, Sections FROM Schedulings", con)
            dt = New DataTable()
            da.Fill(dt)
        End Using
    End Using  ' Using guarantees proper disposal. https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/using-statement

    Dim years = dt.DefaultView.ToTable(True, "YearLevels") ' distinct
    ComboBox1.DisplayMember = "YearLevels"
    ComboBox1.ValueMember = "YearLevels"
    ComboBox1.DataSource = years
End Sub

Private Sub ComboBox1_SelectionChangeCommitted(sender As Object, e As EventArgs) _
    Handles ComboBox1.SelectionChangeCommitted
    Dim yl = ComboBox1.SelectedValue.ToString().Replace("'", "''")
    Dim dv As New DataView(dt) : dv.RowFilter = "YearLevels = '" & yl & "'" ' filter
    ComboBox2.DataSource = Nothing                         ' avoid merging prior items
    ComboBox2.DisplayMember = "Sections"
    ComboBox2.ValueMember = "Sections"
    ComboBox2.DataSource = dv.ToTable(True, "Sections")    ' distinct sections
    ComboBox2.SelectedIndex = -1
End Sub

Notes:

  • Use SelectionChangeCommitted so ComboBox2 refreshes only after a user action, not when you change ComboBox1 in code. ComboBox.SelectionChangeCommitted.
  • The RowFilter and ToTable(True, ...) calls let you filter and get distinct values without another trip to the database. DataView.RowFilter, DataView.ToTable.
  • If you prefer to query per selection, use a parameterized WHERE YearLevels = ? and fill a DataTable; do not concatenate strings. Configuring parameters.

Recommended Answers

All 6 Replies

Member Avatar for Jx_Man

Use looping to read all data.

While (acsdr.Read())
ComboBox2.Items.Add(acsdr("Sections"))
End While
Edited by Jx_Man because: code tag
commented: True +2
Member Avatar for Mitja Bonca

Sure, change if loops with while.

Member Avatar for kampao00

oh thanx! it solves my problem :)

but there's another problem ..
when i first select the items on the combobox1 then the second combobox populated to its corresponding items
then when i tried it again ,the items that corresponds my first selection on combobox1 , combines on the corresponding items to the first try i made.

Member Avatar for Mitja Bonca

clear comboBox2 before loading it:
comboBox1.Items.Clear()

Member Avatar for kampao00

@Mitja it works . but it doesnt show the selected item in the combobox.

Member Avatar for kampao00

ah it works. :D

thanx. :D

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.