Creating a Master - Detail / Parent - Child Form with C# / SSE 2005

Found this snippet of code at (http://www.gamedev.net/community/forums/topic.asp?topic_id=348031) that may be just what you need:

DataSet ds = MyDatabase.GetCategoriesAndProdukts();

//Bind the grid
dataGridView1.DataSource = ds.Tables["Products"];

//Remove the auto-generated CategoryID column
dataGridView1.Columns.Remove("CategorieID");

//Create the detail Column for CategorieID
DataGridViewComboBoxColumn detailCol = new DataGridViewComboBoxColumn();
detailCol.HeaderText = "Category";

//set the DisplayStyle to None, if you only want to have a lookup field
dgcb.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
dgcb.ReadOnly = true;

//This column is bound to the Products.CategoryID field
detailCol.DataPropertyName = "CategorieID";

//The detail column is filled from the Categories table
detailCol.DefaultCellStyle = ds.Tables["Categories"];
detailCol.DisplayMember = "CategoryName";
detailCol.ValueMember = "CategorieID";

//Finally add the column to the grid
dataGridView1.Columns.Add(detailCol);




HTH
Tommie

Let me try and decipher that and see if I can get it to work with my app.

I got the master-detail form to work, however, I am unable to update an existing row in my child table. I used the designer to add 3 textbox's from tblPersonnel and 1 datagridview from tblCertRecords.  I do not know how to do this all manually, so i've been using the designer for most of it. The error message I get is this:

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.


Here's my existing code:

private void Form1_Load(object sender, EventArgs e)
        {
            this.tblPersonnelTableAdapter.Fill(this.cachePROPEDDataSet.tblPersonnel);
            this.tblCertRecordsTableAdapter.Fill(this.cachePROPEDDataSet.tblCertRecords);


        }

        private void tblPersonnelBindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
            this.Validate();
            this.tblPersonnelBindingSource.EndEdit();
            this.tblPersonnelTableAdapter.Update(this.cachePROPEDDataSet.tblPersonnel);

            try
            {
                // Delete records in the child table
                this.tblCertRecordsTableAdapter.Update(this.cachePROPEDDataSet.tblCertRecords.Select("", "", DataViewRowState.Deleted));

                //Handle ADD, UPDATE, and DELETE in parent table
                this.tblPersonnelTableAdapter.Update(this.cachePROPEDDataSet.tblPersonnel.Select("", "",
                    DataViewRowState.Added |
                    DataViewRowState.ModifiedCurrent |
                    DataViewRowState.Deleted));

                //Handle ADD & UPDATE in child table
                this.tblCertRecordsTableAdapter.Update(this.cachePROPEDDataSet.tblCertRecords.Select("","",
                    DataViewRowState.Added |
                    DataViewRowState.ModifiedCurrent));
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.Message);
                throw;
            }

        }

Can someone please help me update my records?

Tackling the errors one by one usually result in a good outcome.

In the designer can you create an UpdateCommand method using the properties of the datagridview?

If so move the process handling into that area.

I'm not sure what you mean.  I've pretty much isolated the error to the last section //Handle ADD & UPDATE in the child table.  I am able to add / delete any item in the child or parent table.  I just cant update and existing item.  I've stepped through the process and basically it catches at the last line.

Here is my progress so far.  I am finally able to update my detail table.  Yay.  Although this part was successful, it opened up another can of worms.  The good thing is, I'm starting to see how this whole process works.  Here's what I did so far.

My original error message I received said:

Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

I did a google search and came upon this post in the MSDN forums.

--------------------------------------------------------------------------------------------------------------

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=261817&SiteID=1

create a method for update.

Goto Data->show data sources, Right click on the table you are having probs with and edit dataset with designer.  Right click on the table adapter and choose properties, expand Data->updateCommand.  Choose new, expand updatecommand '+'.  Go to command text and add table.  Select the fields you reuire you will end up wilth a query similar:

UPDATE    TBLCustomer
  SET              name =, address =, phone =

 

change to

UPDATE    TBLCustomer
  SET              name =?, address =?, phone =?


----------------------------------------------------------------------------------------
 
So it saved!  But here's the problem.  Now, when I update a record, when I close the application and restart there are 5 additional records + the 1 existing record of exactly the same data .  My immediate guess is that it corresponds to 1 record for each of the existing fields or columns that are in that table.  I had tried adding the '?' for the SET locations.  I even tried adding @fieldname to them which gave me the same result.  

When I looked at the UpdateCommand properties of the original TableAdapter, there was a lot more code in there which, although I'm not sure what it means, it appears that it  took into consideration, nulls and other parameters.  I'm going to attempt to copy much of the format from the original tableadapter properties and put them into this new tableadapter.

Just to follow:  Here's my code so far:

       private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'cachePROPEDDataSet.tblCertifications' table. You can move, or remove it, as needed.
            this.tblCertificationsTableAdapter.Fill(this.cachePROPEDDataSet.tblCertifications);
            //Reverse the order.  Make sure tblPersonnel comes before tblCert
            // TODO: This line of code loads data into the 'cachePROPEDDataSet.tblPersonnel' table. You can move, or remove it, as needed.
            this.tblPersonnelTableAdapter.Fill(this.cachePROPEDDataSet.tblPersonnel);

            // TODO: This line of code loads data into the 'cachePROPEDDataSet.tblCertRecords' table. You can move, or remove it, as needed.
            this.tblCertRecordsTableAdapter.Fill(this.cachePROPEDDataSet.tblCertRecords);


        }

        private void tblPersonnelBindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
            if (this.Validate())
            {
                this.tblPersonnelBindingSource.EndEdit();
                //this.tblPersonnelTableAdapter.Update(this.cachePROPEDDataSet.tblPersonnel);
                this.tblCertRecordsBindingSource.EndEdit();
                //this.tblCertRecordsTableAdapter.Update(this.cachePROPEDDataSet.tblCertRecords);

                try
                {
                    // DELETE records in the child table
                    this.tblCertRecordsTableAdapter.Update(this.cachePROPEDDataSet.tblCertRecords.Select("", "",
                        DataViewRowState.Deleted));

                    //Handle ADD, UPDATE, and DELETE in parent table
                    this.tblPersonnelTableAdapter.Update(this.cachePROPEDDataSet.tblPersonnel.Select("", "",
                        DataViewRowState.Added |
                        DataViewRowState.ModifiedCurrent |
                        DataViewRowState.Deleted));

                    //Handle ADD & UPDATE in child table
                    this.tblCertRecordsTableAdapter.Update(this.cachePROPEDDataSet.tblCertRecords.Select("", "",
                        DataViewRowState.Added |
                        DataViewRowState.ModifiedCurrent));

                    //this.Validate();
                    //this.tblCertificationsBindingSource.EndEdit();
                    //this.tblCertificationsTableAdapter.Update(this.cachePROPEDDataSet.tblCertifications);
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(Ex.Message);
                    //throw;
                }
            }
        }

Joe

Ok.  I figured it out... Except I dont know exactly what went wrong. I can only speculate at this time that it was the data itself.  The table I had attempted to update had many null fields.  I still need to figure out why that triggers errors.