Results 1 to 14 of 14

Thread: vb.net split text in a combobox item to multiple items

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jun 2015
    Posts
    17

    vb.net split text in a combobox item to multiple items

    A drug index software
    simply every combobox is filled from a field in the database like this:

    Name:  136488940.jpg
Views: 1160
Size:  23.8 KB

    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:

    Name:  262244685.jpg
Views: 885
Size:  6.8 KB

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

    Name:  840516181.jpg
Views: 877
Size:  6.5 KB

    this is my code:
    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
    Last edited by Shaggy Hiker; Mar 19th, 2020 at 10:33 AM.

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: vb.net split text in a combobox item to multiple items

    First off, do you have any flexibility on the database side of this? Having worked with fields that amount to multiple items packed into a single string, the best advice is to not do that. If you can't change the database, then that's that, but if you CAN change the database, this is the right place for a child table. Those fields that hold multiple values concatenated together should be split out into a different table with a one to many relationship to the main table, or perhaps something a bit more exotic than that, as it seems likely that they will be repeated.
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jun 2015
    Posts
    17

    Re: vb.net split text in a combobox item to multiple items

    thank you for your suggestion
    but this database as i said is for drug index software
    i know it is complicated and could be changed to more easy form
    but like this it has a great advantage as the drug, doses and routes are beside each other
    so no mistake could be made in writing the database which could be fatal
    so i had to do it the hard way i guess

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: vb.net split text in a combobox item to multiple items

    The Antibiotics table should be bound to the second ComboBox. In the appropriate event handler of that ComboBox (SelectedIndexChanged or SelectionChangeCommitted) you can get the other data from the selected row, process it and display it in the last two ComboBoxes. That might look something like this:
    vb.net Code:
    1. Private Sub nameComboBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles nameComboBox.SelectedIndexChanged
    2.     Dim row = DirectCast(nameBindingSource.Current, DataRowView)
    3.     Dim doses = CStr(row("Doses")).Split("-"c)
    4.     Dim routes = CStr(row("Routes")).Split("-"c)
    5.     Dim dosesAndRoutes = Enumerable.Range(0, doses.Length).
    6.                                     Select(Function(n) New With {.Dose = doses(n), .Route = routes(n)}).
    7.                                     ToArray()
    8.  
    9.     doseAndRouteBindingSource.DataSource = dosesAndRoutes
    10.     doseComboBox.DisplayMember = "Dose"
    11.     routeComboBox.DisplayMember = "Route"
    12.     doseComboBox.DataSource = doseAndRouteBindingSource
    13.     routeComboBox.DataSource = doseAndRouteBindingSource
    14. End Sub
    The fourth line creates an array of objects of an anonymous type that each have Dose and Route properties. That array is bound to both of the last two ComboBoxes with a disfferent property shown in each one. Because of the common binding, making a selection in one will also change the other.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jun 2015
    Posts
    17

    Re: vb.net split text in a combobox item to multiple items

    thanks for your effort
    unfortunately i'm not familiar with direct cast operator
    in my code i bound data source like this:
    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
    your suggested code after some modification is:
    Code:
        Private Sub MedName1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles MedName1.SelectedIndexChanged
            If MedType1.SelectedItem = ("Antibiotics") Then
    
                Dim row = DirectCast(nameBindingSource.Current, DataRowView)
                Dim doses = CStr(row("Doses")).Split("-"c)
                Dim routes = CStr(row("Routes")).Split("-"c)
                Dim dosesAndRoutes = Enumerable.Range(0, doses.Length).
                                                Select(Function(n) New With {.Dose = doses(n), .Route = routes(n)}).
                                                ToArray()
    
                doseAndRouteBindingSource.DataSource = dosesAndRoutes
                MedDose1.DisplayMember = "Dose"
                MedRoute1.DisplayMember = "Route"
                MedDose1.DataSource = doseAndRouteBindingSource
                MedRoute1.DataSource = doseAndRouteBindingSource
    
            End If 
       End Sub
    just namebindingsource format is the problem
    the data base picture again is like this:
    Name:  136488940.jpg
