bind a dropdwn box to 2 tables - one fills it and the other records it.

[Harry]

I have two tables in a database. One fills the drop-down and the other
records the selection. Filling the dropdown with another table seems to be
breaking the binding with the table it records to. VS2005 with SQL2005.
I have a lot of these to do, and don't want to hard code each box. I can
drop and drag the table elements to the form and they work fine, however, as
textboxes. I want to add dropdown boxes populated by another table.
Suggestions would be appreciated.

Thanks,
Harry

 

[RobinS]

Try this; sorry, it's in VB, please forgive me, I'm sure it's not
much different than C#.

TableA = the table that fills the dropdown.
TableB = the table you want the value saved in.
BindingSourceA = the binding source for table A.
BindingSourceB = the binding source for table B.

If you're dragging and dropping from the data sources window:

You need to first drag the table that fills the dropdown onto
the combobox. VS will add a BindingSource to the form; this is
used as the source of the data for the combobox. VS sets the
DisplayMember and ValueMember properties to the *name* of the
field; check and make sure you change the ValueMember (assuming
it's different).

Next, drag the field that you want stored in the database from the
DataSources window and drop it on the combobox. VS automatically
sets the SelectedValue property of the combobox to that field.
It does this because it knows you're doublebinding because
you've already bound the combobox once.

In your form_load event, set the data source of the
binding source containing the displayed data accordingly,
so it will load when the form loads. This is the only line
of code you should have to write:

me.BindingSourceA.DataSource = TableA

In my case, I had already dragged fields onto the form
from TableB, so I already have a BindingSourceB, and it's
assigned to the appropriate data source in the VS-generated
code.

So you should end up with these settings when you look at the
properties of the combobox.

DataSource = BindingSourceA
DisplayMember = "TableA-DisplayField"
ValueMember = "TableA-value-field to store in TableB"
SelectedValue = "BindingSourceB - fieldname"

//Just FYI, this is in my VS-generated code.
BindingSourceB.DataSource = tableB
myComboBox.DataBindings.Add(New
System.Windows.Forms.Binding("SelectedValue", _
Me.BindingSourceB, "fieldnametobindto", True)

Hope that helps.
Robin S.