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:
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:
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.
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.
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
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:
Private Sub nameComboBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles nameComboBox.SelectedIndexChanged
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)}).
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.
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:
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?
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
Re: vb.net split text in a combobox item to multiple items
Originally Posted by Doctor GME
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.
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
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.
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
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.