none
How to set combobox value after changing value of another RRS feed

  • Question

  • Hello!

    First - sorry for my English if I'll make some errors. It's not often used.

    I have a form with three comboboxes. Their content is create from sql server tables. While the form is loading, I want to complete all three comboboxes and after changing value in the first combobox, change values in another. Problem is that I can create only the first, but values in the second and third are empty. Below is the code only for loading form (for two comboboxes) but even in this case it doesn't work. I don't know what is wrong in code below.

    Imports System.Data.SqlClient Public Class frm_Start Public zapytanieLista As String = "select datdok, doktran_id, nazwa from doktran order by datdok desc" Public zapytanieSamochod As String = "" Public zapytaniePozycja As String = "" Public polaczenie As New SqlConnection("Data Source=SERVER_NAME;Initial Catalog=DATABASE;Persist Security Info=True;User ID=USER;Password=TOPSECRET") Public komendaLista As New SqlCommand(zapytanieLista, polaczenie) Public komendaSamochod As New SqlCommand() Public komendaPozycja As New SqlCommand() Public adapterLista As New SqlDataAdapter(komendaLista) Public adapterSamochod As New SqlDataAdapter() Public adapterPozycja As New SqlDataAdapter() Public tabelaLista As New DataTable() Public tabelaSamochod As New DataTable() Public tabelaPozycja As New DataTable() Private Sub frm_Start_Load(sender As Object, e As EventArgs) Handles MyBase.Load Try If polaczenie.State = ConnectionState.Closed Then polaczenie.Open() End If Catch exc As Exception MsgBox("Błąd połączenia z bazą danych. ") ToolStripLabelPolaczenie.Text = "SQL NOK" End Try If polaczenie.State = ConnectionState.Open Then ToolStripLabelPolaczenie.Text = "SQL OK" adapterLista.Fill(tabelaLista) cmb_ListaWysylkowa.DataSource = tabelaLista cmb_ListaWysylkowa.DisplayMember = "nazwa" cmb_ListaWysylkowa.ValueMember = "doktran_id"

    '-- till this moment everything is OK, I can see the combobox text and combobox value is set properly
    '-- but below code doesn't work in the same way, the second combobox is empty

    zapytanieSamochod = "select doktran_id, doktranpoz_id, samochod_nr from doktranpoz " + "where doktran_id='" + cmb_ListaWysylkowa.SelectedValue.ToString() + "'" komendaSamochod.CommandText = zapytanieSamochod komendaSamochod.Connection = polaczenie adapterSamochod.SelectCommand() = komendaSamochod adapterSamochod.Fill(tabelaSamochod) cmb_NrSamochodu.DataSource = tabelaSamochod cmb_NrSamochodu.DisplayMember = "samochod_nr" cmb_NrSamochodu.ValueMember = "doktranpoz_id" Else Close() End If End Sub Private Sub frm_Start_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed polaczenie.Close() End Sub End Class

    Can you help me in this case, please?  I'm a beginner in VB.net. I just want to know where the problem is. I'll be grateful for your help. 

    Thursday, November 2, 2017 1:09 AM

