Update dataset with multiple tables

Posted on 2005-04-19
Last Modified: 2010-08-05
I'm using Visual Studio .NET and an Access database.  I have a dataset which is derived from multiple tables (Join).  How do I update the dataset?  I have a grid object that is unbounded to the dataset and I need a way to update the grid information.

In short, how dow you perform an insert, an update and a delete on a dataset derived from multiple tables?
Question by:rgturner
    LVL 3

    Accepted Solution

    The key here is that you have to specify your own UpdateCommand, DeleteCommand, and InsertCommand to your DataAdapter. In the SQL for those commands, just specify multiple UPDATE ,etc statements.

    Below is some code I threw together that gets a simple join into a DataGrid. The user can edit values in the DataGrid, and then click a button to commit. I only did the UpdateCommand, but the Delete and Insert would be similar. Also, I have Sql Server instead of Access so my code uses the SqlCommand, etc, but yours should be similar.

          private DataSet m_ds;
                private SqlDataAdapter m_da;

                private void Form1_Load(object sender, System.EventArgs e)
                      m_ds = new System.Data.DataSet();                  
                      SqlConnection conn = new SqlConnection("server=.;database=bsdb;user=sa;password=empirix");
                      m_da = new SqlDataAdapter();                  
                      m_da.SelectCommand = new SqlCommand(@"SELECT * FROM Item JOIN Names ON Item.ItemID = Names.ItemID");
                      m_da.SelectCommand.Connection = conn;
                      m_da.UpdateCommand = new SqlCommand(
                            @"UPDATE Item SET
                            Title = @Title
                            WHERE ItemId = @ItemID;
                            UPDATE Names SET
                            Name = @Name
                            WHERE NamesID = @NamesID"
                      SqlParameter parm = m_da.UpdateCommand.Parameters.Add("@ItemID", SqlDbType.Int,4, "ItemID");
                      parm.SourceVersion = DataRowVersion.Original;
                      parm = m_da.UpdateCommand.Parameters.Add("@NamesID",SqlDbType.VarChar,50,"NamesID");
                      parm.SourceVersion = DataRowVersion.Original;

                      m_da.UpdateCommand.Connection = conn;

                      dataGrid1.DataSource = m_ds;                  

                private void btnCommit_Click(object sender, System.EventArgs e)
                      SqlConnection conn = new SqlConnection("server=.;database=bsdb;user=sa;password=empirix");

    Author Comment

    This example is showing the resultset containing information from a single table

    SELECT * FROM Item JOIN Names ON Item.ItemID = Names.ItemID

    How would you dp this if the SQL looked something like this?

    SELECT Item.*, Name.* FROM Item JOIN Names ON Item.ItemID = Names.ItemID

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Suggested Solutions

    Title # Comments Views Activity
    sum28 challenge 31 82
    ImportError: No module named 'pypyodbc' - python 3.5.2 3 241
    maxMirror challenge 10 67
    matchUp  challenge 9 48
    Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now