Three Binding Source and two combo box Problems

I have a windows form with a bunch of bound text boxes and three combo boxes. The combo boxes are for looking up values. The values in one of the combo boxes is based off of a selection to another ocmbo box. I attached the code that I'm using and it works however if I move to a record it shows the first available item in stead of the correct one. So if the items in the combo box are 1,2,3 and it's set to 3 and I move back one then forward one it reverts to 1 in the combo box if I save 3 as the vale in the datasource it shows up as 3 but when I load the form it shows 1. Any ideas?
Me.AdjustersBS.DataSource = Me.AdjusterFormDataset1.Adjusters
Me.InsuranceCoBS.DataSource = Me.AdjusterFormDataset1.InsuranceCompanies
Me.LocationBS.DataSource = Me.InsuranceCoBS
        Me.LocationBS.DataMember = "InsuranceCompanies_Locations"
Me.AdjusterBN.BindingSource = Me.AdjustersBS
 
With Me.cboInsuranceCompany
            .ValueMember = "InsuranceCompanyID"
            .DisplayMember = "CompanyName"
            .DataSource = InsuranceCoBS
            .DataBindings.Add("SelectedValue", AdjustersBS, "InsuranceCompanyID")
        End With
 
        With Me.cboLocation
            .ValueMember = "LocationID"
            .DisplayMember = "LocationName"
            .DataSource = LocationBS
            .DataBindings.Add("SelectedValue", AdjustersBS, "LocationID")
        End With

Open in new window

PaulsburbonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SanclerCommented:
The code only deals with two comboboxes, neither of which appears to affect the selection in the other.  And yet you say you have three comboboxes and "The values in one of the combo boxes is based off of a selection to another ocmbo box."  I can't immediately see any problem with the code you do show.  So I wonder if there is something relating to the other, third, combobox; and how that relates to either or both of these two.  So can you please show that code, too?

On "if I save 3 as the vale in the datasource it shows up as 3 but when I load the form it shows 1" it sounds as though, when you load the form, it may be refilling the dataset from the database, and although the datasource had had its value changed to 3, that change was never sent back to the database.

Roger
0
PaulsburbonAuthor Commented:
Sorry got ahead of myself... Never mind the third combo box.. that works and has nothing to do with the problem. So the combo boxes are related by the binding sourcees. I set Bindingsource "1" to the dataset table "A" which has a FK relationship to table "B" and set the bindingsource "2" to the bindingsource "1". I then set the datamember of bindingsource "2" to the relationship.

I set the first combo box  datasource to bindingsource "1" and display and value then databind to the selectedValue. The Second combo box I set the combo box to BindingSource"2" and databind as well. This works. The comboboxes relate one way. If I choose a value in the first combo box then the second box fills in with only the values that it should have. To test I put in four values in table "A" and two values in table "B" for each value in table "A" for a total of eight rows in table "B". So if combo box 1 has a value of "1" then combobox2 should fill in with two choices "A" and "B" if I change combo box 1 to "2" then combobox2  should have "C" and "D" which it does and if I change it to the second choice which inthis case to "D" I check the datasource which is an access database it shows up as "D". However if I reload the form or even go to the next record and go back it shows up as "C".

Last night I was really tired and that's why My questions was not written correctly. Very sorry. All my code fires during the page load. and I even changed it to all be done by the designer instead of code and I get the same problem.

Paul
0
SanclerCommented:
I'm still confused, I'm afraid.

First, I'm no longer sure if this is WinForms or Web.  You originally referred to "form", which made me think the former.  But you now refer to "page load" which makes me wonder if its the latter.  Databinding is (I understand) different in some respects between the two and I've no experience on the Web side.

But I'm still not sure what the overall binding set-up is supposed to be.  Here's a demo of what I originally understood.  One form - two combos (cboColor and cboSize) and one datagridview (dgvGoods) - and the code below.

Can you please demonstrate, basing things on that example with such modifications as are necessary, what's supposed to be related to what and how?

Sorry to be so thick.

