Messiaeno Messiaeno - 3 months ago 21
Vb.net Question

Filling a combobox using a query with a where command (VB.NET)

I have problems with filling a combobox.
I'm able to fill one whith a simple select query.
Now I want to fill a combobox with a query that inculdes a where command. I tried several solutions but none of them worked.
How can I fill a combobox with a query with a where statement?

My code so far is:

Public Function vulComboboxTesten(box As ComboBox, naam As String) As ComboBox
box.Items.Clear()
box.Items.Add(" ")
Dim query As String
query = "Select Sector from Onderaannemers where Naam_firma = @naam "
Debug.WriteLine(query)
Dim command As OleDbCommand
command = New OleDbCommand(query, connectie)

command.Connection.Open()

Dim datareader As OleDbDataReader
datareader = command.ExecuteReader

While datareader.Read
Dim item As New ComboBoxItem
item.Content = datareader("Sector")
box.Items.Add(item)
End While

command.Connection.Close()
Return box
End Function

Answer

Try this sir. i dont know how to translate my code into your type of coding. But just read the explanations.

Private Sub StoringDatainCombobox()
    Try
        Dim comboSource As New Dictionary(Of String, String)()
        mkcon() 'this is just my sqlCon.Open
        Dim cmd As New SqlCommand
        Dim rd As SqlDataReader

        cmd.Connection = sqlCon
        cmd.CommandText = "data_get" 'my query stored procedure.
        'cotains something like this select * from tb1 where isactive = true
        cmd.CommandType = CommandType.StoredProcedure

        comboSource.Add(0, "") ' for blank item in the 1st value of combobox
        ComboBox1.DataSource = New BindingSource(comboSource, Nothing)
        ComboBox1.DisplayMember = "Value"
        ComboBox1.ValueMember = "key"
        ComboBox1.Text = ""

        rd = cmd.ExecuteReader
        While (rd.Read)
                comboSource.Add(rd("dataId"), rd("dataName"))
                ComboBox1.DataSource = New BindingSource(comboSource, Nothing)
                ComboBox1.DisplayMember = "Value"
                ComboBox1.ValueMember = "key"
                ComboBox1.Text = ""
        End While
        sqlCon.Close()
    Catch ex As Exception
        MessageBox.Show("Failed." & ex.Message, "Failed", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
End Sub

You can now display the data result of your query also with an id. if you need how to get the id when user select an item in combobox just inform me.

Hope this will help you.

Comments