automatically synchronized DATA BINDING - Help?

Hello,

I'm working in .NET/C#. I'm trying to have multiple data grids to synchronize information form a database when a selection in a combo box changes.

Here is the code executed at the lunch of my form:

public void Form1_Load(object sender, EventArgs e)
{
// Setup DB-Connection
connectionString = "Data Source=****;Initial Catalog=****;User ID=****;Password=****";
SqlConnection cn = new SqlConnection(connectionString);

//Create DataSet
ds = new DataSet("myDataSet");

// Fill the Dataset with Customers, map Default Customer TableName
// "Table" to "Customer"
SqlDataAdapter daCustomer = new SqlDataAdapter("SELECT * FROM [***].[dbo].[Customer]", cn);
daCustomer.TableMappings.Add("Table", "Customer");
daCustomer.Fill(ds);

// Fill the Dataset with Promotions, map Default Campaign Tablename
// "Table" to "Promotion"
SqlDataAdapter daPromotion = new SqlDataAdapter("SELECT pr. Promotion ID, cp.CustomerID, pr.Promotion Name FROM [***].[dbo].[ Promotion] pr JOIN
[***].[dbo].[PromotionCustomer] cp ON pr.PromotionID = cp.PromotionID", cn);
daPromotion.TableMappings.Add("Table", "Promotion");
daPromotion.Fill(ds);

// Fill the Dataset with Product, map Deufalt Tablename
// "Table" to "Product"
SqlDataAdapter daProduct = new SqlDataAdapter("SELECT p.CustomerID , cp.PromotionID, p.ProductID ,p.ProductName FROM [***].[dbo].[Product] p JOIN [***].[dbo].[PromotionProduct] cp ON p.ProductID = cp.ProductID", cn);
daProduct.TableMappings.Add("Table", "Product");
daProduct.Fill(ds);

// Establish relationship "RelCustPro"
// between Customers ---< Promotions
System.Data.DataRelation relCustPro;
System.Data.DataColumn colMaster1;
System.Data.DataColumn colDetail1;
colMaster1 = ds.Tables["Customer"].Columns["CustomerID"];
colDetail1 = ds.Tables["Promotion"].Columns["CustomerID"];
relCustPro = new System.Data.DataRelation("relCustPro", colMaster1, colDetail1);
ds.Relations.Add(relCustPro);

// Establish relationship "RelProPr"
// between Promotions ---< Products
System.Data.DataRelation relProPr;
System.Data.DataColumn colMaster2;
System.Data.DataColumn colDetail2;
colMaster2 = ds.Tables["Promotion"].Columns["PromotionID"];
colDetail2 = ds.Tables["Product"].Columns["PromotionID"];

relProPr = new System.Data.DataRelation("relProPr", colMaster2, colDetail2);
ds.Relations.Add(relProPr);

// custom settings for each table
dsView = ds.DefaultViewManager;

// combobox binding
cbCustomers.DataSource = dsView;
cbCustomers.DisplayMember = "Customer.CustomerName";
cbCustomers.ValueMember = "Customer.CustomerID";

// PromotionGrid Binding
promotion_dataGridView.DataSource = dsView;
promotion_dataGridView.DataMember = "Customer.relCustPro";

// ProductGrid Binding
product_dataGridView.DataSource = dsView;
product_dataGridView.DataMember = "Customer.relCustPro.ProPr";
}

Relation in red fails to establish as "These columns don't currently have unique values."
I understand why this is so but I don't know how I could go around that.

Here is the outline of what I'm trying to do:
I have the following tables:
1) .Customer
columns: CustomerID, CustomerName
2) Promotion
columns: PromotionID, PromotionName
3) PromotionCustomer
columns: PromotionID, CustomerID
And here is where the problem begins.. PromotionID can be assigned to MULTIPLE CustomerIDs!
So the first relation I created for Customer-Promotion works fine because CustomerID is unique in Customer Table
4) Product
columns: ProductID, ProductName, CustomerID
5) PromotionProduct
columns: PromotionID, ProductID
PromotionID is assigned to multiple products.

So basically a user selects CustomerID from combo box and the first data grid is populated with Promotions that correspond to that CustomerID - that works.
But, when I try to display Products based on PromotionID selected in the first grid I can't because PromotionID is not unique in the PromotionCustomer table!!!!

The join I created would probably work if I could add “WHERE CustomerID= [id selected in the combo box]”;
But I’d not be able to create a relation anyway and I’d have to play with binding sources which defeats the purpose of having a nice automated display based on relations.

I have no idea how to fix this :(

PLEASE HELP!