Roger
Public Class Form1
 
    Private dtColor As New DataTable("Color")
    Private dtSize As New DataTable("Size")
    Private dtGoods As New DataTable("Goods")
 
    Private Sub maketables()
 
        'color table
        Dim dc0 As New DataColumn("ColorID", GetType(Integer))
        dc0.AutoIncrement = True
        dtColor.Columns.Add(dc0)
        Dim dc1 As New DataColumn("ColorName", GetType(String))
        dtColor.Columns.Add(dc1)
        dtColor.Rows.Add(Nothing, "Red")
        dtColor.Rows.Add(Nothing, "Blue")
        dtColor.Rows.Add(Nothing, "Green")
        dtColor.AcceptChanges()
 
        'size table
        Dim dc2 As New DataColumn("SizeID", GetType(Integer))
        dc2.AutoIncrement = True
        dtSize.Columns.Add(dc2)
        Dim dc3 As New DataColumn("SizeDescription")
        dtSize.Columns.Add(dc3)
        dtSize.Rows.Add(Nothing, "Small")
        dtSize.Rows.Add(Nothing, "Medium")
        dtSize.Rows.Add(Nothing, "Large")
        dtSize.AcceptChanges()
 
        'good table
        Dim dc4 As New DataColumn("ItemID", GetType(Integer))
        dc4.AutoIncrement = True
        dtGoods.Columns.Add(dc4)
        Dim dc5 As New DataColumn("Color", GetType(Integer))
        dtGoods.Columns.Add(dc5)
        Dim dc6 As New DataColumn("Size", GetType(Integer))
        dtGoods.Columns.Add(dc6)
        For c As Integer = 0 To 2
            For s As Integer = 0 To 2
                Dim dr As DataRow = dtGoods.NewRow
                dr("Color") = c
                dr("Size") = s
                dtGoods.Rows.Add(dr)
            Next
        Next
        dtGoods.AcceptChanges()
    End Sub
 
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        maketables()
        cboColor.DisplayMember = "ColorName"
        cboColor.ValueMember = "ColorID"
        cboColor.DataSource = dtColor
        cboColor.DataBindings.Add("SelectedValue", dtGoods, "Color")
        cboSize.DisplayMember = "SizeDescription"
        cboSize.ValueMember = "SizeID"
        cboSize.DataSource = dtSize
        cboSize.DataBindings.Add("SelectedValue", dtGoods, "Size")
        dgvGoods.DataSource = dtGoods
    End Sub
End Class

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

SanclerCommented:
By the way, I'm in the UK, and just off to bed.  So if you do reply and then don't hear for some time, that's why ;-)

Roger
0
PaulsburbonAuthor Commented:
No problem, Thank you for taking time to help me out. It is a windows form. Also I have seven different Bindingsources and a datagridview but they all work. on one section of my form I have two combo boxes. I have a typed dataset that I created with the visual studio 2008. That dataset has seven tables in it. most are used to lookup values that are repeated. one table though has a selection that is based off another lookup table.

I am horrible when writing it all in code I will try but if I miss something I am sorry. I'll explain it just in case here.

Main table has these columns:
ID
Name
ColorID
HueID

Color table has these columns:
ColorID
ColorName

Hue Table has these columns:
HueID
HueName
ColorID

I have a relationship between the main table and color thru colorID and hue table with HueID

then I have a relationship with Colortable with huetable thru ColorID

the form displays the main table info thru one textbox and two combo boxes.

The colorcombobox gets it's collection thru a Colorbindingsource that is bound to the dataset.Color

and the HueComboBox.datasource is bound to the ColorBindingSource and then I set the datamember to the relationship which is "Color_Hue"

When I do this the Hue combobox only displays the correct hues that go with each color but it will constantly change the value in them back to the first available choice. Ok now to try and type that in code.

Ok I think I did the best I could on the code. Thank you again for taking time to help me. If you take days to reply it's ok by me so no worries.

