locked
Split Text In A Combobox Item To Multiple Items RRS feed

  • Question

  • A drug index software

    simply every combobox is filled from a field in the database like this:


    but as marked with red arrow in field Routes when i choose dose 20

    the opposite value B&B is listed in combobox named (Route) like this:


    i want it to be split in separate items by the separator (&) to be viewed like this:


    this is my code:

    Dim cnn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Medication.accdb")
    
       Dim da As OleDbDataAdapter
    
       Dim cm As OleDbCommandBuilder
    
       Dim cmd As OleDbCommand
    
       Dim itemRoute As String()
    
       Private Sub MedType1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles MedType1.SelectedIndexChanged
    
           MedName1.Items.Clear()
    
           MedDose1.Items.Clear()
    
           MedRoute1.Items.Clear()
    
           If MedType1.SelectedItem = ("Antibiotics") Then
    
               Dim dt1 As New DataTable
    
               dt1.Clear()
    
               Dim sql As String = "SELECT * FROM Antibiotics"
    
               da = New OleDbDataAdapter(sql, cnn)
    
               cm = New OleDbCommandBuilder(da)
    
               da.Fill(dt1)
    
               For ii As Integer = 0 To dt1.Rows.Count - 1
    
                   MedName1.Items.Add(dt1(ii)(0))
    
               Next
    
           End If
    
       End Sub
    
       Private Sub MedName1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles MedName1.SelectedIndexChanged
    
           MedDose1.Items.Clear()
    
           MedRoute1.Items.Clear()
    
           If MedType1.SelectedItem = ("Antibiotics") Then
    
               Dim dt2 As New DataTable
    
               dt2.Clear()
    
               Dim sql2 As String = "SELECT * FROM Antibiotics WHERE Antibiotics = '" & MedName1.SelectedItem & "'"
    
               da = New OleDbDataAdapter(sql2, cnn)
    
               cm = New OleDbCommandBuilder(da)
    
               da.Fill(dt2)
    
               Dim doses As String = dt2(0)(1)
    
               Dim dose As String() = doses.Split("-")
    
               For ii As Integer = 0 To dose.Count - 1
    
                   MedDose1.Items.Add(dose(ii))
    
               Next
    
               Dim routes As String = dt2(0)(2)
    
               Dim route As String() = routes.Split("-")
    
               itemRoute = route
    
           End If
    
       End Sub



    • Edited by Doctor GME Monday, March 30, 2020 9:51 AM
    Monday, March 30, 2020 9:50 AM

Answers

  • the problem is solved but combobox2 (cb2) isn't viewing right
    even after some modifications
    it only views items of one phrase (System.Data.DataRowView)
    it looks like the data isn't bound to database
    my code:
    Imports System.Data.OleDb
    Public Class Form1
    
        Private dt As DataTable
        Dim cnn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Medication.accdb")
        Dim da As OleDbDataAdapter
    
        Private Sub cb1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cb1.SelectedIndexChanged
    
            If cb1.SelectedItem = "Antibiotics" Then
                Try
                    dt = New DataTable
                    Using da As New OleDbDataAdapter("SELECT * FROM Antibiotics", cnn)
                        da.Fill(dt)
                    End Using
                    cb2.DataSource = dt
    
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End If
            If cb1.SelectedItem = "Analgesics" Then
                Try
                    dt = New DataTable
                    Using da As New OleDbDataAdapter("SELECT * FROM Analgesics", cnn)
                        da.Fill(dt)
                    End Using
                    cb2.DataSource = dt
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End If
        End Sub
        Private Sub cb2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cb2.SelectedIndexChanged
            cb3.DataSource = Nothing
            Dim d = TryCast(cb2.SelectedItem, DataRowView)
            If d Is Nothing Then Exit Sub
            Dim l As New List(Of String)(d("Doses").ToString.Split("-"c))
            cb3.DataSource = l
        End Sub
        Private Sub cb3_SelectedValueChanged(sender As Object, e As EventArgs) Handles cb3.SelectedValueChanged
            cb4.DataSource = Nothing
            Dim d = TryCast(cb2.SelectedItem, DataRowView)
            If d Is Nothing Then Exit Sub
            Dim i = cb3.SelectedIndex
            If i >= 0 Then
                Dim l As New List(Of String)(d("Routes").ToString.Split("-"c)(i).Split("#"c))
                cb4.DataSource = l
            End If
        End Sub
    
    End Class

    • Marked as answer by Doctor GME Monday, March 30, 2020 8:37 PM
    Monday, March 30, 2020 5:57 PM

