locked
Updatable DataGridView with columns from multiple tables RRS feed

  • Question

  • I have 3 related tables in a DataSet - Customers, Products and Orders.

    I am trying to create an "editable" DataGridView which, ideally, will contain columns from all the tables.    I have tried creating the joins in the TableAdapter SQL, but this results in a read-only data grid.

    Customers and Orders are joined in a 1:M relationship by CUSTID.
    Products and Orders are joined in a 1:M relationship by PRDID.

    When the user adds a new Order record they will key the ORDERID, CUSTID, PRDID and QTY.  I would like the DataGridView to display the related CUSNAM and PRDDES as soon as the user has keyed the Order record.  The data must be displayed on a single data grid row.  I am not interested in drill down or multiple data grids.

    My data looks like this:

    Orders: ORDERID, CUSTID, PRDID, QTY
    Products: PRDID, PRDDES
    Customers: CUSTID, CUSNAM

    The data grid should look like this:

    ORDERID, CUSTID, CUSDES, PRDID, PRDDES, QTY

    My SQL would look like this:

    SELECT Orders.ORDERID, Orders.CUSTID, Customers.CUSNAME, Orders.PRDID, Products.PRDDES, Orders.QTY
    FROM (ORDERS
    INNER JOIN CUSTOMERS ON Orders.CUSTID = Customers.CUSTID)
    INNER JOIN PRODUCTS ON Orders.PRDID = Products.PRDID;

    Can someone please point me in the right direction?    I am thankful for any advice. 

    Code samples in VB.NET will be appreciated.

    Wednesday, June 10, 2009 2:04 PM

Answers

  • I don't use tableadapters, so I have no sample code to provide you ... plus it seems with table adapters it is mostly setting things through wizards.

    I did spend some time researching, however, and found this link:

    http://www.asp.net/learn/data-access/tutorial-69-vb.aspx

    It seems to discuss this same problem and provide ways (using stored procedures) to get the TableAdapter to handle updates with joined tables.

    The example is a Web application, but you can ignore that part because everything it is doing with regard to the TableAdapters would work with WinForms as well.

    Hope that gets you started...
    www.insteptech.com
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    Wednesday, June 10, 2009 5:01 PM

All replies

  • I don't use tableadapters, so I have no sample code to provide you ... plus it seems with table adapters it is mostly setting things through wizards.

    I did spend some time researching, however, and found this link:

    http://www.asp.net/learn/data-access/tutorial-69-vb.aspx

    It seems to discuss this same problem and provide ways (using stored procedures) to get the TableAdapter to handle updates with joined tables.

    The example is a Web application, but you can ignore that part because everything it is doing with regard to the TableAdapters would work with WinForms as well.

    Hope that gets you started...
    www.insteptech.com
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    Wednesday, June 10, 2009 5:01 PM
  • Thanks DeborahK,  the suggested tutorial covers the functionalty I require in great detail.

    Consequently, I have had to upgrade my data source from Microsoft Access to SQL Server Express, as Access does not support Stored Procedures.

    It would appear I am back on track, so thank you once again.

    Thursday, June 11, 2009 5:15 AM