Paul
Public Class Form1
 
    Private dtMain As New DataTable("Main")
    Private dtColor As New DataTable("Color")
    Private dtHue As New DataTable("Hue")
 
    Private Sub maketables()
 
        'Main table
        Dim dc0 As New DataColumn("MainID", GetType(Integer))
        dc0.AutoIncrement = True
        dtMain.Columns.Add(dc0)
        Dim dc1 As New DataColumn("Name", GetType(String))
        dtMain.Columns.Add(dc1)
        Dim dc2 As New DataColumn("ColorID", GetType(Integer))
        dtMain.Columns.Add(dc2)
        Dim dc3 As New DataColumn("HueID", GetType(Integer))
        dtMain.Columns.Add(dc3)
        dtMain.Rows.Add(Nothing, "Bob")  
        dtMain.Rows.Add(Nothing, "John")
        dtMain.Rows.Add(Nothing, "Gary")
        dtMain.AcceptChanges()
 
        'Color table
        Dim dc4 As New DataColumn("ColorID", GetType(Integer))
        dc4.AutoIncrement = True
        dtColor.Columns.Add(dc4)
        Dim dc5 As New DataColumn("ColorName")
        dtColor.Columns.Add(dc5)
        dtColor.Rows.Add(Nothing, "Red")
        dtColor.Rows.Add(Nothing, "Blue")
        dtColor.Rows.Add(Nothing, "Green")
        dtColor.AcceptChanges()
 
        'Hue table
        Dim dc6 As New DataColumn("HueID", GetType(Integer))
        dc6.AutoIncrement = True
        dtHue.Columns.Add(dc6)
        Dim dc7 As New DataColumn("HueName", GetType(Integer))
        dtHue.Columns.Add(dc7)
        Dim dc8 As New DataColumn("ColorID", GetType(Integer))
        dtHue.Columns.Add(dc8)
        For c As Integer = 0 To 2
            For s As Integer = 0 To 2
                Dim dr As DataRow = dtHue.NewRow
                dr("ColorID") = 1
                dr("HueName") = Blue
                dtHue.Rows.Add(dr)
            Next
        Next
        dtGoods.AcceptChanges()
    End Sub
 
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim Dataset as New Typed Dataset
        maketables() 'I would have done this on my dataset designer
        'Here I would have a fill to fill my dataset
        ' I use a WFC project Service
        Dim MainBS as New BindingSource
        Dim ColorBS as New BindingSource
        Dim HueBS as New BindingSource
        MainBS.Datasource = Dataset.dtMain
        ColorBS.Datasource = Dataset.dtColor
        HueBS.Datasource = ColorBS
        HueBS.DataMember = "Color_Hue"
        cboColor.DisplayMember = "ColorName"
        cboColor.ValueMember = "ColorID"
        cboColor.DataSource = ColorBS
        cboColor.DataBindings.Add("SelectedValue", dtMain, "ColorID")
        cboHue.DisplayMember = "HueName"
        cboHue.ValueMember = "HueID"
        cboHue.DataSource = HueBS
        cboHue.DataBindings.Add("SelectedValue", dtMain, "HueID")
            End Sub
End Class

Open in new window

0
PaulsburbonAuthor Commented:
I wish I could edit the above comment

This was wrong:

and the HueComboBox.datasource is bound to the ColorBindingSource and then I set the datamember to the relationship which is "Color_Hue"

It is really I get the HueBindingsource.datasource from the color bindingsource and set it's datamember to the relationship in the typed dataset. then I bind the hue combobox to the huebindingsource

Sorry


0
SanclerCommented:
I'm still not certain I've got this right but my current guess is that you've got conflicting databindings.  I've amended the demo to try to do two things.  First, to reflect what I currently understand to be the crucial elements of your set-up: second to show (if I have got that correct) what I think may be going wrong.  One form - two combos (cboColor and cboHue), one datagridview (dgvMain) and one label (lblHue).  The revised code is below.

