Combobox lookup binding with bound controls - Please help!

[jon f kaminsky]

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?

--
thx,
jf kaminsky

[jon f kaminsky]

Okay so I think I answered my own question partially anyway. I did the
following:

Bound the Combobox to the Orders table with dataadapter1 with the selected
value set to CustomerID.
Filled the lookup data from Customers table with dataadapter2 and set both
the displaymember and value member to "CustomerID"

right before adding a new row, I set the combobox index to -1 to blank it
out, then the new row is added. I then change to the desired value of
customerID and then call update and acceptchanges.

This seems to work, except that if you do not move off of the combo box and
enter a value in one of the other form controls before calling update, the
new row will NOT be added. It looks like it is, but when you reload the
dataset, there is no new row.

I also added the following to Combobox1_SelectedIndexChanged
ComboBox1.DataBindings("SelectedValue").BindingMan agerBase.EndCurrentEdit()

Just to see what happens... the idea was trying to get the value set when I
selected the new CustomerID. No change. Help!!!
--
thx,
jf kaminsky
"jon f kaminsky" wrote:

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?

--
thx,
jf kaminsky

[Ken Tucker [MVP]]

Hi,

Dim dv As DataView

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim strConn As String
Dim da, daOrders As SqlDataAdapter
Dim conn As SqlConnection
Dim ds As New DataSet

strConn = "Server = (local);"
strConn &= "Database = NorthWind; Integrated Security = SSPI;"
conn = New SqlConnection(strConn)

da = New SqlDataAdapter("Select * From Customers", conn)
daOrders = New SqlDataAdapter("Select * From Orders", conn)

da.Fill(ds, "Customers")
daOrders.Fill(ds, "Orders")

dv = New DataView(ds.Tables("Orders"))

cboCustomers.DataSource = ds.Tables("Customers")
cboCustomers.DisplayMember = "CompanyName"
cboCustomers.ValueMember = "CustomerID"

dgOrders.DataSource = dv
End Sub

Private Sub cboCustomers_SelectedValueChanged(ByVal sender As Object,
ByVal e As System.EventArgs) Handles cboCustomers.SelectedValueChanged
Try
Dim drv As DataRowView = DirectCast(cboCustomers.SelectedItem,
DataRowView)
dv.RowFilter = String.Format("CustomerID = '{0}'",
drv.Item("CustomerID"))
Catch ex As Exception

End Try
End Sub
Ken
----------------------
"jon f kaminsky" <jo**********@discussions.microsoft.com> wrote in message
news:37**********************************@microsof t.com...

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?

--
thx,
jf kaminsky

[jon f kaminsky]

Thanks Ken for your quick response. I have also got things to work better
with dataviews and grids. Couple of things though. I am not using a datagrid-
all the fields in the Orders table are bound individually to textboxes on the
form, except for Orders.CustomerID, it is displayed in a combobox.
Unfortunately, my real project, for which I am using the Northwind database
to model the interaction, is not using views and datagrids. My real project
is also different on the field that is modeled with CustomerID. The
displaymember and value member are one and the same. Sort of like having zip
codes stored in a combo - the displayed value IS the data. These differences
somehow contribute to the problems I am experiencing.

if you have any more input, I would sure like to read it.

Thanks
Jon
--
thx,
jf kaminsky
"Ken Tucker [MVP]" wrote:

Hi,

Dim dv As DataView

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim strConn As String
Dim da, daOrders As SqlDataAdapter
Dim conn As SqlConnection
Dim ds As New DataSet

strConn = "Server = (local);"
strConn &= "Database = NorthWind; Integrated Security = SSPI;"
conn = New SqlConnection(strConn)

da = New SqlDataAdapter("Select * From Customers", conn)
daOrders = New SqlDataAdapter("Select * From Orders", conn)

da.Fill(ds, "Customers")
daOrders.Fill(ds, "Orders")

dv = New DataView(ds.Tables("Orders"))

cboCustomers.DataSource = ds.Tables("Customers")
cboCustomers.DisplayMember = "CompanyName"
cboCustomers.ValueMember = "CustomerID"

dgOrders.DataSource = dv
End Sub

Private Sub cboCustomers_SelectedValueChanged(ByVal sender As Object,
ByVal e As System.EventArgs) Handles cboCustomers.SelectedValueChanged
Try
Dim drv As DataRowView = DirectCast(cboCustomers.SelectedItem,
DataRowView)
dv.RowFilter = String.Format("CustomerID = '{0}'",
drv.Item("CustomerID"))
Catch ex As Exception

End Try
End Sub
Ken
----------------------
"jon f kaminsky" <jo**********@discussions.microsoft.com> wrote in message
news:37**********************************@microsof t.com...

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?

--
thx,
jf kaminsky

[Ken Tucker [MVP]]

Hi,

You could still bind the combobox with the orders to a dataview
and filter the records for that.

Ken
---------------------
"jon f kaminsky" <jo**********@discussions.microsoft.com> wrote in message
news:5A**********************************@microsof t.com...

Thanks Ken for your quick response. I have also got things to work better
with dataviews and grids. Couple of things though. I am not using a
datagrid-
all the fields in the Orders table are bound individually to textboxes on
the
form, except for Orders.CustomerID, it is displayed in a combobox.
Unfortunately, my real project, for which I am using the Northwind
database
to model the interaction, is not using views and datagrids. My real
project
is also different on the field that is modeled with CustomerID. The
displaymember and value member are one and the same. Sort of like having
zip
codes stored in a combo - the displayed value IS the data. These
differences
somehow contribute to the problems I am experiencing.

if you have any more input, I would sure like to read it.

Thanks
Jon
--
thx,
jf kaminsky
"Ken Tucker [MVP]" wrote:

Hi,

Dim dv As DataView

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim strConn As String
Dim da, daOrders As SqlDataAdapter
Dim conn As SqlConnection
Dim ds As New DataSet

strConn = "Server = (local);"
strConn &= "Database = NorthWind; Integrated Security = SSPI;"
conn = New SqlConnection(strConn)

da = New SqlDataAdapter("Select * From Customers", conn)
daOrders = New SqlDataAdapter("Select * From Orders", conn)

da.Fill(ds, "Customers")
daOrders.Fill(ds, "Orders")

dv = New DataView(ds.Tables("Orders"))

cboCustomers.DataSource = ds.Tables("Customers")
cboCustomers.DisplayMember = "CompanyName"
cboCustomers.ValueMember = "CustomerID"

dgOrders.DataSource = dv
End Sub

Private Sub cboCustomers_SelectedValueChanged(ByVal sender As Object,
ByVal e As System.EventArgs) Handles cboCustomers.SelectedValueChanged
Try
Dim drv As DataRowView =
DirectCast(cboCustomers.SelectedItem,
DataRowView)
dv.RowFilter = String.Format("CustomerID = '{0}'",
drv.Item("CustomerID"))
Catch ex As Exception

End Try
End Sub
Ken
----------------------
"jon f kaminsky" <jo**********@discussions.microsoft.com> wrote in
message
news:37**********************************@microsof t.com...

> 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?
>
> --
> thx,
> jf kaminsky