Cannot get relationship between two tables to work.

[lithium1976]

Hello I need a bit of advice regarding the design of part of my database.

I am designing a customers-bookings databse in SQL Server Management Studio Express, and designing the front end in VS2008 (vb.net) using bindingsources and datagridviews to display the data.

My database has 4 related tables which are displayed in their own datagridviews -

tblCustomers
customerID (pk)
customerName
customerAddress

tblBookings
bookingsID (pk)
customerID
dateofbooking

tblBookingsItems
bookingsItemsID (pk)
bookingsID
stockID
quantityRequested

tblStock
StockID (pk)
stockTitle
Description
NumberInStock

When I select a customerID (via the Customer DGV) I can view the related BookingsID's in the bookings DGV. Then I can select a BookingsID to view the related BookingsItemsID's in the bookingsItems DGV. This allows for a single booking to have more than one stockID added to it.

Next, I want to select a row in BookingsItems DGV and have Stock DGV show the Stock details for that selected bookingsItem. But I cant get my code to select a row from bookingsItems DGV and view the related Stock details in Stock DGV. I CAN do it the other way around - select a StockID from Stock DVG and view the related StockID's in BookingsItems DGV.

I think it is becuse my DB is designed so one StockID has many BookingsItemsId's.

I have tried the following code in vb.net to create relationships for my tblcustomers-tlbookings, and tblbookings-tblbookingsitems it works fine, but doesnt work for the relationship between tblStock and tblBookingsItems -

[CODE]'Preparing tblBookingsItems

BookingsItemsDataAdapter.Fill(ds, "tblbookingsItems")
datatable = ds.Tables("tblbookingsitems")

BookingsItemsBindingSource.DataSource = ds
BookingsItemsBindingSource.DataMember = "tblBookingsItems"
dgvBookingsItems.DataSource = BookingsItemsBindingSource

'Preparing tblStock
stockDataAdapter.Fill(ds, "tblstock")
datatable = ds.Tables("tblStock")

Dim relationsStockBookingsItems As New DataRelation("StockBookingsItems", _
ds.Tables("tblstock").Columns("stockID"), _
ds.Tables("tblbookingsItems").Columns("stockID"))
ds.Relations.Add(relationsStockBookingsItems)

stockBindingSource.DataSource = BookingsItemsBindingSource
stockBindingSource.DataMember = "StockBookingsItems"
dgvStock.DataSource = stockBindingSource

DataMember property 'StockBookingsItems' cannot be found on the DataSource.

- second to last line of code.

[Hack]

I think it is looking at the bookings items not the stock items, and that is why it can't find that field.