The label and the sub cboHue_SelectedValueChanged are just there for purposes of the demo to show what's happening in the hue combobox.  Given that the DataMember of that is the Color_Hue relation, when a different color is selected in the color combo, the values that are available as the new SelectedValue change.  But, and this is what I think may be the crucial point, none of those values is what the SelectedValue is supposed to be coerced to by the binding to the Main table's HueID.  This is because each HueID is unique, with different sub-sets relating to different colors even if the HueName is the same.

I appreciate that the symptoms are not exactly as you described: the demo shows the phenomenon in relation to Hue rather than Color.  But it illustrates the principle of what I think may be wrong with your set-up.  Have a play with it, and see whether you agree.  If you do, then we can consider how to put it right.

Roger
Public Class Form1
 
    Private dtMain As New DataTable("Main")
    Private dtColor As New DataTable("Color")
    Private dtHue As New DataTable("Hue")
    Private ds As New DataSet("Test") 'now included so datarelation can be used
    Private loading As Boolean = True
 
    Private Sub maketables()
 
        'Main table
        Dim dc0 As New DataColumn("MainID", GetType(Integer))
        dc0.AutoIncrement = True
        dtMain.Columns.Add(dc0)
        Dim dc1 As New DataColumn("Name", GetType(String))
        dtMain.Columns.Add(dc1)
        Dim dc2 As New DataColumn("ColorID", GetType(Integer))
        dtMain.Columns.Add(dc2)
        Dim dc3 As New DataColumn("HueID", GetType(Integer))
        dtMain.Columns.Add(dc3)
        dtMain.Rows.Add(Nothing, "Bob", 0, 2)
        dtMain.Rows.Add(Nothing, "John", 1, 2)
        dtMain.Rows.Add(Nothing, "Gary", 2, 2)
 
        'Color table
        Dim dc4 As New DataColumn("ColorID", GetType(Integer))
        dc4.AutoIncrement = True
        dtColor.Columns.Add(dc4)
        Dim dc5 As New DataColumn("ColorName")
        dtColor.Columns.Add(dc5)
        dtColor.Rows.Add(Nothing, "Red")
        dtColor.Rows.Add(Nothing, "Blue")
        dtColor.Rows.Add(Nothing, "Green")
 
        'Hue table
        Dim dc6 As New DataColumn("HueID", GetType(Integer))
        dc6.AutoIncrement = True
        dtHue.Columns.Add(dc6)
        Dim dc7 As New DataColumn("HueName", GetType(String))
        dtHue.Columns.Add(dc7)
        Dim dc8 As New DataColumn("ColorID", GetType(Integer))
        dtHue.Columns.Add(dc8)
        Dim HueName As String
        HueName = "Light"
        For c As Integer = 1 To 2
            Dim dr As DataRow = dtHue.NewRow
            dr("ColorID") = c
            dr("HueName") = HueName
            dtHue.Rows.Add(dr)
        Next
        HueName = "Medium"
        For c As Integer = 0 To 1
            Dim dr As DataRow = dtHue.NewRow
            dr("ColorID") = c
            dr("HueName") = HueName
            dtHue.Rows.Add(dr)
        Next
        HueName = "Dark"
        For c As Integer = 0 To 2
            Dim dr As DataRow = dtHue.NewRow
            dr("ColorID") = c
            dr("HueName") = HueName
            dtHue.Rows.Add(dr)
        Next
 
        ds.Tables.Add(dtMain)
        ds.Tables.Add(dtColor)
        ds.Tables.Add(dtHue)
        Dim rel As New DataRelation("Color_Hue", dtColor.Columns("ColorID"), dtHue.Columns("ColorID"))
        ds.Relations.Add(rel)
        ds.AcceptChanges()
    End Sub
 
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        maketables() 'I would have done this on my dataset designer
        'Here I would have a fill to fill my dataset
        ' I use a WFC project Service
        Dim MainBS As New BindingSource
        Dim ColorBS As New BindingSource
        Dim HueBS As New BindingSource
        MainBS.DataSource = ds.Tables("Main")
        dgvMain.DataSource = MainBS
        ColorBS.DataSource = ds.Tables("Color")
        HueBS.DataSource = ColorBS
        HueBS.DataMember = "Color_Hue"
        cboColor.DisplayMember = "ColorName"
        cboColor.ValueMember = "ColorID"
        cboColor.DataSource = ColorBS
        cboColor.DataBindings.Add("SelectedValue", ds.Tables("Main"), "ColorID")
        cboHue.DisplayMember = "HueName"
        cboHue.ValueMember = "HueID"
        cboHue.DataSource = HueBS
        cboHue.DataBindings.Add("SelectedValue", ds.Tables("Main"), "HueID")
        'lines just for demo display
        loading = False
        cboHue_SelectedValueChanged(Nothing, Nothing)
    End Sub
 
    Private Sub cboHue_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboHue.SelectedValueChanged
        'sub just for demo display
        If loading Then Exit Sub
        Dim msg As String = "dgv selection = " & dgvMain.CurrentRow.Cells("HueID").Value
 
        msg += ": Available in cbo = "
        For Each drv As DataRowView In cboHue.Items
            msg &= drv.Item("HueID") & "|"
        Next
        msg = msg.Trim("|")
        If cboHue.SelectedValue Is Nothing Then
            lblHue.Text = msg & ": No selection"
        Else
            lblHue.Text = msg & ": Selected = " & cboHue.SelectedValue.ToString
        End If
    End Sub