All replies

  • Hi,
    try following demo:

    Public Class Form1
    
      Private WithEvents cb1 As New ComboBox With {.Dock = DockStyle.Left, .DisplayMember = "Antibiotic"}
      Private WithEvents cb2 As New ComboBox With {.Dock = DockStyle.Left}
      Private cb3 As New ComboBox With {.Dock = DockStyle.Left}
    
      Private data As List(Of clsData)
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {cb3, cb2, cb1})
        data = GetData()
        '
        cb1.DataSource = data
      End Sub
    
      Private Sub cb1_SelectedValueChanged(sender As Object, e As EventArgs) Handles cb1.SelectedValueChanged
        cb2.DataSource = Nothing
        Dim d = TryCast(cb1.SelectedItem, clsData)
        If d Is Nothing Then Exit Sub
        Dim l As New List(Of String)(d.Doses.Split("-"c))
        cb2.DataSource = l
      End Sub
    
      Private Sub cb2_SelectedValueChanged(sender As Object, e As EventArgs) Handles cb2.SelectedValueChanged
        cb3.DataSource = Nothing
        Dim d = TryCast(cb1.SelectedItem, clsData)
        If d Is Nothing Then Exit Sub
        Dim i = cb2.SelectedIndex
        Dim l As New List(Of String)(d.Routes.Split("-"c)(i).Split("&"c))
        cb3.DataSource = l
      End Sub
    
      Private Function GetData() As List(Of clsData)
        Dim l = New List(Of clsData)
        l.Add(New clsData With {.Antibiotic = "Augmentin", .Doses = "10-20-30", .Routes = "AA-B&B-CC"})
        l.Add(New clsData With {.Antibiotic = "Curam", .Doses = "&", .Routes = "&"})
        Return l
      End Function
    
      Public Class clsData
        Public Property Antibiotic As String
        Public Property Doses As String
        Public Property Routes As String
      End Class
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Monday, March 30, 2020 10:16 AM
  • When examining what has been presented my first thought is there should be a table for Antibiotics (master table), Doses, child of antibiotics (master to Routes) and a Routes table child of Doses. Any time I see a design were data is a field needs to be split there is a poor database design (and I realize this is not your field of expertise).

    So with the above master-detail laid out, in code you would have a DataSet, load the Antibiotics table into the DataSet using a OleDbDataAdapter the repeat for the other tables which would be done in a class which a form calls. The first two tables become the DataSource of a BindingSource component.

    What is key to the above working is that each table has a primary auto incrementing primary key and the two child tables link backwards using a foreign key. Then when making selections in the ComboBox controls no code is required to do the filtering and no need to read data again from the database.

    For someone who has never done master-detail the above may be daunting, seem like excess work but this is the right way, easy is not always the right way. Others will (and already have) provide ideas to fit into your current setup but that is wrong.

    I don’t have an exact code sample but do have working example that provides the pattern needed.

    The following project is a class project which contains all data operations for reading.

    The following windows form project (uses the above class project) shows how to link all together.

    If the code idea above was too much but the database changes are okay the following shows an example that does not link tables once but filters on SelectedIndexChanged event done with two ComboBox controls where adding a third is the same as done with the current ones. 

    Partial Public Class Form1
    	Inherits Form
    
    	Private ReadOnly _productBindingSource As New BindingSource()
    	Public Sub New()
    		InitializeComponent()
    		AddHandler CategoryComboBox.SelectedIndexChanged, AddressOf CategoryComboBox_SelectedIndexChanged
    	End Sub
    
    	Private Sub CategoryComboBox_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    		If CategoryComboBox.SelectedItem Is Nothing Then
    			Return
    		End If
    
    		Dim categoryIdentifier = CType(CategoryComboBox.SelectedItem, DataRowView).Row.Field(Of Integer)("CategoryId")
    
    		_productBindingSource.Filter = $"CategoryId = {categoryIdentifier}"
    	End Sub
    
    	Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
    		Dim ops = New DataOperations()
    		CategoryComboBox.DisplayMember = "CategoryName"
    
    		CategoryComboBox.DataSource = ops.CategoryDataTable()
    
    		ProductComboBox.DisplayMember = "ProductName"
    
    		_productBindingSource.DataSource = ops.ProductDataTable()
    		ProductComboBox.DataSource = _productBindingSource
    	End Sub
    End Class
    

    Each table is loaded w/o linking shown done in SQL-Server yet works by simply changing the data provider.

    Public Class DataOperations
    	Inherits SqlServerConnection
    
    	''' <summary>
    	''' Create database connection string. If your server name
    	''' is not .\SQLEXPRESS then the first line of code
    	''' below needs to change to your SQL-Server name.
    	'''
    	''' This also needs to be done in the class NorthWindEntityCore
    	''' in OnConfiguring method of the class NorthWindContext
    	''' </summary>
    	Public Sub New()
    		DatabaseServer = ".\SQLEXPRESS"
    
    		If Environment.UserName.ToLower().Contains("karen") Then
    			DatabaseServer = "KARENS-PC"
    		End If
    
    		DefaultCatalog = "NorthWindAzure"
    	End Sub
    
    	Public Function CategoryDataTable() As DataTable
    		Dim dt = New DataTable()
    
    		Using cn = New SqlConnection(ConnectionString)
    			Using cmd = New SqlCommand() With {.Connection = cn}
    				Dim selectStatement = "SELECT CategoryId, CategoryName " &
    					"FROM Categories ORDER BY CategoryName"
    
    				cmd.CommandText = selectStatement
    				cn.Open()
    				dt.Load(cmd.ExecuteReader())
    			End Using
    		End Using
    
    		Return dt
    	End Function
    	Public Function ProductDataTable() As DataTable
    		Dim dt = New DataTable()
    
    		Using cn = New SqlConnection(ConnectionString)
    			Using cmd = New SqlCommand() With {.Connection = cn}
    				Dim selectStatement = "SELECT ProductId,CategoryId, ProductName " &
    					"FROM Products ORDER BY ProductName"
    
    				cmd.CommandText = selectStatement
    				cn.Open()
    				dt.Load(cmd.ExecuteReader())
    			End Using
    		End Using
    
    		Return dt
    	End Function
    End Class
    

    Important on all above.

    Each table needs primary keys and child tables to a column linking back to the parent table.

    Note

    When going the first route with linked tables there are methods that allow you to ask, who is my parent row in a parent table and also the parent can ask, who are my child DataRows.


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, March 30, 2020 10:52 AM
  • Sorry, I have only plain Windows OS here. rar is not supported.


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Monday, March 30, 2020 12:01 PM
  • Hi,
    try following demo:

    Public Class Form1
    
      Private WithEvents cb1 As New ComboBox With {.Dock = DockStyle.Left, .DisplayMember = "Antibiotic"}
      Private WithEvents cb2 As New ComboBox With {.Dock = DockStyle.Left}
      Private cb3 As New ComboBox With {.Dock = DockStyle.Left}
    
      Private data As List(Of clsData)
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {cb3, cb2, cb1})
        data = GetData()
        '
        cb1.DataSource = data
      End Sub
    
      Private Sub cb1_SelectedValueChanged(sender As Object, e As EventArgs) Handles cb1.SelectedValueChanged
        cb2.DataSource = Nothing
        Dim d = TryCast(cb1.SelectedItem, clsData)
        If d Is Nothing Then Exit Sub
        Dim l As New List(Of String)(d.Doses.Split("-"c))
        cb2.DataSource = l
      End Sub
    
      Private Sub cb2_SelectedValueChanged(sender As Object, e As EventArgs) Handles cb2.SelectedValueChanged
        cb3.DataSource = Nothing
        Dim d = TryCast(cb1.SelectedItem, clsData)
        If d Is Nothing Then Exit Sub
        Dim i = cb2.SelectedIndex
        Dim l As New List(Of String)(d.Routes.Split("-"c)(i).Split("&"c))
        cb3.DataSource = l
      End Sub
    
      Private Function GetData() As List(Of clsData)
        Dim l = New List(Of clsData)
        l.Add(New clsData With {.Antibiotic = "Augmentin", .Doses = "10-20-30", .Routes = "AA-B&B-CC"})
        l.Add(New clsData With {.Antibiotic = "Curam", .Doses = "&", .Routes = "&"})
        Return l
      End Function
    
      Public Class clsData
        Public Property Antibiotic As String
        Public Property Doses As String
        Public Property Routes As String
      End Class
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    thanks for your effort
    i think it may work with some modifications
    this is the link to the project after my modifications:
    Demo3
    just need to bind the database (in the first few lines)
    with cb3 function (Private Sub cb2_SelectedValueChanged)
    Monday, March 30, 2020 12:02 PM
  • Sorry, I have only plain Windows OS here. rar is not supported.


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    this is the form design


    and this is my code:
    Imports System.Data.OleDb
    Public Class Form1
    
        Dim cnn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Medication.accdb")
        Dim da As OleDbDataAdapter
        Dim cm As OleDbCommandBuilder
        Dim cmd As OleDbCommand
    
        Private Sub cb1_SelectedValueChanged(sender As Object, e As EventArgs) Handles cb1.SelectedValueChanged
            cb2.Items.Clear()
            cb3.Items.Clear()
            cb4.Items.Clear()
            If cb1.SelectedItem = ("Analgesics") Then
                Dim dt1 As New DataTable
                dt1.Clear()
                Dim sql As String = "SELECT * FROM Analgesics"
                da = New OleDbDataAdapter(sql, cnn)
                cm = New OleDbCommandBuilder(da)
                da.Fill(dt1)
                For ii As Integer = 0 To dt1.Rows.Count - 1
                    cb2.Items.Add(dt1(ii)(0))
                Next
            End If
    
            If cb1.SelectedItem = ("Antibiotics") Then
                Dim dt1 As New DataTable
                dt1.Clear()
                Dim sql As String = "SELECT * FROM Antibiotics"
                da = New OleDbDataAdapter(sql, cnn)
                cm = New OleDbCommandBuilder(da)
                da.Fill(dt1)
                For ii As Integer = 0 To dt1.Rows.Count - 1
                    cb2.Items.Add(dt1(ii)(0))
                Next
            End If
        End Sub
    
        Private Sub cb2_SelectedValueChanged(sender As Object, e As EventArgs) Handles cb2.SelectedValueChanged
            cb3.DataSource = Nothing
            Dim d = TryCast(cb1.SelectedItem, clsData)
            If d Is Nothing Then Exit Sub
            Dim i = cb2.SelectedIndex
            Dim l As New List(Of String)(d.Routes.Split("-"c)(i).Split("&"c))
            cb3.DataSource = l
        End Sub
    
        Private Function GetData() As List(Of clsData)
            Dim l = New List(Of clsData)
            l.Add(New clsData With {.Antibiotic = "Augmentin", .Doses = "10-20-30", .Routes = "AA-B&B-CC"})
            l.Add(New clsData With {.Antibiotic = "Curam", .Doses = "&", .Routes = "&"})
            Return l
        End Function
    
        Public Class clsData
            Public Property Antibiotic As String
            Public Property Doses As String
            Public Property Routes As String
    
        End Class
    
    End Class

    Monday, March 30, 2020 12:11 PM
  • it is solved with the simplest way
    just the data enters a textbox first then be split in combobox
    • Marked as answer by Doctor GME Monday, March 30, 2020 2:13 PM
    • Unmarked as answer by Doctor GME Monday, March 30, 2020 6:06 PM
    Monday, March 30, 2020 2:13 PM
  • Hi,
    it's not necessary use TextBox. Try following demo:

    Imports System.Data.OleDb
    
    Public Class Form1
    
      Private lbl1 As New Label With {.Text = "Type", .Top = 50, .Left = 50}
      Private lbl2 As New Label With {.Text = "Drug", .Top = 50, .Left = 200}
      Private lbl3 As New Label With {.Text = "Dose", .Top = 50, .Left = 350}
      Private lbl4 As New Label With {.Text = "Route", .Top = 50, .Left = 500}
      Private WithEvents cb1 As New ComboBox With {.Top = 80, .Left = 20}
      Private WithEvents cb2 As New ComboBox With {.Top = 80, .Left = 170, .DisplayMember = "Antibiotics"}
      Private WithEvents cb3 As New ComboBox With {.Top = 80, .Left = 320}
      Private cb4 As New ComboBox With {.Top = 80, .Left = 470}
    
      Private dt As DataTable
    
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Me.Controls.AddRange(New Control() {lbl4, lbl3, lbl2, lbl1, cb4, cb3, cb2, cb1})
        '
        cb1.DataSource = New String() {"Analgesics", "Antibiotics"}
        cb1_Click(Nothing, Nothing)
      End Sub
    
      Dim cnn As New OleDbConnection(My.Settings.cnDatabase1)
    
      Private Sub cb1_Click(sender As Object, e As EventArgs) Handles cb1.Click
        Try
          dt = New DataTable
          Using da As New OleDbDataAdapter($"SELECT * FROM {cb1.SelectedItem}", cnn)
            da.Fill(dt)
          End Using
          cb2.DataSource = dt
        Catch ex As Exception
          MsgBox(ex.Message)
        End Try
      End Sub
    
      Private Sub cb2_SelectedValueChanged(sender As Object, e As EventArgs) Handles cb2.SelectedValueChanged
        cb3.DataSource = Nothing
        Dim d = TryCast(cb2.SelectedItem, DataRowView)
        If d Is Nothing Then Exit Sub
        Dim l As New List(Of String)(d("Doses").ToString.Split("-"c))
        cb3.DataSource = l
      End Sub
    
      Private Sub cb3_SelectedValueChanged(sender As Object, e As EventArgs) Handles cb3.SelectedValueChanged
        cb4.DataSource = Nothing
        Dim d = TryCast(cb2.SelectedItem, DataRowView)
        If d Is Nothing Then Exit Sub
        Dim i = cb3.SelectedIndex
        If i >= 0 Then
          Dim l As New List(Of String)(d("Routes").Tostring.Split("-"c)(i).Split("&"c))
          cb4.DataSource = l
        End If
      End Sub
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Monday, March 30, 2020 2:29 PM
  • the problem is solved but combobox2 (cb2) isn't viewing right
    even after some modifications
    it only views items of one phrase (System.Data.DataRowView)
    it looks like the data isn't bound to database
    my code:
    Imports System.Data.OleDb
    Public Class Form1
    
        Private dt As DataTable
        Dim cnn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Medication.accdb")
        Dim da As OleDbDataAdapter
    
        Private Sub cb1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cb1.SelectedIndexChanged
    
            If cb1.SelectedItem = "Antibiotics" Then
                Try
                    dt = New DataTable
                    Using da As New OleDbDataAdapter("SELECT * FROM Antibiotics", cnn)
                        da.Fill(dt)
                    End Using
                    cb2.DataSource = dt
    
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End If
            If cb1.SelectedItem = "Analgesics" Then
                Try
                    dt = New DataTable
                    Using da As New OleDbDataAdapter("SELECT * FROM Analgesics", cnn)
                        da.Fill(dt)
                    End Using
                    cb2.DataSource = dt
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End If
        End Sub
        Private Sub cb2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cb2.SelectedIndexChanged
            cb3.DataSource = Nothing
            Dim d = TryCast(cb2.SelectedItem, DataRowView)
            If d Is Nothing Then Exit Sub
            Dim l As New List(Of String)(d("Doses").ToString.Split("-"c))
            cb3.DataSource = l
        End Sub
        Private Sub cb3_SelectedValueChanged(sender As Object, e As EventArgs) Handles cb3.SelectedValueChanged
            cb4.DataSource = Nothing
            Dim d = TryCast(cb2.SelectedItem, DataRowView)
            If d Is Nothing Then Exit Sub
            Dim i = cb3.SelectedIndex
            If i >= 0 Then
                Dim l As New List(Of String)(d("Routes").ToString.Split("-"c)(i).Split("#"c))
                cb4.DataSource = l
            End If
        End Sub
    
    End Class

    • Marked as answer by Doctor GME Monday, March 30, 2020 8:37 PM
    Monday, March 30, 2020 5:57 PM
  • Hi,
    DisplayMember of cb2 is incorrect. Set the correct name of column in database.

      Private WithEvents cb2 As New ComboBox With {.Top = 80, .Left = 170, .DisplayMember = "Antibiotics"}


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Monday, March 30, 2020 7:17 PM
  • like a charm
    thank you for your efforts
    Monday, March 30, 2020 8:37 PM