G
Guest
Hi-
I've seen this problem discussed a jillion times but I cannot seem to
implement any advice that makes it work. I am porting a large project from
VB6 to .NET. The issue is using the combo box bound to a table as a lookup,
drawing values from another table to populate the available selections. This
all worked fine in VB6.
I have distilled the problem down to a simple form drawing data from the
Northwind database for a representative model so that it can easily be
duplicated by anyone.
1. I use two tables, Orders and Customers. Orders contains a FK from
Customers called CustomerID.
2. Create one form, with text fields bound to columns from the Orders table,
except for Orders.CustomerID. Orders.CustomerID is bound and displayed in the
Combobox. Navigation buttons are used to move around in the orders table,
with buttons to add rows, etc. You can use a wizard to generate this very
easily.
3. The idea is one can add a new order to the Orders table, and make a
selection for the customerID by using a value from the combobox, or change an
existing CustomerID to a different one. The CustomerID values in the
combobox are all populated from the Customers table. Of course, as one
scrolls through orders using the navigation buttons the value displayed in
the combobox changes accordingly, as do the rest of the columns displayed in
the text fields.
4. I have used various means of doing this with various results, all bad.
a. Using one dataadapter and one dataset. Orders table and Customers table
are added to the dataset. All form datafields, including the combobox are
bound to the Orders table with statements similar to the following:
Me.editShipName.DataBindings.Add(New System.Windows.Forms.Binding("Text",
Me.dsOrders, "Orders.ShipName"))
The Orders table is loaded into the form controls with
SqldataAdapter1.fill(ds, "Orders"). The Combobox lookup values are the
loaded by Sqldataadapter1.Fill(ds,"Customers") with displaymember set to
CustomerID. This throws an exception regarding nonnull, key restraints
violated. etc. I have tried adding a relation between the tables on
customerID to no avail.
b. I created another dataadapter for just the customer table in order to
load the lookup values. Orders is bound to the controls as before with the
Combobox being loaded by another dataset from datadapter2.fill:
SqlDataAdapter2.Fill(custTemp, "Customers")
ComboBox1.DataSource = custTemp.Tables(0)
ComboBox1.DisplayMember = "CustomerID"
This second method works fine- the combo is populated and changes values as
one scrolls through the orders table with with navigation buttons. However,
you cannot change the value in the combobox or add a new row because the
value selected in the combobox just changes back to the orginal value if one
moves to the next record or reloads the dataset. Obviously the change is not
being saved.
c. Same as b, but after the combobox is filled with the CustomerID lookup
values from the Customers table, I rebind the combo to the orginal Order
table with:
ComboBox1.DataBindings.Add("text", dsOrders.Tables(0), "CustomerID") or
ComboBox1.DataBindings.Add("text", jdsOrders, "Orders.CustomerID")
Neither have any effect.
I believe I have a currency manager issue. I am doing everything right
except for the combobox. I can even add rows if the combobox is not populated
with the lookup values. I even tried adding:
ComboBox1.BindingContext = New BindingContext
before populating the combobox with the lookup values.. again with no
improvement.
What am I doing wrong?
I've seen this problem discussed a jillion times but I cannot seem to
implement any advice that makes it work. I am porting a large project from
VB6 to .NET. The issue is using the combo box bound to a table as a lookup,
drawing values from another table to populate the available selections. This
all worked fine in VB6.
I have distilled the problem down to a simple form drawing data from the
Northwind database for a representative model so that it can easily be
duplicated by anyone.
1. I use two tables, Orders and Customers. Orders contains a FK from
Customers called CustomerID.
2. Create one form, with text fields bound to columns from the Orders table,
except for Orders.CustomerID. Orders.CustomerID is bound and displayed in the
Combobox. Navigation buttons are used to move around in the orders table,
with buttons to add rows, etc. You can use a wizard to generate this very
easily.
3. The idea is one can add a new order to the Orders table, and make a
selection for the customerID by using a value from the combobox, or change an
existing CustomerID to a different one. The CustomerID values in the
combobox are all populated from the Customers table. Of course, as one
scrolls through orders using the navigation buttons the value displayed in
the combobox changes accordingly, as do the rest of the columns displayed in
the text fields.
4. I have used various means of doing this with various results, all bad.
a. Using one dataadapter and one dataset. Orders table and Customers table
are added to the dataset. All form datafields, including the combobox are
bound to the Orders table with statements similar to the following:
Me.editShipName.DataBindings.Add(New System.Windows.Forms.Binding("Text",
Me.dsOrders, "Orders.ShipName"))
The Orders table is loaded into the form controls with
SqldataAdapter1.fill(ds, "Orders"). The Combobox lookup values are the
loaded by Sqldataadapter1.Fill(ds,"Customers") with displaymember set to
CustomerID. This throws an exception regarding nonnull, key restraints
violated. etc. I have tried adding a relation between the tables on
customerID to no avail.
b. I created another dataadapter for just the customer table in order to
load the lookup values. Orders is bound to the controls as before with the
Combobox being loaded by another dataset from datadapter2.fill:
SqlDataAdapter2.Fill(custTemp, "Customers")
ComboBox1.DataSource = custTemp.Tables(0)
ComboBox1.DisplayMember = "CustomerID"
This second method works fine- the combo is populated and changes values as
one scrolls through the orders table with with navigation buttons. However,
you cannot change the value in the combobox or add a new row because the
value selected in the combobox just changes back to the orginal value if one
moves to the next record or reloads the dataset. Obviously the change is not
being saved.
c. Same as b, but after the combobox is filled with the CustomerID lookup
values from the Customers table, I rebind the combo to the orginal Order
table with:
ComboBox1.DataBindings.Add("text", dsOrders.Tables(0), "CustomerID") or
ComboBox1.DataBindings.Add("text", jdsOrders, "Orders.CustomerID")
Neither have any effect.
I believe I have a currency manager issue. I am doing everything right
except for the combobox. I can even add rows if the combobox is not populated
with the lookup values. I even tried adding:
ComboBox1.BindingContext = New BindingContext
before populating the combobox with the lookup values.. again with no
improvement.
What am I doing wrong?