End Class

Open in new window

0
PaulsburbonAuthor Commented:
Hi Roger!

That is exactly want I want to do. However since I cannot change values in my main data grid to see if the change happens and if it gets loaded correctly if I reload the page. Should I attach my project I'm working on aybe that would be better.  Cause everything you wrote I understand yet I still cannot get mine to work properly.

One more thing. Say on mine if I change all the hue values to the second option"Dark". Update it it say 3 rows up dated. If I close the project and run it again the first item in the main table pops up and the combobox for hue has the first item "light" in it. If I change and update it should only update one row the first one I' changing but it updates all three back to the first hue "light" Even if I don't even choose them in bindingsource. That make any sense?

Paul

 
0
SanclerCommented:
Yes.  Upload your project.  Use this link

http://www.ee-stuff.com/

then post the URL back here.

Roger
0
PaulsburbonAuthor Commented:
Ha,

There is a 2MB storage limit and after I zip it I get to 2.256 MB. Figures right. That's after I pullout the app_data database. Maybe something else I can pull off of it?
0
SanclerCommented:
You don't need to include the \bin and \obj folders.  Just select all the files in the Project folder itself - none of the sub folders.

Roger
0
PaulsburbonAuthor Commented:
Here it is:

https://filedb.experts-exchange.com/incoming/ee-stuff/6759-SSEW.zip

Thank you so much for putting up with all this!!!
0
SanclerCommented:
I've got it, but it's going to take me a while to get to grips with it.  I cannot just load it into VB and play with it because the ClaimsWebService bit will not load for me.  That, in turn, means that the datasets won't generate properly so the form designer - I assume we are dealing here with the AdjusterForm - won't work.

I may well be able to modify it to go direct to the Access file you included.  And/or I can work things out from the .Designer files.  But it'll take time.

A cursory glance, however, make me think that a fundamental problem might be with the dataset design.  The Adjusters table has InsuranceCompanyID and LocationID as Foreign Keys to, respectively, the InsuranceCompanies and Location tables.  But the Location table also has a Foreign Key to the InsuranceCompanies table.  Now, let's say that the values in the Adjusters table are

InsuranceCompanyID = 1
LocationID = 1

What happens if - which so far as I can see at the moment may well happen when your user makes selection/s in the comboboxes - the values in the Location table are

LocationID = 1
InsuranceCompanyID = 2

One of the relationships between these three tables - Adjusters_InsuranceCompanies, Adjusters_Locations and InsuranceCompanies_Locations - MUST be violated.  And if any relationships are being violated, then neither the bindings nor the saving is likely to work as expected.

