none
Add and edit child table rows RRS feed

  • Question

  • My tables are related only in the dataset (VS 2005).  They are not related in the SQL database.  I've tried a few ways of adding and editing child table rows, but none of them work.  How is this done?  I have no problems creating and saving the parent table record.


    I'm using BindingSource.AddNew() on the BindingSource of the relation, not the BindingSource of the child table.  That's because my form has controls that are bound to the relation's BindingSource, and so that's where the user's edits are seen.  AddRow() does create a row within the dataset, and it's accessible via BindingSource.Current.  But it's not accessible via the parent row's GetChildRows().  Saving the record (with BindingSource.EndEdit() and TableAdapter.Update()) doesn't save anything to the database.

     

    If I use the child table's BindingSource there are other problems, which I could be specific about if it's relevant.


    Thanks for the help on this beginner's question.  I could only find documentation on how to access existing child table rows.

    Friday, November 30, 2007 11:30 PM

Answers

  • Hi DM & Dillon.

    What you are both doing is generating an id in the database. That id is automatically updated in the datarow just after the insert because the insert command includes a query 'insert bla blah;select x,y,z where id = scope_identity()' that retreives the new id and stores it in the dataset. (you can double check that in the dataset designer).

     

    I just created a sample project to demonstrate how to do this.  This time i wrote the code in c#. Dillon to get you started on a vb version, note that in vb overriding EndInit is not required with the Handles systax.

     

    I changed a little the code compared to what we were discussing. It's a little simpler but not entirely clean. If you have any questions don't hesitate.

     

    Regards,

    Charles

    Wednesday, December 5, 2007 3:34 AM
  • Charles,

     

    Thanks, that's very helpful.  I see what you're saying, that the new ID generated by the database is fed back into the dataset, and that's what makes it possible to wire up the events that update the child rows with the new ID.


    Your specific example involves using a DataGridView event.  In my situation, some of my child tables don't appear in any user controls.  Also, since we know when the master row is changing, it seems like it's possible to do this without any events, as in this code, which is saving 1 master row and 1 child row that were created at the same time:


    Code Block
    private void Save()
    {
      Validate();
      // Disable constraints for now
      m_dataSet.EnforceConstraints = false;
      // Get the master row
      DataSet.MasterRow master = (DataSet.MasterRow)(((DataRowView)masterBindingSource.Current).Row);
      // Get the child row
      DataSet.DetailRow detail = (DataSet.DetailRow)(((DataRowView)detailBindingSource.Current).Row);
      // Save the master row
      masterBindingSource.EndEdit();
      masterTableAdapter.Update(master);
      // Store the new database-generated ID to the child row
      detail.DetailID = master.MasterID;
      // Save the child row
      detailBindingSource.EndEdit();
      detailTableAdapter.Update(detail);
      // Re-enable constraints
      m_dataSet.EnforceConstraints = true;
    }

     

    Is there anything short-sighted about this, or is this an acceptable way of doing this?
     

    Thanks a lot.

     

    JM

    Wednesday, December 5, 2007 9:44 AM
  • It seams ok, the only thing is this line:

     

    detail.DetailID = master.MasterID;

     

    Shouldn't it be

     

    detail.MasterID = master.MasterID;

     

    Charles

    Wednesday, December 5, 2007 11:53 PM
  • Yes, I think you're right;  I misread your intention in the database, and didn't notice that args.Row.Cells[1] refers to Detail.MasterID.


    One more point in case it pertains to any of the exceptions that Dillon was seeing -- According to the documentation I've seen, the typical way to relate two tables is:  Set the parent table's BindingSource.DataSource to the dataset and its BindingSource.DataMember to the parent table.  Create the relation (which belongs to the parent table).  Set the child table's BindingSource.DataSource to the parent table's BindingSource, and its BindingSource.DataMember to the relation.  The result is that when you move the row pointer in the parent table, the child table is automatically filtered to the related records.


    This is somewhat dysfunctional however because if you try to create and save child rows, it produces exceptions -- at least, I haven't yet figured out how to make it work.


    I assume this is why in your sample project you instead set the child table BindingSource.DataSource and BindingSource.DataMember to the dataset and the child table.  Now there's no problem saving child rows.  However the child table is no longer filtered to only the related records.  So you handled the parent table's PositionChanged event, and every time that fires, you set the child table's BindingSource.Filter.  Now, the child table is always correctly filtered.  In this setup, I don't know if the relation is even accomplishing anything.  Maybe it creates a behind-the-scenes index for faster filtering.


    I'd be happy to learn of any better way to do this.  Maybe LINQ to SQL is the answer.

     

     

    Thursday, December 6, 2007 1:35 AM

