[RESOLVED] Combobox binding problem

[Mark@SF]

I have a form with a bindingsource (bsMain) and a combobox (cboLinkID).

The bsMain properties are as follows.

With bsMain
    .DataMember = "Accounts"   '...this is the Accounts table in the dsMain dataset
    .DataSource = dsMain
End With
cboLinkID.DataBindings.Add(b)

The cboLinkID is bound as follows.

With cboLinkID
    .ValueMember = "RecID"  '...this is the primary key field of the Accounts table
    .DisplayMember = "Account"   '...this is the Account field of the Accounts table (i.e., account names) 
    .DataSource = dsMain.Tables("Accounts")
End With

b = New Binding("SelectedValue", bsMain, "LinkID") 
With b
    .DataSourceUpdateMode = DataSourceUpdateMode.OnPropertyChanged
End With
cboLinkID.DataBindings.Add(b)

The above code is executed with the form's Load method.

When the form opens, the selected item's cboLinkID drop-down list shows all the account names in the Accounts table. The cboLinkID.SelectedValue shows the name of the linked account. This is the expected behavior.

But when I navigate to another record, then the cboLinkID drop-down list now has no items and the cboLinkID.SelectedValue is blank.

Closing/reopening the form repeats the above behavior.

What am I doing wrong?

[wes4dbt]

b = New Binding("SelectedValue", bsMain, "LinkID") 
With b
    .DataSourceUpdateMode = DataSourceUpdateMode.OnPropertyChanged
End With

What is that???

There seems to be some missing relevant code.

[Mark@SF]

wes - Yes, my copy/paste missed the last line. I've added it to the above code snippit. I am having problems with the vbvforums site today -- lots of "Error 503 first byte timeout" errors with my browser (both Edge and Chrome, and also with Safari on my iPad).

Thanks for taking a look at this issue.

[jmcilhinney]

Your bindings don't make sense. You should be binding the ComboBox to two different data sources, one for the DataSource and one for the DataBindings. Consider this. Let's say that you have a User table and a Role table, with the User table having a RoleId column that is a foreign key. You would use a ComboBox to display a list of Role.Name values and then populate the User.RoleId based on the selection. You would assign the list of Role records to eh DataSource and then bind the User.RoleId via the DataBindings, e.g.

With roleBindingSource
    .DataMember = "Role"
    .DataSource = myDataSet
End With

With userBindingSource
    .DataMember = "User"
    .DataSource = myDataSet
End With

With roleComboBox
    .DisplayMember = "Name"
    .ValueMember = "RoleId"
    .DataSource = roleBindingSource

    .DataBindings.Add("SelectedValue", userBindingSource, "RoleId")
End With

givenNameTextBox.DataBindings.Add("Text", userBindingSource, "GivenName")
familyNameTextBox.DataBindings.Add("Text", userBindingSource, "FamilyName")

So, the drop-down list of the ComboBox is bound to the parent table and then the SelectedValue is bound to the child table, just as all the other individual controls are. When you select a User record, the RoleId is assigned to the SelectedValue of the ComboBox and that displays the corresponding Role Name. When the user selects a Role Name, the corresponding RoleId is assigned to the SelectedValue and that gets pushed to the child record.

[Mark@SF]

jmc -

Thank you for taking a look at my question. Your explanation is helpful. In your example, are the Role, User, and RoleId fields in the same datatable of the myDataSet? I'm asking because in my case, and following your guidance, I think I would need to create a relation between 2 fields in the same table.

dsMain.Relations.Add("Accounts_AccountLinks", dsMain.Tables("Accounts").Columns("RecID"), dsMain.Tables("Accounts").Columns("LinkID"))
fkc = dsMain.Relations("Accounts_AccountLinks").ChildKeyConstraint
fkc.DeleteRule = Rule.SetNull
fkc.UpdateRule = Rule.Cascade

Then, I would need to create 2 bindingsource components.

With bsMain
    .DataSource = Nothing
    .DataMember = "Accounts"  '...bind to table (before setting the DataSource property, which fires the PositionChanged() event of the BindingSource)
    .DataSource = dsMain    '...fires the BindingSource's PositionChanged() event
End With

With bsLinks
    .DataSource = Nothing
    .DataMember = "Accounts_AccountLinks"    '...bind to relationship (before setting the DataSource property, which fires the PositionChanged() event of the BindingSource)
    .DataSource = bsMain    '...fires the BindingSource's PositionChanged() event
End With

And finally, bind the cboLinkID combobox.

b = New Binding("SelectedValue", bsLinks, "LinkID")
With b
    .DataSourceUpdateMode = DataSourceUpdateMode.OnValidation
End With
cboLinkID.DataBindings.Add(b)

With cboLinkID
    .ValueMember = "RecID"
    .DisplayMember = "Account"
    .DataSource = bsMain    '...this is the DefaultView (which is sorted)
End With

Now, having done the above, there are no datarows returned when I call the GetChildRows method in following code snippet.

For Each accountRow As DataRow In dsMain.Tables("Accounts").Rows
    Debug.WriteLine("Account: {0}, LinkID: {1}", accountRow("Account").ToString(), accountRow("LinkID").ToString)
    For Each linkRow As DataRow In accountRow.GetChildRows(dsMain.Relations("Accounts_AccountLinks"))
        Debug.Print(linkRow("Account").ToString())
    Next linkRow
Next accountRow

[wes4dbt]

In your example, are the Role, User, and RoleId fields in the same datatable of the myDataSet?

No.

Role - Database Table, contains RoleId, Name
User - Database Table, contains RoleId, GivenName, FamilyName ' also some type of User ID but thats not really relevant.

[jmcilhinney]

OK, you are doing it wrong but not in the way I thought you8 were. The fact that you were trying to bind the SelectedValue threw me. If there's just one table and you want to select an item using the ComboBox and then have the other field values displayed in other controls then you don't need to use the DataBindings of the ComboBox at all. You simply bind the drop-down list and then bind the same data source to your other controls via their DataBindings collections, e.g.

With thingBindingSource
    .DataMember = "Thing"
    .DataSource = myDataSet
End With

With nameComboBox
    .DisplayMember = "Name"
    .ValueMember = "ThingId"
    .DataSource = thingBindingSource
End With

descriptionTextBox.DataBindings.Add("Text", thingBindingSource, "Description")

If you're only using the ComboBox for selection then you only bind the drop-down list. You only bind the SelectedValue via the DataBindings if you're using the control to edit a field in a child record where the parent table is bound to the drop-down list.

[Mark@SF]

Thank you, jmc.