vb.netms-accessdatatablecheckedlistbox

vb.net CheckedListbox get value from database


I am populating a CheckedListBox from a MsAccess database table. The table consists of two fields, Terms and RegX. I want to display Terms but when I submit I want to get the value form the RegX field.

Public Function GetMyTable() As DataTable
        ' Create new DataTable instance.
        Dim table As New DataTable

        Dim strSql As String = "SELECT * FROM Keywords ORDER BY Terms ASC"

        Dim cmd As New OleDbCommand(strSql, con)
        Using dr As OleDbDataReader = cmd.ExecuteReader
            table.Load(dr)
        End Using

        Return table
    End Function

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

        Dim dt1 As DataTable = GetMyTable()

        If dt1.Rows.Count > 0 Then
            For i As Integer = 0 To dt1.Rows.Count - 1
                CheckedListBox1.Items.Add(CStr(dt1.Rows(i).Item("Terms")), False)
            Next
        End If
        CheckedListBox1.CheckOnClick = True
    End Sub

I dont know how to return the value of RegX when I click my Submit button


Solution

  • If you want to keep together the information about "Terms" and "RegX" then you should use the DataSource property of the CheckedListBox setting it with the datatable retrieved and then specify what column should be showed in the list.

    Private Sub SearchInDoc_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
        Dim dt1 As DataTable = GetMyTable()
        CheckedListBox1.DataSource = dt1
        CheckedListBox1.DisplayMember = "Terms"
        CheckedListBox1.CheckOnClick = True
    End Sub
    

    Now in your submit button you could add code like this to retrieve the RegX field

    Sub Submit_Click(sender As Object, e As EventArgs) Handles Submit.Click
        For Each row As DataRowView In CheckedListBox1.CheckedItems
            Console.WriteLine(row("RegX"))
        Next
    End Sub
    

    Each element in the CheckedListBox is a DataRowView taken from the table and you can extract the information about the RegX field simply indexing that DataRowView with the name of the column.
    In the example above only the checked items are subject to the loop enumeration. If you want to traverse all the items in the CheckedListBox use CheckedListBox1.Items