Do I need a DataRelation?

[hawbsys]

Dear all. I'm wondering why it's so hard to bind a lookup value from
a
related table into a textbox on my form.

Say I've got two tables, product and productcategory. I'm paging
through product information using the bindingnavigator, one product
per page. Everything on the form is bound to fields in the product
table. Description, price, notes productcategoryid. Nice and simple
Ado.net bound form.

But instead of the ID value for product category I'd like to see the
productcategory.description. A lookup value. The relationship between
the two tables is defined in the dataset.

All the samples for this kind of thing are parent-child datagrids and
comboboxes. I just want a simple textbox lookup. I don't want
comboboxes. I don't need two way binding, just a readonly lookup.

I can get what I want by a) using a combo, b) using lots of code and
filters on the bindingsource but there must be an easier way. After
all, if it's easy with a combo box (and it is, just slot in the
selectedvalue, displaymember, valuemember), then it's got to be easy-
peasy using a textbox or a label.

Someone else suggested c) using a join in the original select but that
breaks
breaks a key element of binding. If the description for
productcategory is changed in another form
(sharing the same dataset) the original form knows nothing about the
change and the "old" name is still shown. The beauty of ADO.NET
databinding, is that you can expect your changes to propagate to all
other parts of your application without extra code. Compare with a
combobox (an updated productcategory description shows seamlessly in
the original form if you change productcategory description in
another
form so long as they share the same dataset)

Do I need a DataRelation? I've had a look at them but they don't seem
to solve my problem.

hawbsl

 

[RobinS]

I want to make sure I understand. You have a table called
Product that you are leafing through using a BindingNavigator.

It has a field "ProductCategoryID". You want to display the values for
ProductCategory, with the corresponding ID being saved in your data.

Are you using any BindingSource components, or just binding directly to the
tables?

Either way, you need to do what is called "Double Binding". Bind your
combobox like the following. If you *are* using a BindingSource, you will
need different BindingSource components for your Product and your
ProductCategory table, and use those in place of the Tables in the
following.

Assuming you have a data source for the ProductCategory table.

Do your binding like this:

myComboBox.DataSource = ds.Tables("ProductCategory")
myComboBox.DisplayMember = _
ds.Tables("ProductCategory").Item("ProductCategoryDescription")
myComboBox.ValueMember = _
ds.Tables("ProductCategory").Item("ProductCategoryID")
myComboBox.SelectedValue = _
ds.Tables("Product").Item("ProductCategoryID")

I'm not sure I got the syntax exactly right, but it should get you on your
way.

It will show the dropdown info from ProductCategory, but save the
ValueMember in the SelectedValue if they change it. Note that it will not
show any description if they have a ProductID that is not in the
ProductCategory table.

If you have done your data binding through the Designer, you can create
your data source for the ProductCategory and drag and drop it onto the
combobox, and then drag and drop the ProductID from the Product's data
source onto the *same* combobox, and it will do the data binding for you.
Pretty cool.

Hope this helps.

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------