Before I spend a lot of time rooting into the stuff you posted, would you like to think about that point and let me have your comments on it.

Roger
0
PaulsburbonAuthor Commented:
Ok I think you are right. I've never read past building data tables and using relationships other than what I know from Access. So I would I go about setting it up properly? Should I have a relationship only or add in a FK? Or Both? I figured that the AdjusterTable should have a relationship with Insurance Company and a relationship with Locations. Each adjuster has a comapny he works for and a location he is at. Then to get only the specific location that each adjuster works at the table Insurance Company should have a relationship with Locations table right? Do I have to add a constraint? Thank you for all your help again!
0
PaulsburbonAuthor Commented:
I'll add the parent table is insurance co and the child is location on the relationship.
0
SanclerCommented:
>>
Should I have a relationship only or add in a FK? Or Both?
<<

In database terms, one implies the other.  A relationship is implemented by a Foreign Key in one table matching a unique value (usually the Primary Key) in the other table.  Having said that, in programming terms, there are two ways of handling such relationships.  One is to declare  DataRelation/s and use databindings - in which case the "system" is supposed to ensure that everything is consistent.  The other is to self-code to ensure such consistency.

But if the data design itself is inconsistent, neither of those ways can work.

The data design should match, so far as possible, the real world.  I am not sure of all the details of "the real world".  In particular, I am not sure whether it involves one-to-many relationships or many-to-many relationships.

If we look at the sample data in your database we have four companies - Safeco, Travelers, Grange, St.Paul - and eight locations - South, North, East, West, Hill, Mountain, Ocean, River.  But the striking thing about the Location table is that each of those locations applies just to ONE of the companies.  Although one company can relate to more than one location, each location relates to only one company.

Now consider another example.  Assume there are many locations - e.g. London, Paris, New York - and many insurance companies - ABC plc, PQR sg, XYZ inc - with the possibility of any one or more of the insurance companies being located in any one or more of the locations.  For example

ABC plc is in London and Paris
PQR sg is in Paris and New York
XYZ inc is in London and New York

That's different because not only can one company relate to many locations, but also one location can relate to many companies.

The difference has important implications for the design of tables that link to company and/or location - in this case the adjuster table.  For the former - one location relating to just one company - the adjuster table does not need (indeed, to avoid the sort of problems that started this thread, should not have) links to BOTH company and location.  As one location implies one company, the inclusion of just the location reference is enough uniquely to identify the company.  For the latter - as any location can "point to" any company and vice versa - then some more complicated reference will be necessary.  One might be to include both company and location references in the adjuster table, but with some sort of constraint that ensured that the company/location pairing was one that existed in some other table.  Or, if there was some other table containing all the valid company/location pairings, a reference in the adjuster table might be made to a specific record in that.

At the database design level, I think you are falling between two stools.  As I say, I don't know which stool you should be sitting on - that will be dictated by the real-life situation your data is modelling.  But I think you need to sort that out before we can sort the VB.NET coding out.

On that side of things there are, as I say, two approaches.  One is to rely on datarelations and databinding.  The other is to self-code.  I personally tend, for all bar very straightforward stuff, to go down the latter route.  But we can cross that bridge when we reach it ;-)

Roger
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbturboCommented:
Paulsburbon

Roger has given you some very important tips on how make some decisions from the real world scenario that
you want to model into database entityTypes.

i've uploaded a very simple sample on how to establish a one to many relationship.
https://filedb.experts-exchange.com/incoming/ee-stuff/6773-Relation.zip 

Try have a look at it.

Also do your self a big favor and start with the very straightforward  and basic simple stuff , then build on (e.g) multiple tables in the relation , adding constraints...etc (follow the KISS rule)

As mentioned you should use PK on the one side Entity and foreign keys on the many Entity.
Code stuff your self instead of using designers/wizzards that way you have much more control over things and
later on it becomes a lot more easy to maintain and expanding.

Ill try download your upload and look into it tomorrow (and drop a comment on it)

vbturbo

0
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.