How to assign columns of two diferent tables to "valuemember" and "displaymember" pro

[gd2q]

Hello friends.

I have a quite peculiar problem.

I would like to have a combobox that showed values of a field (key primary of a table) and besides I would like to record this value in another different field of a diferent table (that it would be foreign key). I know that with ADO this fact was possible if you used the properties BoundColumn and BoundText of the DataCombo control, but with ComboBox control of VisualBasic.NET I can´t.

I have filled up the DataSet with the two tables, I´ve even established its relation, and I´ve assigned the DataSet to the DataSource property of ComboBox control. I´ve also assigned the name of the column that I want to the ValueMember property (like this: mydataset.Tables(0).Columns("mycolumn").tostring) and i´ve assigned the name of the other column of the other table to the DisplayMember property, in the same way. But the program sends a generic exception to me that does not help me much:"Cannot bind to the new display member.
Parameter name: newDisplayMember".

If you can help me I would be tremendously thankful .

Thank you very much!

[jhammer]

You can't do that. This is one of the biggest pains in ADO.NET - you can't have a "Joined View" of two tables with a relation between them.

I can offer two solutions:
1. If you are connected to a database, use a Command object to retrieve a table with a JOIN SQL statement. This will solve most of your problems - you can het all the columns you need from both tables.

2. You can use JoinView, and use it as a datasource. The code and examples is here:
http://support.microsoft.com/kb/325682/EN-US/

And an article about it is here: Using JoinView to Show Queries From Multiple Tables

One other advice I can give if none of these suit your needs: Refactor your design. Simply put: Think of another way to do it.