Views: 1160
Size:  23.8 KB

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Jun 2015
    Posts
    17

    Re: vb.net split text in a combobox item to multiple items

    uppppppppppppppppppppppp

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: vb.net split text in a combobox item to multiple items

    I think I may have misunderstood the original question. It's becoming apparent that this database design is even worse than I originally thought and it should definitely be changed. Disregarding that though, let me just confirm that my current understand of what you want is correct. Let's say that you had "10-20-30" in the Doses column and "A&B-C&D-E&F" in the Routes column. Are you saying that you want to see three items (10, 20, 30) in the Dose ComboBox and then, if the user selects 10 you want to see two items (A, B) in the Route ComboBox, if the user selects 20 you want to see two items (C, D) in the Route ComboBox and if the user selects 30 you want to see two items (E, F) in the Route ComboBox?

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Jun 2015
    Posts
    17

    Re: vb.net split text in a combobox item to multiple items

    exactly what i want
    i know that the database design is awful
    but as i said, it is the only way to add medication doses without a fatal mistake
    another note
    not all doses will have more than one route
    it may be (10-20-30) and just (A-C&D-E) as i mentioned above

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: vb.net split text in a combobox item to multiple items

    Quote Originally Posted by Doctor GME View Post
    but as i said, it is the only way to add medication doses without a fatal mistake
    That's not really true. There is a big difference between the design of a database and the design of the output of a database. The output can look however you want it to look. That has nothing to do with the database design such that there is sufficient information.

    The reason that you feel that it has to be the way that is shown is solely that it is the way people want to look at it. You can make a properly designed database that looks just as you have shown, or even better. We do it all the time in every database I work with: What people see is not the way the data is stored in the database because the way it is stored is done for efficiency, while the way it is displayed is the way people want to see it (sometimes different ways for different people).

    If you are concerned about data entry errors causing bad problems, then that's a problem that requires a better solution than just a database layout. For example, I have a database that was prone to entry errors. It wasn't all that critical, since it only dealt with salmon captures, but the rate of errors in the version before I got there was onerous. Therefore, the old system required that everything be entered twice on two different systems, either by two different people or by the same person on two different days. This reduced data entry errors to near zero, since the odds of entering the correct data incorrectly, twice, and wrong in the same way twice, is vanishingly small. Nobody sees the data the way it is entered in the database, though, because that is nothing more than a LONG string of numbers, which very few people understand. Still, it has a VERY low error rate due to the system of double entering everything (there may not have been any data entered incorrectly in the last twenty years). The display is whatever people want it to be, but the data in the database is reliable not because of the database design, but because of the data entry design. And that's just for fish data.
    My usual boring signature: Nothing

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Jun 2015
    Posts
    17

    Re: vb.net split text in a combobox item to multiple items

    i think you misunderstood my purpose
    i'm not keeping this poor organized database like that because it will be viewed like that
    but i want all of the drug details to be listed in the same row to be easy added
    with no mistake as i said
    i tried to build the database with different organization (please look at attached project)
    if you choose: Antibiotics Then Augmentin, 2 Doses Will Appear:
    375 mg and 600 mg
    but whether you choose the first or the second one, the 2 listed Routes will Appear
    but 375 mg Dose Isn't By injection, only oral
    and if you looked at the 2 previous replies, you will definitely understand me
    Attached Files Attached Files

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: vb.net split text in a combobox item to multiple items

    the Database design is not just awful but also irresponsible !!

    there are atleast 3 Tables missing
    a medication doses should also be based on Age, Weight.... and what if somebodoy is allergic to all Antibiotics (like my Wife)like you said here
    but as i said, it is the only way to add medication doses without a fatal mistake
    I would go back to the drawing board

    @Doctor GME
    are you a Doctor?
    Last edited by ChrisE; Mar 28th, 2020 at 01:14 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Jun 2015
    Posts
    17

    Re: vb.net split text in a combobox item to multiple items

    @ ChrisE
    i'am a dentist
    let me explain to you what i'm programming
    i'm not programming a doctor, details like age, dose or allergy
    is called diagnostic information which is taken be the doctor himself
    and can't be selected automatically as you suggested by adding to database
    this program represent a medical book we call it (Drug Index)
    it contains trade name of the drugs with available doses and route of administration of each dose
    consider it as a reminder to the user of the available drugs in the pharmacies with doses
    the route of administration must be precise as a dose 50 mg of some drug for example may be available as 2 forms
    and this is my aim, to help the user remember the available doses with its routes
    but not to calculate the dose or select the drug, this is done manually by the doctor himself

  13. #13
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: vb.net split text in a combobox item to multiple items

    well if you want to remind a Patient(User) what and when to take a certain medication in which dose...
    I hope you checked the Legal side of things.

    in Germany we have the same sort of...medical book we call it (Drug Index).... it is updated on the 1st and 15th
    of each month and avalible to general practitioners
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Jun 2015
    Posts
    17

    Re: vb.net split text in a combobox item to multiple items

    thank you for your concern
    it is just a reminder and won't be officially used as i said

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width