Update dataset with multiple tables

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?
Ray TurnerSenior ConsultantAsked:
Who is Participating?
 
solublefishConnect With a Mentor Commented:
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");
                  conn.Open();
                  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"
                        );
                  m_da.UpdateCommand.Parameters.Add("@Title",SqlDbType.VarChar,50,"Title");
                  SqlParameter parm = m_da.UpdateCommand.Parameters.Add("@ItemID", SqlDbType.Int,4, "ItemID");
                  parm.SourceVersion = DataRowVersion.Original;
                  
                  m_da.UpdateCommand.Parameters.Add("@Name",SqlDbType.VarChar,50,"Name");
                  parm = m_da.UpdateCommand.Parameters.Add("@NamesID",SqlDbType.VarChar,50,"NamesID");
                  parm.SourceVersion = DataRowVersion.Original;

                  m_da.UpdateCommand.Connection = conn;
                  m_da.Fill(m_ds);
                  conn.Close();

                  dataGrid1.DataSource = m_ds;                  
            }

            private void btnCommit_Click(object sender, System.EventArgs e)
            {
                  SqlConnection conn = new SqlConnection("server=.;database=bsdb;user=sa;password=empirix");
                  conn.Open();
                  m_da.Update(m_ds);
                  conn.Close();
            }
0
 
Ray TurnerSenior ConsultantAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.