All replies

  • Hi,

    Don't forget to call the Update method of the child's table adapter if you want the DB to be updated! EndEdit just updates the values in the datasource - ie the dataset.  The table adapter must be used to send the change to the DB.

     

    Regards,

    Charles

    Saturday, December 1, 2007 12:25 AM
  • I appreciate the answer, and yet I have further questions.  I know this is remedial, but let's back up a step. 

     

    Suppose our database has Company and Employee tables that are not related in the SQL database.  They are however related in the typed dataset.  Company is the parent.  Company has a UID (an int Identity column), and that's what the two tables are related on.  Each table has a TableAdapter.  The two TableAdapters and the Relation each have a BindingSource, so there are 3 BindingSources.

     

    Our form has a textbox for displaying child Employee table data.  The textbox is bound to the relation's BindingSource.

     

    The user clicks a 'New' button that should create one new Company record and one new child Employee record.

     

    Our code adds the parent record with this.companyBindingSource.AddNew(), and it adds the child record with this.relation1BindingSource.AddNew().  The code runs, but where's the child record?  Property this.relation1BindingSource.Current is null.  The parent row's GetChildRows("relation1") returns an empty array.

     

    To create the child record, we might instead run this.employeeBindingSource.AddNew().  This is better -- this.employeeBindingSource.Current has a new row.  But the parent row's GetChildRows("employee") returns an empty array.  But we proceed anyway and let the user change the child value via the textbox.  He clicks the 'Save' button, and our code gets the child row using employeeBindingSource.Current, but the user's data changes aren't there, presumably because the textbox is bound to the relation's BindingSource, not the TableAdapter's BindingSource.

     

    Until we see the data, running TableAdapter.Update() won't accomplish anything.

     

    How do we do this?  Thanks.

    Saturday, December 1, 2007 10:11 PM
  • If I understand it right, this is a standard master-detail view only the database do not have a foreign key? In my code I never used the relation's binding source, only the master's and the detail's binding source.

     

    Try using both of these and register to the AddingNew event of the employee bindingsource.  In that event set the CompanyID column to the value based on the current row of the company bindingsource.

     

    After EndEdit is called on the employee, you should be able to get it though the relation binding source and the company datarow's Employees collection.

     

    Hope this helps,

    Charles

    Saturday, December 1, 2007 10:47 PM
  • Hi,

    I have a related question to the previous post. What if the master record hasn't been added to the database yet, so it doesn't have a CompanyID value, in this case, until it is added? How do I bind the foreign key column of the child rows to the parent so they get updated when the records are added?
    Sunday, December 2, 2007 8:25 PM
  •  

    Hi Dillon,

     

    So your using an identity column in the master table to assign the primary key?  Datasets have no support for automatically updating the foreign key when the related key changes. Here's the strategy that I think is the best way to handle this:

     

     

    In the dataset designer set the primary key column identity specification to start at -1 and to increment by -1.  This way there will not be any clash between locally generated ids and those from the db.  Then in the child data rows extend the partial classes to register to the ColumnChanged event of the master table.  Then if the column is the primary key and the row is the parent row, update the id of the forign key to the real id.

     

    So when you first call masterTA.Update(dataset.Master), the sql of the insert will update the row's PK which will cause events to fire to update the children.  When the next line is executed childTA.Update(dataset.Child), the id are now correct so everything works.

     

    Hope this helps,

    Charles

    Sunday, December 2, 2007 8:46 PM
  • Thanks for the prompt reply. I'm no novice at VB but I am certainly not an expert so when you say "Then in the child data rows extend the partial classes to register to the ColumnChanged event of the master table", what in particular do you mean. Do you have some sample code to do this? Thanks much.

    P.s. Just to clarify, I want to use the primary key from the master table as the foreign key of all detail rows.
    Monday, December 3, 2007 12:30 AM
  • In the dataset designer, if you double click on the child table, you will see that it creates a new file in the solution with code similar to this:

     

    Partial Class ChildDataTable

    End Class

     

    In there you can extend the code generated by the dataset.

     

    Charles

    Monday, December 3, 2007 12:42 AM
  • I got that part, I just need to know what code to put in the partial class . Thanks much.
    Monday, December 3, 2007 12:54 AM
  • This code is untested but something like this should work:

     

    Partial Class MyDataset

        Partial Class ChildRow

            Public Sub AttachToParentChangedEvent()
                AddHandler Me.GetParentRow("FK_Parent").Table.RowChanged, AddressOf UpdateFK
            End Sub

            Private Sub UpdateFK(ByVal sender As Object, ByVal args As DataRowChangeEventArgs)
                If args.Row("PrimaryKey") <> Me("ForeignKey") Then
                    Me("ForeignKey") = args.Row("PrimaryKey")
                End If
            End Sub

        End Class

        Partial Class ChildDataTable

            Private Sub ChildAdded(ByVal sender As Object, ByVal args As DataTableNewRowEventArgs) Handles Me.TableNewRow
                CType(args.Row, ChildRow).AttachToParentChangedEvent()
            End Sub

        End Class

    End Class

    Monday, December 3, 2007 1:44 AM
  • That helps a lot. However, after putting the code into my project with the necessary changes to suit it, compiling and running, I get an exception 'object reference not set to an instance of an object' at the line where 'AddHandler Me.GetParentRow("FK_Parent").Table.RowChanged, AddressOf UpdateFK' is. For FK_Parent, i assume I am to put the name of the relation between Parent and Child table as displayed in the DataSet Designer? This seems to be where the problem is.
    Monday, December 3, 2007 2:44 AM
  • Hi, check if the parent dataset is populated and as an alternative use Me.Parent where Parent is the name of the parent table.

     

    Charles

    Monday, December 3, 2007 2:56 AM
  • Not too sure how to do that at run time. You have to forgive my low experience level. When you say use Me.Parent, where are suggesting I use it? In place of FK_Parent? Don't think that works.
    Monday, December 3, 2007 3:06 AM
  • Here's what I have anyway:

        Partial Class tblClassCourseListRow

            Public Sub AttachToParentChangedEvent()
                AddHandler Me.GetParentRow("tblClassList_tblClassCourseList").Table.RowChanged, AddressOf UpdateFK
            End Sub

            Public Sub UpdateFK(ByVal sender As Object, ByVal args As DataRowChangeEventArgs)
                If args.Row("ClassListID") <> Me("ClassListID") Then
                    Me("ClassListID") = args.Row("ClassListID")
                End If
            End Sub

        End Class

        Partial Class tblClassCourseListDataTable

            Private Sub tblClassCourseListAdded(ByVal sender As Object, ByVal args As DataTableNewRowEventArgs) Handles Me.TableNewRow
                CType(args.Row, tblClassCourseListRow).AttachToParentChangedEvent()
            End Sub

        End Class

    The parent table is "tblClassList" and the child table is "tblClassCourseList". The parent identity column and child foreign key column is "ClassListID". In the DataSet Designer, the tables have a relation called "
    tblClassList_tblClassCourseList" based on ClassListID. On the form, I have the strong typed dataset named "RichardsDataSet" and binding sources "TblClassCourseListBindingSource" and "tblClassListBindingSource". Both binding sources have DataSource set to "RichardsDataSet" with their DataMember set to their respective tables. When the form is first opened, I call the tblClassListBindingSource.AddNew method, then open up a sort of lookup table in the database that contains the child rows I want to relate with the new parent record, then loops through each row calling Me.RichardsDataSet.tblClassCourseList.Rows.Add(rowNew) to add rowNew = Me.RichardsDataSet.tblClassCourseList.NewtblClassCourseListRow. A DataGridView on the form has its DataSource set to TblClassCourseListBindingSource to display the child rows. Before I started having troubles, this code worked, and rows are added and displayed on the DataGridView but obivously the ClassListID column on the new rows is set to null even after saving the changes to the database. Hope this clarifies my situation
    Monday, December 3, 2007 3:36 AM
  • Ah, so I think the problem is that you should set the class list id on new rows to be the id of the parent - the negative temporary id. that way GetParentRow should be able to find the parent row in the master table.

     

    Charles

    Monday, December 3, 2007 6:28 PM
  • I can't do anything at runtime with the new rows since the error happens when I first call Me.RichardsDataSet.tblClassCourseList.NewtblClassCourseListRow. So what are you suggesting I do at design time? I tried setting the default value of ClassListID in the child table to "-1" but that didn't work.
    Monday, December 3, 2007 6:54 PM
  • OK, try the following:

     

    Partial Class tblClassCourseListDataTable

            Private Sub tblClassCourseListAdded(ByVal sender As Object, ByVal args As DataTableNewRowEventArgs) Handles Me.TableNewRow

                           

                            args.Row("ForeignKey") = m_newParentID
                CType(args.Row, tblClassCourseListRow).AttachToParentChangedEvent()
            End Sub

                    private  m_newParentID as integer;

                    public property NewParentID() as integer

                        set(ByVal Value As Integer)

                             m_newParentID = Value

                        end set


        End Class

     

    Before you call newXyzRow, you need to set the NewParentID property to the temporary id of the parent.  This is a little ugly, but once that works you can replace by an event like ParentIdNeeded...

     

    I hope this works better.

    Charles

    Monday, December 3, 2007 7:10 PM
  • Still getting the "Object reference not set to an instance of an object" error at that GetParentRow line. This is sooo frustrating, aargh. I'm going to have to start paying you by the hour...

    I have the following now in my new row loop:

            ...
            Me.RichardsDataSet.tblClassCourseList.NewParentID = CType(Me.txtClassListID.Text, Integer)
            rowNew = Me.RichardsDataSet.tblClassCourseList.NewtblClassCourseListRow
            ...

    And the following the DataSet Partial Class:

        Partial Class tblClassCourseListRow

            Public Sub AttachToParentChangedEvent()
                AddHandler Me.GetParentRow("tblClassList_tblClassCourseList").Table.RowChanged, AddressOf UpdateFK
            End Sub

            Public Sub UpdateFK(ByVal sender As Object, ByVal args As DataRowChangeEventArgs)
                If args.Row("ClassListID") <> Me("ClassListID") Then
                    Me("ClassListID") = args.Row("ClassListID")
                End If
            End Sub

        End Class

        Partial Class tblClassCourseListDataTable

            Private m_newParentID As Integer

            Private Sub tblClassCourseListAdded(ByVal sender As Object, ByVal args As DataTableNewRowEventArgs) Handles Me.TableNewRow
                args.Row("ClassListID") = m_newParentID
                CType(args.Row, tblClassCourseListRow).AttachToParentChangedEvent()
            End Sub

            Public Property NewParentID() As Integer
                Get
                    Return m_newParentID
                End Get

                Set(ByVal Value As Integer)
                    m_newParentID = Value
                End Set
            End Property

        End Class

    Monday, December 3, 2007 10:44 PM
  • Ok, from the following line I understand that you take the parent id from the textbox:

     

    Me.RichardsDataSet.tblClassCourseList.NewParentID = CType(Me.txtClassListID.Text, Integer)

     

    First, are you using data bindings to bind the parent and children?  You need to call EndEdit on the parent so that the parent is saved back to the data table (before setting NewParentID).  Place a break point after that line to make sure that the content of that table is ok (ie contains the row with proper id).

     

    Charles

    Tuesday, December 4, 2007 12:46 AM
  • Yes, I'm using a BindingSource for both tables. I have a few controls on the form bound to the parent table and a DataGridView bound to the child table. So, calling EndEdit right before setting NewParentID works... UNTIL I save the changes to the database, when I get an error "The INSERT statement conflict with the FOREIGN KEY contraint..." Before that happens, when the form is loaded, the parent row and child rows show "-1" for ClassID but when it is saved to the database, the new parent row gets a different ClassListID (2, in this case, becuase 1 is used) and no child rows are created. Could this because in SQL Express I have auto-increment set to 1 and see to 0? Also, I don't know if I want to EndEdit before creating the rows when the form is loading. I want the user to be able to make changes to the record (parent or child rows) before they even decide to save them to the database, thus I don't want to save ANYTHING to the database until the user presses the OK button on that particular form.
    Tuesday, December 4, 2007 1:53 AM
  • EndEdit does not save to the db, only to the in-memory dataset.  Make sure that you call the Parent table adapter's Update Method first, then the child's. 

    The constraint error is probably because the children's FK are not updated. Place a breakpoint in the UpdateFK method and make sure it's called when the parent's TA Update method is called.

     

    Charles

     

    Tuesday, December 4, 2007 2:18 AM

  • Do I understand it correctly that you're copying existing child rows and associating them with a parent row?  I take it the user uses the textbox to specify which parent row to associate the new child rows with.  But in that case, why is the parent ID changing?  Maybe you're making a new copy of the parent row also.  As you create each child row in the dataset, you wire it to the parent table's RowChanged event.  The RowChanged event is intended to fire when the parent row is saved to the database and it gets its autogenerated ID. 

     

    But I question whether the RowChanged event really fires.  When the database autogenerates the ID, does it automatically update the dataset table row with that ID?  I thought a dataset only gets updated when you explicitly query the database.  If it doesn't get updated in the dataset, then the event won't fire.  If it does get updated in the dataset, then you don't need the event to tell you that.  Just save the parent row and read its new ID value.  I might be way off on this, but this is my understanding at the moment.

    Tuesday, December 4, 2007 4:23 AM
  • The UpdateFK method is only being called when the parent bindsource's endedit method is called, again, when the user presses the OK button, before the update methods are called. The error then happens when the child table's update method is called. When the UpdateFK method is called, args.Row("ClassListID") is assigned a value so the method doesn't even do anything.
    Tuesday, December 4, 2007 4:00 PM
  • JM,

    What I am doing, is basically copying a "structure" of a Class (my program is an application for a school) and it's associated Courses, so that I can have more than one copy of the structure in the database. I call the parent table "ClassList", which is related to a Student record (so each Student is associated with it's own ClassList record, which are all related to one Class structure). Each ClassList record is then associated with many child ClassCourseList records which define the default Courses they are to take with the selected Class.

    When a user first goes to enroll a student in a class, they choose the Class or "structure" from a list, which then opens up a form for a new ClassList record, associates it to the student and selected Class (with no problem), then adds the child ClassCourseList rows based on the structure (which there isn't any problems doing, besides the foreign key problem). At this point, I want the user to be able to make changes to some ClassList fields AS WELL as ClassCourseList records (adding, modifying, deleting), before ANY changes are even saved to the database. This is where the problem occurs where I want the child ClassCourseList records to be related to parent ClassList records by the parent's primary key ClassListID. I hope that makes sense .

    If you guys need any code to clarify things, let me know. All this help is much appreciated.
    Tuesday, December 4, 2007 4:14 PM
  • My situation is somewhat different, though I think the problem might be the same.  My code should create a parent row and child rows, and save them all at the same time.  The parent row gets its auto-generated UID as it's stored to the database, and the child rows don't know what that UID is.


    So an important question is whether .NET provides a way for the database to communicate the parent row's new UID back to the dataset.  I wouldn't guess that the parent's RowChanged event would accomplish that, but I really don't know yet.  Do you know from observation that it does fire, and does provide the new auto-generated value, or is that just the intention?


    I need to do more experimentation, but if all else fails, I might do something like this -- Give the parent table a "TempUID" column.  Before saving the parent row, give that column a value that's sure to be unique, based on the user and the time.  Save the parent row, and the auto-generated UID is stored to its own column.  We don't know what that UID is, so we query based on TempUID.  Now we have the record, and can read the real UID, and store that to the child rows, then save them to the database.


    That's really ugly code, and I'm just a beginner, and I hope Charles or any other experienced programmer can tell us the right way to do this.  I hope Charles is already right about using the RowChanged event.


    I know very little VB (I'm working in C#), and so had a hard time reading your code, so it's easier for me if we talk conceptually rather than review code.

    Tuesday, December 4, 2007 6:04 PM
  • Hi DM & Dillon.

    What you are both doing is generating an id in the database. That id is automatically updated in the datarow just after the insert because the insert command includes a query 'insert bla blah;select x,y,z where id = scope_identity()' that retreives the new id and stores it in the dataset. (you can double check that in the dataset designer).

     

    I just created a sample project to demonstrate how to do this.  This time i wrote the code in c#. Dillon to get you started on a vb version, note that in vb overriding EndInit is not required with the Handles systax.

     

    I changed a little the code compared to what we were discussing. It's a little simpler but not entirely clean. If you have any questions don't hesitate.

     

    Regards,

    Charles

    Wednesday, December 5, 2007 3:34 AM
  • Charles,

     

    Thanks, that's very helpful.  I see what you're saying, that the new ID generated by the database is fed back into the dataset, and that's what makes it possible to wire up the events that update the child rows with the new ID.


    Your specific example involves using a DataGridView event.  In my situation, some of my child tables don't appear in any user controls.  Also, since we know when the master row is changing, it seems like it's possible to do this without any events, as in this code, which is saving 1 master row and 1 child row that were created at the same time:


    Code Block
    private void Save()
    {
      Validate();
      // Disable constraints for now
      m_dataSet.EnforceConstraints = false;
      // Get the master row
      DataSet.MasterRow master = (DataSet.MasterRow)(((DataRowView)masterBindingSource.Current).Row);
      // Get the child row
      DataSet.DetailRow detail = (DataSet.DetailRow)(((DataRowView)detailBindingSource.Current).Row);
      // Save the master row
      masterBindingSource.EndEdit();
      masterTableAdapter.Update(master);
      // Store the new database-generated ID to the child row
      detail.DetailID = master.MasterID;
      // Save the child row
      detailBindingSource.EndEdit();
      detailTableAdapter.Update(detail);
      // Re-enable constraints
      m_dataSet.EnforceConstraints = true;
    }

     

    Is there anything short-sighted about this, or is this an acceptable way of doing this?
     

    Thanks a lot.

     

    JM

    Wednesday, December 5, 2007 9:44 AM
  • It seams ok, the only thing is this line:

     

    detail.DetailID = master.MasterID;

     

    Shouldn't it be

     

    detail.MasterID = master.MasterID;

     

    Charles

    Wednesday, December 5, 2007 11:53 PM
  • Yes, I think you're right;  I misread your intention in the database, and didn't notice that args.Row.Cells[1] refers to Detail.MasterID.


    One more point in case it pertains to any of the exceptions that Dillon was seeing -- According to the documentation I've seen, the typical way to relate two tables is:  Set the parent table's BindingSource.DataSource to the dataset and its BindingSource.DataMember to the parent table.  Create the relation (which belongs to the parent table).  Set the child table's BindingSource.DataSource to the parent table's BindingSource, and its BindingSource.DataMember to the relation.  The result is that when you move the row pointer in the parent table, the child table is automatically filtered to the related records.


    This is somewhat dysfunctional however because if you try to create and save child rows, it produces exceptions -- at least, I haven't yet figured out how to make it work.


    I assume this is why in your sample project you instead set the child table BindingSource.DataSource and BindingSource.DataMember to the dataset and the child table.  Now there's no problem saving child rows.  However the child table is no longer filtered to only the related records.  So you handled the parent table's PositionChanged event, and every time that fires, you set the child table's BindingSource.Filter.  Now, the child table is always correctly filtered.  In this setup, I don't know if the relation is even accomplishing anything.  Maybe it creates a behind-the-scenes index for faster filtering.


    I'd be happy to learn of any better way to do this.  Maybe LINQ to SQL is the answer.

     

     

    Thursday, December 6, 2007 1:35 AM
  • The relation ensure the integrity of the data within the dataset.  It also makes the designer generate properties and methods to access child rows / parent rows from a datarow.  I don't know if behid the scene it generates an index.

     

    There is quite a few problems/limitations/gotchas with databinding.  See the 7th post in this thread for a good summary of the problems encoutered by Robert.

     

    Linq to sql is not a magical answer to binding problems because in that case you have to implement manually all the interfaces that databinding require to work properly. But at least you control exactly what gets executed since there is no autogenerated code.

     

    Regards,

    Charles

    Thursday, December 6, 2007 2:55 AM
  • Charles,

    I'm hoping some of your code in the project you provided will help me. I don't have much experience with working in Visual C# however and could appreciate a conversion of the following code to VB :-). I do understand the just of what's going on. Trying to convert the "this.ColumnChanged..." line throws me off. Thanks!

    Code Block

       partial class DataSet
        {
            partial class MasterDataTable
            {
                public override void EndInit()
                {
                    base.EndInit();
                    this.ColumnChanged += new DataColumnChangeEventHandler(MasterDataTable_ColumnChanged);
                }

                void MasterDataTable_ColumnChanged(object sender, DataColumnChangeEventArgs args)
                {
                    if (args.Column.ColumnName == "MasterID") {
                        MasterRow master = (MasterRow)args.Row;
                        DataRow[] children =
                            DataSet.Tables["Detail"].Select(
                                String.Format("MasterID={0}", master["MasterID", DataRowVersion.Original]));
                        foreach (DataSet.DetailRow row in children) {
                            row.MasterID = (int)args.ProposedValue;
                        }
                    }
                }
            }
        }
    }


    Friday, December 7, 2007 2:52 AM
  • Hi,

     

    I think it should be like this (untested):

     

    private sub MasterDataTable_ColumnChanged(object sender, DataColumnChangeEventArgs args) handles Me.ColumnChanged
                    if (args.Column.ColumnName == "MasterID") then
                        dim master as MasterRow = Ctype(args.Row, MasterRow);
                        dim children as DataRow() =
                            DataSet.Tables("Detail").Select(
                                String.Format("MasterID={0}", master.Item("MasterID", DataRowVersion.Original)));
                        foreach (row as DataSet.DetailRow in children) {
                            row.MasterID = ctype(args.ProposedValue, int);
                        }
                    }
                }

    The EndEdit method is not needed because of the 'handles' syntax provided by vb.

     

    Charles

    Friday, December 7, 2007 2:56 AM
  • The part giving me a problem is as follows:

    dim master as MasterRow = Ctype(args.Row, MasterRow);
    dim children as DataRow() =
                            DataSet.Tables("Detail").Select(
                                String.Format("MasterID={0}", master.Item("MasterID", DataRowVersion.Original)));

    There is no exact MasterRow object in VB and I can't seem to find the best way to access my child table from the parent table's class, which is within my dataset class. Instead of the MasterRow object, would I just use the DataRow object? For the parent DataRow, I have no clue.
    Saturday, December 8, 2007 7:38 AM
  • The MasterRow type is the strongly typed master DataRow. In your case I think it's ClassListRow.

     

    Charles

    Saturday, December 8, 2007 6:56 PM
  • That make sense, thanks :-). And I didn't word my last post right: I have no idea what to assign the child row. I can't seem to access the parent row in the data set the same way you did in your C# project.
    Saturday, December 8, 2007 7:20 PM
  • I'm not sure exactly what you mean but the code wasn't transalted right:

     

    Dim children As DataRow() = _
                            DataSet.Tables("Detail").Select( _
                                String.Format("MasterID={0}", master.Item("MasterID", DataRowVersion.Original)))

     

    charles

    Saturday, December 8, 2007 7:48 PM
  • I don't have an option for Tables in the IntelliSense list for my dataset "RichardsDataSet"
    Saturday, December 8, 2007 8:28 PM
  • Actually DataSet is a property of the DataTable object. ie Me.DataSet (this one you don't rename). To get it as a stringly-typed dataset, just cast it using ctype.

     

    Charles

    Saturday, December 8, 2007 8:30 PM
  • Thanks for all the help guys, it's much appreciated. Finals are over so now I can continue on with this project. I made all the changes based on the sample MasterDetail program. When I save my form, the ColumnChanged event fires for my master datatable, however, the even doesn't fire for my parent ID column. This is weird because it does change from -1 to whatever the next available value is in the database. Any ideas? I tried my best to replicate what's being done in the C# program in mine. Thanks and happy holidays!
    Sunday, December 23, 2007 3:43 PM
  • Hi Charles,
    OK, so I just realized my Insert command for my master data table doesn't include the "select .... where id = scope_identity()" part. Is this supposed to be automatically included in the command? When I open Query Builder for the Insert command of the master table in your project, there is nothing about Scope_Identity shown, but when I look at the CommandText in the properties toolbox, it's there. How can I get this included in my project?
    Saturday, December 29, 2007 6:04 PM
  • OK, I figured out the SCOPE_IDENTITY part. Now the ColumnChanged event is finally firing for my identity column, however I am now getting an "object reference not set to an instance of an object error on the Dim Children()... line below. I hope I translated your C# code properly.

    Code Block

    Private Sub tblClassListDataTable_ColumnChanged(ByVal sender As Object, ByVal e As System.Data.DataColumnChangeEventArgs) Handles Me.ColumnChanged
                If e.Column.ColumnName = "ClassListID" Then
                    Dim Master As RichardsDataSet.tblClassListRow = CType(e.Row, DataRow)
                    Dim Children() As DataRow = DataSet.Tables("tblClassCourseList").Select( _
                        String.Format("ClassListID={0}", Master("ClassListID", DataRowVersion.Original)))
                    Dim Row As RichardsDataSet.tblClassCourseListRow
                    For Each Row In Children
                        Row.ClassListID = e.ProposedValue
                    Next
                End If
            End Sub


    Saturday, December 29, 2007 6:42 PM
  •  

    Hi,

     

    try to break the line in multiple statement.  That way you'll see what return null/nothing.

     

    Charles

    Saturday, December 29, 2007 8:38 PM
  • I broke it down as follows. The null is happening at the Dim Children() line. RowFilter returns what is expected.

    Code Block

    Private Sub tblClassListDataTable_ColumnChanged(ByVal sender As Object, ByVal e As System.Data.DataColumnChangeEventArgs) Handles Me.ColumnChanged
            If e.Column.ColumnName = "ClassListID" Then
                Dim Master As RichardsDataSet.tblClassListRow = CType(e.Row, DataRow)
                Dim FilterArg As Object = Master("ClassListID", DataRowVersion.Original)
                Dim RowFilter As String = String.Format("ClassListID={0}", FilterArg)
                Dim Children() As DataRow = DataSet.Tables("tblClassCourseList").Select(RowFilter)
                Dim Row As RichardsDataSet.tblClassCourseListRow
                For Each Row In Children
                    Row.ClassListID = e.ProposedValue
                Next
            End If
       End Sub


    Saturday, December 29, 2007 9:57 PM
  •  

    My guess is that the DataSet.Tables("tblClassCourseList") return null. Please verify if it's the case and double check the name...

     

    In the VS immediate window when you hit this line with a breakpoint execute "? DataSet.Tables[0].TableName" and change the index until you find the right table. Verify that the name is the same.

     

    Charles

    Saturday, December 29, 2007 10:12 PM
  • When I try index 0, or any index, I get "Referenced object has a value of 'Nothing'." Maybe "DataSet" isn't the right object to use.
    Saturday, December 29, 2007 10:58 PM
  • So Me.DataSet is null?  Is your table contained in a dataset?

     

    Saturday, December 29, 2007 11:01 PM
  • Me.DataSet is Nothing. My table is in the dataset "RichardsDataSet".
    Saturday, December 29, 2007 11:38 PM
  • Yes but do you new up the table or the dataset?

     

    dim tbl as MyTable = new MyTable() ' tbl.DataSet is null

     

    -- vs --

     

    dim ds as MyDataset = new MyDataset()

    dim tbl as MyTable = ds.tblMyTable ' tbl.DataSet = ds

     

    Charles

    Sunday, December 30, 2007 12:17 AM
  • It finally works! Well, sort of... The problem was when I was calling the Update method, I wasn't passing the whole DataSet, I was passing only changed rows of the corresponding DataTable in several steps as shown below:

    Code Block

                Dim NewClassListRecord As RichardsDataSet.tblClassListDataTable = _
                   CType(RichardsDataSet.tblClassList.GetChanges(DataRowState.Added), _
                   RichardsDataSet.tblClassListDataTable)

                Dim ModClassListRecord As RichardsDataSet.tblClassListDataTable = _
                   CType(RichardsDataSet.tblClassList.GetChanges(DataRowState.Modified), _
                   RichardsDataSet.tblClassListDataTable)

                Dim DelClassListRecord As RichardsDataSet.tblClassListDataTable = _
                    CType(RichardsDataSet.tblClassList.GetChanges(DataRowState.Deleted), _
                    RichardsDataSet.tblClassListDataTable)

                Dim DelClassCourseList As RichardsDataSet.tblClassCourseListDataTable = _
                   CType(RichardsDataSet.tblClassCourseList.GetChanges(DataRowState.Deleted), _
                   RichardsDataSet.tblClassCourseListDataTable)

                Dim NewClassCourseList As RichardsDataSet.tblClassCourseListDataTable = _
                   CType(RichardsDataSet.tblClassCourseList.GetChanges(DataRowState.Added), _
                   RichardsDataSet.tblClassCourseListDataTable)

                Dim ModClassCourseList As RichardsDataSet.tblClassCourseListDataTable = _
                   CType(RichardsDataSet.tblClassCourseList.GetChanges(DataRowState.Modified), _
                   RichardsDataSet.tblClassCourseListDataTable)


                    'Delete ClassCourseList records
                    If Not DelClassCourseList Is Nothing Then
                        Me.TblClassCourseListTableAdapter.Update(DelClassCourseList)
                    End If

                    'Insert new ClassList records
                    If Not NewClassListRecord Is Nothing Then
                        Me.tblClassListTableAdapter.Update(NewClassListRecord)
                    End If

                    'Update modified ClassList records
                    If Not ModClassListRecord Is Nothing Then
                        Me.tblClassListTableAdapter.Update(ModClassListRecord)
                    End If

                    'Insert new ClassCourseList records
                    If Not NewClassCourseList Is Nothing Then
                        Me.TblClassCourseListTableAdapter.Update(NewClassCourseList)
                    End If

                    'Update modified ClassList records
                    If Not ModClassListRecord Is Nothing Then
                        Me.tblClassListTableAdapter.Update(ModClassListRecord)
                    End If

                    'Update modified ClassCourseList records
                    If Not ModClassCourseList Is Nothing Then
                        Me.TblClassCourseListTableAdapter.Update(ModClassCourseList)
                    End If

                    'Delete ClassList records
                    If Not DelClassListRecord Is Nothing Then
                        Me.tblClassListTableAdapter.Update(DelClassListRecord)
                    End If


    When I simply call tblClassListTableAdapter.Update(Me.RichardsDataSet.tblClassList) and tblClassCourseListTableAdapter.Update(Me.RichardsDataSet.tblClassCourseList), it works. I don't want to do this though, I want to be able to save records like above. Is this attainable using the code you have provided me?
    Sunday, December 30, 2007 4:33 PM
  • Why do you want to separate deleting, updating and inserting updates??

     

    Sunday, December 30, 2007 7:11 PM
  • Deletions of records are done in the opposite order of inserts and I just want to maintain that kind of structure in my program. So, do you think it is possible to use a variation of your code to do this?
    Monday, December 31, 2007 6:21 PM
  • But what do you gain?  I don't see any advantages and a couple disadvantages - longer code, less maintainable, etc

    Monday, December 31, 2007 7:53 PM
  • After much research I've decided to seek outside help.  Here's the very basic scenario:

    Environment:
    VB 2008 Express Edition
    Access 2007 DB
    Windows Vista

    Table Structure:
    Customers contains many Purchases contains many Items Purchased (told you it was basic)

    Application:
    I select a customer from a datagridview which then open the Purchases Form with selected Customer Information displayed

    Me.CustomersTableAdapter.FillByLicenseNumber(Me.CapitalRecyclingPOSDataSet.Customers, LicenseNumber)

    I then programatically insert into the Purchases table the customer information (CustomerID)

    Me.PurchasesTableAdapter.Insert(Purchase_DateDateTimePicker.Value, "", 1, CInt(CustomerIDTextBox.Text))

    which subseqently creates the new purchase record in the Purchases table complete with autogenerated (PurchaseID). 

    On this same form is a third TableAdapter called ItemsPurchased (in grid form) where I want to be able to tab into and begin to enter information. 

    My question:  How can I link the new items ItemsPurchased DataGridView to the newly created Purchase Record without knowing the unique ID that was autogenerated by the database?  They are linked in the database via a PK/FK relationship.

    Since I'm using Access the "Refresh the Data after Insert" under Advanced Options in the Query Wizard is disabled. 

    Any help is greatly appreciated as I've spent the past 2 days struggling with this seemingly simple application.

    Regards,
    Jason
    Monday, December 31, 2007 8:49 PM
  • Hi Jason,

     

    Since access cannot refresh the data automatically, you have to do it manually.  After the insert do a select that returns only the new row. From that row you get the generated id then you manually assign it to the relation's FK.

     

    Hope this helps,

    Charles

    Monday, December 31, 2007 10:30 PM
  • Hi Charles,

    I tried everything you suggested, and I still can't get it to work. The only difference I have is that my dataset is created in code.

    Here's my relationship code:

     DataRelation relation = new DataRelation("IssueToSolution",
                    this.DataSet.Tables["IssueList"].Columns["IssueID"],
                    this.DataSet.Tables["IssueSolutions"].Columns["IssueID"], true);
                DataSet.Relations.Add(relation);

                DataSet.Relations["IssueToSolution"].ChildKeyConstraint.AcceptRejectRule = AcceptRejectRule.Cascade;
                DataSet.Relations["IssueToSolution"].ChildKeyConstraint.DeleteRule = Rule.Cascade;
                DataSet.Relations["IssueToSolution"].ChildKeyConstraint.UpdateRule = Rule.Cascade;

    I set the autoincrement to -1 also.

    I also had to cal lthe endedit on the header before I could add the detail line, to at least see it.Though, all this works if I save the header record to the DB first, then add the detail lines...
    Tuesday, January 8, 2008 7:23 PM
  • Ignore my last post. I figured it out.
    Tuesday, January 8, 2008 9:41 PM
  • Great!

    Charles

    Tuesday, January 8, 2008 11:52 PM
  • If anybody is curious, I posted my findings on the following post:

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



    Wednesday, January 9, 2008 3:22 PM