All replies

  • The basic way to accomplish this is to populate a DataSet, each DataTable in the DataSet will represent data for a ComboBox. Once the DataSet has been populated you setup relations between each DataTable via DataSet.Relations.Add(… the relations are setup by primary keys. So let’s say ComboBox1 data are categories with a primary key of catId which relates to data from products with a primary key of prodId and the last DataTable for say color (color of a product).

    Let’s call our DataSet ds we would create a relation for the first two ComboBoxes like

    ds.Relations.Add( New DataRelation(“CatagoryProduct”, ds.Tables(“Category”).Columns(“catId”), ds.Tables(“Products”).Columns(“proId”)))

    Then setup another relationship following the pattern above for product to colors.

    Now you need to have one BindingSource component for each DataTable in the DataSet. Assign DataTable, setup the ComboBoxes e.g. the DisplayMember and the DataSource (the DataSource will be a BindingSource).

    Following the above pattern means changing a value in the first ComboBox will automatically change the data source filtered to items relating to the first ComboBox. This is true when changing the second ComboBox to the third ComboBox.

    Yes this may sound complex but it works. Alternates are to write a bunch of code for when an item is selected in a ComboBox to filter the next ComboBox which can get ugly and more complex then what I suggested.

    The pattern mentioned above is shown in the following code sample for three DataGridView controls but don't get hung up on the DataGridView controls vs ComboBox controls as the code flow is the same up to when assigning the BindingSource components to a control.

     If you can't get it, sorry I can write an exact code sample until tomorrow afternoon and if you want that and can wait let me know.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, November 2, 2017 1:51 AM
    Moderator
  • Hi Yarek_G,

    DO you like this:

    If yes, you can do this in ComboBox.SelectIndexChanged() event.

    Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            TextBox3.Text = "123"
            Dim sql As String = "select * from Test11"
            ComboBox1.DataSource = loaddate(sql)
            ComboBox1.DisplayMember = "ProductName"
            ComboBox1.ValueMember = "Id"
        End Sub
        Private Function loaddate(sql As String) As DataTable
            Dim dt As New DataTable
            Dim str As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Sample\Data1.mdf;Integrated Security=True"
            Using con As New SqlConnection(str)
                con.Open()
                Using cmd As New SqlCommand(sql, con)
    
                    Dim adapter As New SqlDataAdapter(cmd)
                    adapter.Fill(dt)
                End Using
                con.Close()
            End Using
            Return dt
        End Function
     Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
            If ComboBox1.Items.Count > 0 Then
                If ComboBox1.ValueMember <> String.Empty Then
    
                    Dim value = Convert.ToInt32(ComboBox1.SelectedValue)
                    Dim sql1 As String = "Select * from Test11 where Id=" & value
                    ComboBox2.DataSource = loaddate(sql1)
                    ComboBox2.DisplayMember = "Category"
                    ComboBox2.ValueMember = "Id"
                    ComboBox3.DataSource = loaddate(sql1)
                    ComboBox3.DisplayMember = "Vendor"
                    ComboBox3.ValueMember = "Id"
                    'End If
                End If
            End If
    
    
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 2, 2017 7:12 AM
    Moderator
  • Hi Yarek_G,

    DO you like this:

    If yes, you can do this in ComboBox.SelectIndexChanged() event.

    Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            TextBox3.Text = "123"
            Dim sql As String = "select * from Test11"
            ComboBox1.DataSource = loaddate(sql)
            ComboBox1.DisplayMember = "ProductName"
            ComboBox1.ValueMember = "Id"
        End Sub
        Private Function loaddate(sql As String) As DataTable
            Dim dt As New DataTable
            Dim str As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Sample\Data1.mdf;Integrated Security=True"
            Using con As New SqlConnection(str)
                con.Open()
                Using cmd As New SqlCommand(sql, con)
    
                    Dim adapter As New SqlDataAdapter(cmd)
                    adapter.Fill(dt)
                End Using
                con.Close()
            End Using
            Return dt
        End Function
     Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
            If ComboBox1.Items.Count > 0 Then
                If ComboBox1.ValueMember <> String.Empty Then
    
                    Dim value = Convert.ToInt32(ComboBox1.SelectedValue)
                    Dim sql1 As String = "Select * from Test11 where Id=" & value
                    ComboBox2.DataSource = loaddate(sql1)
                    ComboBox2.DisplayMember = "Category"
                    ComboBox2.ValueMember = "Id"
                    ComboBox3.DataSource = loaddate(sql1)
                    ComboBox3.DisplayMember = "Vendor"
                    ComboBox3.ValueMember = "Id"
                    'End If
                End If
            End If
    
    
        End Sub

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    There is no need to go to the database or use events like this which of course may be fine for a small personal app but when there is a medium to large set of data in a larger app this method will slow things down.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, November 2, 2017 9:39 AM
    Moderator
  • OKay, I rigged up an example using the following MSDN code sample as a base in that I use Operations class and data from a SQL-Server database.

    The SQL-Server database script is in the code sample solution if you want to test this out. The highlighted project has base data operations.

    Here is the form code which requires zero events to setup proper changing of data when a new selection is made. DataOperations_vb is the highlighted project above which is a class project used in WindowsApplication_vb.

    Imports DataOperations_vb
    
    Public Class Form1
        Private bsMaster As New BindingSource()
        Private bsDetails As New BindingSource()
        Private bsOrderDetails As New BindingSource
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim ops As New Operations
    
            ' load all data at once
            ops.LoadData()
    
            ' setup our underlying data source
            bsMaster = ops.Master
            bsDetails = ops.Details
            bsOrderDetails = ops.OrderDetails
    
            '
            ' Setup our ComboBoxes which when
            ' changing selections filters down
            ' the underlying ComboBoxes via
            ' the data relationships we set
            ' in Operations.vb
            '
            cboCustomer.DataSource = bsMaster
            cboCustomer.DisplayMember = "LastName"
    
            cboOrders.DataSource = bsDetails
            cboOrders.DisplayMember = "OrderDate"
    
            cboOrderDetails.DataSource = bsOrderDetails
            cboOrderDetails.DisplayMember = "ProductName"
        End Sub
    
    
    End Class

    Screenshots


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, November 2, 2017 11:36 AM
    Moderator
  • We have more samples around this on our website. 

    I think this one is quite like your problem. 

    http://www.vb-tips.com/WindowsForms3RelatedComboboxes.ASPX

    (And don't talk about your used English, there is no standard English while this is a world forum, so everybody does it in a way wrong).


    Success
    Cor



    Thursday, November 2, 2017 12:46 PM
  • I just want to know where the problem is.

    If all your code is in the Form load, then when you get to the second combo box, your second select statement depends on a selection in the first combo box. Depending how you set up the first combo maybe nothing is selected at first. Move the code for the second combo to the first combo's SelectedIndexChanged event as shown in Cherry's solution.


    Mary

    Friday, November 3, 2017 8:07 AM
  • This might be of assistance, I just published a MSDN code sample that shows how to do this properly without any events.

    The code sample uses ListBox controls yet the code works no different with ComboBox controls as you set in both the DataSource and DisplayMember.

    Note the two rectangles are explained in the code sample and are only displayed when running from the IDE, not outside of the IDE.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, November 3, 2017 9:12 PM
    Moderator