When I select a customerID (via the Customer datagridview) 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.
What I cannot do is select a BookingsItemsID from bookingsItems DGV and view the particular StockID they have booked in the Stock DGV. I CAN do it the other way around - select a StockID to view all the BookingsItemsID for that stockID.
I think it is becuse my DB is designed so one StockID has many BookingsItemsId's.
What do I need to alter? Do I need to add another table called say "tblStockItems" and add the stockID and create a new field called say "stockItemsID" linked to tblBookingsItems? Or should I use a Query to show tblbookingsitems and related items on tblstock?
inner join tblBookingsItems on tblStock.StockID = tblBookingsItems.StockID
where tblBookingsItems.BookingsItemsID = [YOURID]
As a matter of fact, if all you want is the StockIDs for a given BookingsItemsID, you can get that just from the BookingItems table alone.
And do yourself a favor and drop the ridiculous "tbl" prefixes before you tie too much code to this naming convention.
Drop the ridiculous "tbl"prefixes?? I have always been taught the opposite - if its a table it helps to use tbl, if its a form use frm etc etc, it really does help me to do it that way, anyway its personal choice so if you dont like it thats fair enough Im going to keep it like that..
I dont just want the stockId's from tblBookingsItems, I know I can have that. I want to select a stockID in tblBookingsItems via the BookingsItems DGV and have tblStock's DGV display the related stock details for that selected stockID. But the way the db is at the moment is - one stockID (tblStock) to many stockID (tblBookingsItems) - hence I can only select a stockID and its view related bookingsItems. I want it the other way round - select a BookingsItemsId and view its related Stock Details.
Experienced DBAs scoff at such naming conventions. But, if you don't mind a little scorn and snickering, forge ahead.
Regarding your issue, my point is that there is nothing in the database schema that prevents you from doing what you want to do. If for some reason the Data Grid Views do not allow this then that is a short-coming of theirs that you will have to work around.
Do NOT change your schema to adapt to limitations of the tools that you choose to use.
If it's not practically useful, then it's practically useless.
Ok as long as I dont have to add another table between tblStock and tblBookings then I must be heading in the right direction.
I have tried the following code in vb.net to create relationships and for my tblcustomers-tlbookings, and tblbookings-tblbookingsitems the code works great, but for the following tblbookingsitems-tblstock vb.net doesnt want to know -
DataMember property 'StockBookingsItems' cannot be found on the DataSource.
- second to last line of code.
I can get that code to work the other way round so I can select a row in tblStock and display its related bookingsitems details. But I need to select a row in tblBookingsItems and display its related Stock details.