• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 679
  • Last Modified:

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?
0
Ray Turner
Asked:
Ray Turner
1 Solution
 
solublefishCommented:
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now