Vb.Net Ms Access: Filter Combobox Untuk Ditampilkan Di Combobox Lain

Untuk artikel yang satu ini Rani agak gundah memberi judul bahu-membahu.
Intinya kalau terdapat 2 buah combobox yang ingin menampilkan data dari database, dimana performa combobox kedua merupakan hasil filter dari combobox pertama.

Dua tabel dari database yang akan kita gunakan adalah:
Tabel Kategori:

Tabel Item:

Atur UI di form sebagai berikut:

Seperti lazimkita perlu mengimport OLEDB untuk membuat koneksi Access, di bab paling atas jendela code.
Imports System.Data.OleDb

Kemudian di antara area class form kita deklarasikan object koneksi dan buat suatu sub procedure untuk mebuka koneksi.
Dim con As New OleDbConnection   

Sub Open_Koneksi()
    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0" & _
                           ";Data Source=latihan.accdb;" & _
                           "Persist Security Info=False;"
    con.Open()
End Sub

Kode untuk memperlihatkan data di ComboBox1
  Sub ListKategori()
      Dim cmd As New OleDbCommand
      Dim adapt As New OleDbDataAdapter
      Dim dt As New DataTable

      Try
          If Not con.State = ConnectionState.Open Then Open_Koneksi()

          cmd.Connection = con
          cmd.CommandText = "Select kodekategori, namakategori From klasifikasi"

          adapt.SelectCommand = cmd
          adapt.Fill(dt)

          ComboBox1.DataSource = dt
          ComboBox1.DisplayMember = "namakategori"
          ComboBox1.ValueMember = "kodekategori"

          con.Close()
      Catch myerror As OleDbException
          MessageBox.Show("Error: " & myerror.Message)
      Finally
          con.Dispose()
      End Try
  End Sub

Kode untuk menampilkan data di ComboBox2
Sub ListItem(KodeKategori As String)
    Dim cmd As New OleDbCommand
    Dim adapt As New OleDbDataAdapter
    Dim dt As New DataTable

    Try
        If Not con.State = ConnectionState.Open Then Open_Koneksi()

        cmd.Connection = con
        cmd.CommandText = "Select kodeitem, namaitem From item " & _
                          "WHERE kodekategori = '" & KodeKategori & "'"

        adapt.SelectCommand = cmd
        adapt.Fill(dt)


        ComboBox2.DataSource = dt
        ComboBox2.DisplayMember = "namaitem"
        ComboBox2.ValueMember = "kodeitem"

        con.Close()
    Catch myerror As OleDbException
        MessageBox.Show("Error: " & myerror.Message)
    Finally
        con.Dispose()
    End Try
End Sub

Kita akan mengundang sub ListKategori pada ketika event form load.
Private Sub Form2_Load(sender As Object, _
      e As EventArgs) Handles MyBase.Load

    ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList
    ComboBox2.DropDownStyle = ComboBoxStyle.DropDownList

    ListKategori()
End Sub


Dan memanggil ListItem pada saat event combobox1 selectedvaluechanged.
Private Sub ComboBox1_SelectedValueChanged(sender As Object, _
        e As EventArgs) Handles ComboBox1.SelectedValueChanged
    ListItem(ComboBox1.SelectedValue.ToString)
End Sub

Runtime:



Kode selengkapnya:
Imports System.Data.OleDb

Public Class Form2
  Dim con As New OleDbConnection   

  Sub Open_Koneksi()
      con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0" & _
                             ";Data Source=latihan.accdb;" & _
                             "Persist Security Info=False;"
      con.Open()
  End Sub

  Sub ListKategori()
      Dim cmd As New OleDbCommand
      Dim adapt As New OleDbDataAdapter
      Dim dt As New DataTable

      Try
          If Not con.State = ConnectionState.Open Then Open_Koneksi()

          cmd.Connection = con
          cmd.CommandText = "Select kodekategori, namakategori From kategori"

          adapt.SelectCommand = cmd
          adapt.Fill(dt)

          ComboBox1.DataSource = dt
          ComboBox1.DisplayMember = "namakategori"
          ComboBox1.ValueMember = "kodekategori"

          con.Close()
      Catch myerror As OleDbException
          MessageBox.Show("Error: " & myerror.Message)
      Finally
          con.Dispose()
      End Try
  End Sub

  Sub ListItem(KodeKategori As String)
      Dim cmd As New OleDbCommand
      Dim adapt As New OleDbDataAdapter
      Dim dt As New DataTable

      Try
          If Not con.State = ConnectionState.Open Then Open_Koneksi()

          cmd.Connection = con
          cmd.CommandText = "Select kodeitem, namaitem From item " & _
                            "WHERE kodekategori = '" & KodeKategori & "'"

          adapt.SelectCommand = cmd
          adapt.Fill(dt)


          ComboBox2.DataSource = dt
          ComboBox2.DisplayMember = "namaitem"
          ComboBox2.ValueMember = "kodeitem"

          con.Close()
      Catch myerror As OleDbException
          MessageBox.Show("Error: " & myerror.Message)
      Finally
          con.Dispose()
      End Try
  End Sub

  Private Sub Form2_Load(sender As Object, _
        e As EventArgs) Handles MyBase.Load

      ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList
      ComboBox2.DropDownStyle = ComboBoxStyle.DropDownList

      ListKategori()
  End Sub

  Private Sub ComboBox1_SelectedValueChanged(sender As Object, _
          e As EventArgs) Handles ComboBox1.SelectedValueChanged
      ListItem(ComboBox1.SelectedValue.ToString)
  End Sub

End Class


Click here if you like this article.



Sumber http://rani-irsan.blogspot.com