vb.netdictionarycomboboxbindingdatasource

why doesn't appear Binding combobox using dictionary with the MS ACCESS database with dapper in VB.NET


I'm trying binding combobox using dictionary with the MS ACCESS database with dapper in VB.NET.

so I want the binding using dictionary to appear in the combobox which is the Result of the table Dictionarytest

Is there perhaps something wrong with my code implementation?

Please Guide Me

Thanks

Public Class Form4
    Private bindingSource1 As BindingSource = Nothing
    Private connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\dapperdemo.accdb;Persist Security Info=False;"
    Private CBFullList As Dictionary(Of String, Integer)
  Private Sub BindcomboboxColorCode()
        Using conn = New OleDbConnection(connectionString)
            conn.Open()
            Dim sql = "SELECT * FROM Dictionarytest"
            Dim CBFullList = conn.Query(Of Dictionarytest)(sql).ToDictionary(Function(row) row.Result, Function(row) row.Key)
            conn.Close()
        End Using
        ComboBox1.DisplayMember = "Key"
        ComboBox1.ValueMember = "Result"
        ComboBox1.DropDownHeight = 80
        ComboBox1.DropDownStyle = ComboBoxStyle.DropDown
        ComboBox1.AutoCompleteMode = AutoCompleteMode.None
        ComboBox1.AutoCompleteSource = AutoCompleteSource.None
        ComboBox1.DataSource = New BindingSource(CBFullList.ToList(), Nothing)
    End Sub
Private Sub Form4_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        CBFullList = New Dictionary(Of String, Integer)()
        BindcomboboxColorCode()
    End Sub
End Class
Public Class Dictionarytest
    Public Property Key() As Integer
    Public Property Result() As String
End Class

Result db in ms access

db in ms access

answer update code from @jmcilhinney

Public Class Form4
    Private bindingSource1 As BindingSource = Nothing
    Private connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\dapperdemo.accdb;Persist Security Info=False;"
    Private CBFullList As Dictionary(Of Integer, String)
 Private Sub BindcomboboxColorCode()
        Using conn = New OleDbConnection(connectionString)
            conn.Open()
            Dim sql = "SELECT * FROM Dictionarytest"
            Dim CBFullList = conn.Query(Of Dictionarytest)(sql).ToDictionary(Function(row) row.Key, Function(row) row.Result)
            conn.Close()
        End Using
With ComboBox1
            .DisplayMember = "Value"
            .ValueMember = "Key"
            .DataSource = CBFullList.ToArray()
        End With
    End Sub
 Private Sub Form4_Load(sender As Object, e As EventArgs) Handles MyBase.Load
 BindcomboboxColorCode()
    End Sub
End Class

update code from @dr.null

Private Sub BindcomboboxColorCode()
        Using conn = New OleDbConnection(connectionString)
            conn.Open()
            Dim sql = "SELECT * FROM Dictionarytest"
CBFullList = conn.Query(Of Dictionarytest)(sql).ToDictionary(Function(row) row.Key, Function(row) row.Result)
End Using
        With ComboBox1
            .DisplayMember = "Value"
            .ValueMember = "Key"
                .DropDownHeight = 80
                .DropDownStyle = ComboBoxStyle.DropDown
                .AutoCompleteMode = AutoCompleteMode.None
                .AutoCompleteSource = AutoCompleteSource.None
                .DataSource = CBFullList.ToArray()

            End With

    End Sub

Solution

  • There are two things to address here. Firstly, the specific reason that your code isn't working. Secondly, what you should be doing to make it work and work better.

    The specific reason it's not working is that you are binding an empty Dictionary instead of the one you populate from the query. You declare a field here:

    Private CBFullList As Dictionary(Of String, Integer)
    

    You then pointlessly create an empty Dictionary and assign it to that field here:

    CBFullList = New Dictionary(Of String, Integer)()
    

    You then ignore that field and assign your populated Dictionary to a local variable that immediately goes out of scope here:

    Dim CBFullList = conn.Query(Of Dictionarytest)(sql).ToDictionary(Function(row) row.Result, Function(row) row.Key)
    

    You then bind the empty Dictionary that you should not even have created here:

    ComboBox1.DataSource = New BindingSource(CBFullList.ToList(), Nothing)
    

    Even if you fix that by assigning the populated Dictionary to the field instead of a local variable, your code is still not going to work, because your binding is wrong.

    I would map the Key and Result properties of your query results to the Key and Value properties of the Dictionary items. That is unless the table may have duplicate values in the Key column, but that would be strange, given the name.

    Private CBFullList As Dictionary(Of Integer, String)
    
    CBFullList = conn.Query(Of Dictionarytest)(sql).ToDictionary(Function(row) row.Key, Function(row) row.Result)
    

    I would then display the Value of the items in the ComboBox:

    With ComboBox1
        .DisplayMember = "Value"
        .ValueMember = "Key"
        .DataSource = CBFullList.ToArray()
    End With
    

    You can use a BindingSource if you want but, if it makes sense to use one, you should add it to the form in the designer.