Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)-VBForums
Page 1 of 2 12 LastLast
Results 1 to 40 of 41

Thread: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

  1. #1

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,662

    Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    How do you filter child data in one control based on a selection from parent data in another control? Like this:

    1. Create a new Windows Forms Application project.
    2. Add two ComboBoxes to the form.
    3. Add two BindingSources to the form.
    4. Add the following code:
    Code:
    Private Sub Form1_Load(ByVal sender As Object, _
                           ByVal e As EventArgs) Handles MyBase.Load
        'Get the data.  The DataSet must contain a Parent table,
        'a Child table and a ParentChild relation between them
        Dim data As DataSet = Me.GetDataSet()
    
        'Bind the parent source to the parent table.
        Me.BindingSource1.DataSource = data
        Me.BindingSource1.DataMember = "Parent"
    
        'Bind the child source to the relationship.
        Me.BindingSource2.DataSource = Me.BindingSource1
        Me.BindingSource2.DataMember = "ParentChild"
    
        'Bind the parent control to the parent source.
        Me.ComboBox1.DisplayMember = "Name"
        Me.ComboBox1.ValueMember = "ID"
        Me.ComboBox1.DataSource = Me.BindingSource1
    
        'Bind the child control to the child source.
        Me.ComboBox2.DisplayMember = "Name"
        Me.ComboBox2.ValueMember = "ID"
        Me.ComboBox2.DataSource = Me.BindingSource2
    End Sub
    
    Private Function GetDataSet() As DataSet
        Dim data As New DataSet
        Dim parent As DataTable = Me.GetParentTable()
        Dim child As DataTable = Me.GetChildTable()
    
        data.Tables.Add(parent)
        data.Tables.Add(child)
    
        'Add a relationship between the ID of the parent
        'table and the ParentID of the child table.
        data.Relations.Add("ParentChild", _
                           parent.Columns("ID"), _
                           child.Columns("ParentID"))
    
        Return data
    End Function
    
    Private Function GetParentTable() As DataTable
        Dim table As New DataTable("Parent")
    
        With table.Columns
            .Add("ID", GetType(Integer))
            .Add("Name", GetType(String))
        End With
    
        With table.Rows
            .Add(1, "Parent1")
            .Add(2, "Parent2")
            .Add(3, "Parent3")
        End With
    
        Return table
    End Function
    
    Private Function GetChildTable() As DataTable
        Dim table As New DataTable("Child")
    
        With table.Columns
            .Add("ID", GetType(Integer))
            .Add("ParentID", GetType(Integer))
            .Add("Name", GetType(String))
        End With
    
        With table.Rows
            .Add(1, 1, "Child1")
            .Add(2, 1, "Child2")
            .Add(3, 1, "Child3")
            .Add(4, 2, "Child4")
            .Add(5, 2, "Child5")
            .Add(6, 2, "Child6")
            .Add(7, 3, "Child7")
            .Add(8, 3, "Child8")
            .Add(9, 3, "Child9")
        End With
    
        Return table
    End Function
    Now run the project and make selections from the parent list in the first ComboBox. Watch the child list filter automatically as you do.

    You can use DataGridView controls instead of ComboBoxes if you like. In fact, because the work is done by the BindingSource components, you can use any controls at all that you can bind to a BindingSource.

    Now, before anyone says that that's all well and good but I'm creating the DataTables myself and they are getting their data from a database, that makes absolutely no difference whatsoever. Note that in my Load event handler I call GetDataSet to get a DataSet. All that matters at that point is that a DataSet with the appropriate format is returned. Where that DataSet comes from is irrelevant to the code that uses it, so you can implement the GetDataSet function in any way you like. Obviously, if your tables and relation have different names then you should adjust my code to use those names. I feel that this disclaimer is required because it happens with monotonous regularity that I post example code an someone just copies and pastes it and then wonders why it doesn't work. This is an example only to illustrate a principle. If you want to implement that principle then you can either write your own code from scratch or else use mine as a starting point and adjust it as needed.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  2. #2
    PowerPoster Jenner's Avatar
    Join Date
    Jan 2008
    Location
    Mentor, OH
    Posts
    3,485

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Nice trick. I didn't know you could bind a control to a relation like that.
    My CodeBank Submissions: TETRIS using VB.NET2010 and XNA4.0, Strong Encryption Class, Hardware ID Information Class, Generic .NET Data Provider Class, Lambda Function Example, Lat/Long to UTM Conversion Class, Audio Class using BASS.DLL

    Remember to RATE the people who helped you and mark your forum RESOLVED when you're done!

    "Two things are infinite: the universe and human stupidity; and I'm not sure about the universe. "
    - Albert Einstein

  3. #3
    Lively Member
    Join Date
    Jun 2008
    Posts
    110

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Hi jmcihinney, your link really helps.

    However, it is not exactly what I want. It just seems a filter

    1. Adding the relation for parent and child require unique id in parent table. But I don't want this constraint. Since the parent id is only used to filter the rows in child, i don't know why it need to be unique

    2. In your case, user click a row in Parent combobox (I try datagridview1 here), and then only rows match the parent id will be displayed in "child" datagridview2. But what I want is display all in dgv2(child), but set the row(s) in dgv2 that match the parent id in selected row in dgv1(parent) as selected.
    At the same time, if user select a row in dgv2, the selection in dgv1 is also located accordingly.

  4. #4

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,662

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Quote Originally Posted by sunhpj
    Hi jmcihinney, your link really helps.

    However, it is not exactly what I want. It just seems a filter

    1. Adding the relation for parent and child require unique id in parent table. But I don't want this constraint. Since the parent id is only used to filter the rows in child, i don't know why it need to be unique

    2. In your case, user click a row in Parent combobox (I try datagridview1 here), and then only rows match the parent id will be displayed in "child" datagridview2. But what I want is display all in dgv2(child), but set the row(s) in dgv2 that match the parent id in selected row in dgv1(parent) as selected.
    At the same time, if user select a row in dgv2, the selection in dgv1 is also located accordingly.
    1. The ParentID column in the Child table has no unique constraint. This is a one-to-many relationship. Each ParentID value can appear only once in the Parent table but can appear many times in the Child table. If the same ParentID appeared more than once in the Parent table then how could you tell which record was the actual parent of a Child record with that value? The whole point of the ParentID is to uniquely identify a Parent record. The relationship is to uniquely identify a Child record's parent.

    2. There's no way to do that automatically. You'd basically have to bind the two grid's to the DataTable's independently. When the user makes a selection in the parent grid you can get the selected row, call its GetChildRows method to get all the related rows, then loop through the child grid and select those records. To go the other way you can call GetParentRow.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  5. #5
    New Member
    Join Date
    Nov 2008
    Posts
    4

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    I used the above code and it worked great - very straightforward and easy to use. I'm new to Visual Studio 2005, so I'm still trying to figure out how everything connects.

    One question though. On my form, I want to have multiple datagridviews which are based off of the selection in the child combo box. Would you use similar code to what you have above, creating a relation between the child table and the datagrid tables, and could you do this within the same function (GetDataset, in your example)? Thanks.

  6. #6

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,662

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Quote Originally Posted by mungle_back
    I used the above code and it worked great - very straightforward and easy to use. I'm new to Visual Studio 2005, so I'm still trying to figure out how everything connects.

    One question though. On my form, I want to have multiple datagridviews which are based off of the selection in the child combo box. Would you use similar code to what you have above, creating a relation between the child table and the datagrid tables, and could you do this within the same function (GetDataset, in your example)? Thanks.
    Not a problem. The controls used to display the parent and child data don't have to be the same. Also, you can have as many levels of relationship as you like. You can have a parent table, a child, a grandchild, a great grandchild and so on. This thread demonstrates the principle between one pair of tables. There can be as many pairs as you like with as much overlap as you like, all in the same DataSet.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,820

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    @jm - hope you don't mind me putting this question into this thread - if you do I'll delete it and start a new thread...

    At any rate - I've got a more complex relationship and I was wondering if this would be possible with the method you describe here. I'll attach a mock-up of the form we are considering using for this.

    Basically a binding navigator is used to flip through all the VENDOR's in a table. Each vendor has child records in another table - that fall into 1 of 4 categories - Translater, Proofreader, Interpretor and Foreign associate.

    There can be many child records for a VENDOR for each of these categories based on the "language pair" being represented (they can translate from English-to-Spanish and from Eng-to-Portuguese, for example).

    We were thinking of having a tab control for each of these categories - with it's own bind-nav control.

    Would a WHERE clause simply put the right category onto the correct tab control?

    Am I headed down the right path here?

    Thanks in advance
    Attached Images Attached Images  

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,662

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Quote Originally Posted by szlamany
    @jm - hope you don't mind me putting this question into this thread - if you do I'll delete it and start a new thread...

    At any rate - I've got a more complex relationship and I was wondering if this would be possible with the method you describe here. I'll attach a mock-up of the form we are considering using for this.

    Basically a binding navigator is used to flip through all the VENDOR's in a table. Each vendor has child records in another table - that fall into 1 of 4 categories - Translater, Proofreader, Interpretor and Foreign associate.

    There can be many child records for a VENDOR for each of these categories based on the "language pair" being represented (they can translate from English-to-Spanish and from Eng-to-Portuguese, for example).

    We were thinking of having a tab control for each of these categories - with it's own bind-nav control.

    Would a WHERE clause simply put the right category onto the correct tab control?

    Am I headed down the right path here?

    Thanks in advance
    If I'm reading that correctly then you just have what I've described but four times. You would fill one parent table and four child tables. Each of the child tables would be filled from the same source table with, as you say, a WHERE clause to limit the result set to a single category.

    Your DataSet will then have four DataRelations that you'll bind each of the child BindingSources to.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  9. #9
    Hyperactive Member
    Join Date
    Mar 2008
    Location
    Burlington, ON, Canada
    Posts
    343

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    just a quick shout out to jmcilhinney - as per the suggestions, I had to tweak this code a little to get it pulling from a sql database, and after doing so, it does exactly what i was after - two thumbs up and a big thanks!

  10. #10
    Hyperactive Member
    Join Date
    Mar 2008
    Location
    Burlington, ON, Canada
    Posts
    343

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Here's a question to add to this by the way if anyone can contribute - I'm looking to figure out how to deal with a selectedindexchanged event on the child of this binding so i can pull additional values instead of being restricted to the single value member......

    in my iteration of this concept, i'd like to be able to process on a valuemember in addition to ID.

    normally with a table adapter i would just call the following:

    Code:
    TableAdapter.GetData.Item(combobox2.SelectedIndex).Field_I_Want_To_Work_With
    but in this case its not a tableadapter nor is the selected index of the combobox reflective of the record position as a whole......so it will pull inaccurate data....

    any help would be appreciated...

  11. #11
    Hyperactive Member
    Join Date
    Mar 2008
    Location
    Burlington, ON, Canada
    Posts
    343

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    okay i "sorta" figured it out.....i just changed me.combobox2.valuemember to the field i need to do the processing on - but i'm of the opinion that's messy....(what if i had 100 fields to process on?) - does anybody else have alternatives?

  12. #12

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,662

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Quote Originally Posted by trevorjeaton View Post
    Here's a question to add to this by the way if anyone can contribute - I'm looking to figure out how to deal with a selectedindexchanged event on the child of this binding so i can pull additional values instead of being restricted to the single value member......

    in my iteration of this concept, i'd like to be able to process on a valuemember in addition to ID.

    normally with a table adapter i would just call the following:

    Code:
    TableAdapter.GetData.Item(combobox2.SelectedIndex).Field_I_Want_To_Work_With
    but in this case its not a tableadapter nor is the selected index of the combobox reflective of the record position as a whole......so it will pull inaccurate data....

    any help would be appreciated...
    The SelectedItem of the ComboBox is the entire bound item, so you simply cast it as the appropriate type and then get whatever property of column value you want. For instance, if you've bound a DataTable then each item is a DataRowView, therefore the SelectedItem is a DataRowView. As such you can get the SelectedItem, cast it as type DataRowView and then get any field you want, e.g.
    vb.net Code:
    1. Dim row As DataRowView = DirectCast(myComboBox.SelectedItem, DataRowView)
    2. Dim firstName As String = CStr(row("FirstName"))
    3. Dim lastName As String = CStr(row("LastName"))

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  13. #13
    Hyperactive Member
    Join Date
    Mar 2008
    Location
    Burlington, ON, Canada
    Posts
    343

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    as usual, you're spot on.....my compliments on your extensive knowledge......you are a great help to people "coming up" in VB.....don't ever leave vbforums - we love ya

  14. #14
    Hyperactive Member
    Join Date
    Mar 2008
    Location
    Burlington, ON, Canada
    Posts
    343

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    okay - last question, i promise ;-).....how can the selectedindexchanged event of the parent control trigger the selectedindexchanged event of the child control in this example?

    here's an example using twist ties:

    my parent control is a master material name and my child control is a list of different sizes of that master material (ie master material = "twist tie 1" and child material = "twist tie 1, one inch length", "twist tie 1, two inch length", "twist tie 1, three inch length", etc etc)

    i have a selectedindexchanged event on the child combobox that works great (it changes a length textbox.text value to reflect the length of the material - and that is thanks to the directcast solution above). But when i switch the master material combobox (the parent) to "twist tie 2", of course the child combobox default entry now is "twist tie2, 4 inch length" for example - and the length textbox.text will still reflect the previously selected child combobox value.

    my proposed solution is that in the selectedindexchanged event of the parent combobox, i want to call the selectedindexchanged event of the child combobox and that way regardless of which combobox is changed, the textbox.text will be updated to reflect the current child value....but i'm not sure if that's possible......

    sorry its a little long winded and i'm trying to keep this thread as clean and as relevant possible.....
    Last edited by trevorjeaton; Jul 23rd, 2009 at 08:30 AM.

  15. #15

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,662

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    You don't call an event. If you want to do something on the SelectedIndexChanged of both ComboBoxes then that's exactly what you should do: handle both SelectedChangedIndex events and do the same thing on both. You might have a single method that handles both events and performs the required task or you might have two separate methods that then both call the same third method that does the work.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  16. #16
    Hyperactive Member
    Join Date
    Mar 2008
    Location
    Burlington, ON, Canada
    Posts
    343

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    does the selectedindexchanged event fire on the child combobox when the relationship to the child combobox is refreshed?

    i'm deducing that because a different value is now displayed in the child combobox after the change to the parent combobox via the relationship, that 'some' event must be triggered.....and in that event is where i'd like to place my code to fill my textbox.text based on the new child value.....and of course i'm not quite experienced enoughto put all that together on what that event is.....

    right now it appears as though selectedindexchanged on the child only fires via a user initiated click and not when the parent combobox updates the relationship. would that fall under the displaymemberchanged event?
    Last edited by trevorjeaton; Jul 23rd, 2009 at 11:38 AM.

  17. #17
    Hyperactive Member
    Join Date
    Mar 2008
    Location
    Burlington, ON, Canada
    Posts
    343

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    okay i'm half way there - its "working" when i add my code to the selectedvaluechanged event as well as the selectedindexchanged event, but on form launch, my try catch statement grabs "object reference not set to an instance of an object"

  18. #18
    Hyperactive Member
    Join Date
    Mar 2008
    Location
    Burlington, ON, Canada
    Posts
    343

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    okay fixed - selectedvalue was the key - the object reference errors were because originally my form was using table adapters and i forgot to set their databindings back to nothing when i switched to this code, so they were calling empty items, hence generating the null references - thanks once again to jmcilhinney

  19. #19
    Member
    Join Date
    Apr 2007
    Posts
    52

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Hi there!

    Question for you... I'm trying to expand the concept of this code to multiple parent/child comboboxes. In a nutshell, I have one parent combobox that has one child, and a second that has three children that are unrelated to one another, and all of these are on a single form. Do I need to do each parent and its offspring separately? Or can I get the data for both parent boxes in a single datatable, and all the children in another datatable?


    Thanks in advance,
    Zoe
    "Never shall I fail my comrades, nor leave any to fall;
    For we are not separate persons, but one Guardian to All."

  20. #20

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,662

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Quote Originally Posted by ZoeWashburn View Post
    Hi there!

    Question for you... I'm trying to expand the concept of this code to multiple parent/child comboboxes. In a nutshell, I have one parent combobox that has one child, and a second that has three children that are unrelated to one another, and all of these are on a single form. Do I need to do each parent and its offspring separately? Or can I get the data for both parent boxes in a single datatable, and all the children in another datatable?


    Thanks in advance,
    Zoe
    You have six separate tables and four separate relationships. You don't have to do anything differently. You simply do as I've done multiple times.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  21. #21
    Member
    Join Date
    Apr 2007
    Posts
    52

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Quote Originally Posted by jmcilhinney View Post
    You have six separate tables and four separate relationships. You don't have to do anything differently. You simply do as I've done multiple times.

    Ok, I've set all that up and I'm getting an error "Parent columns and Child columns don't have type-matching columns." I'm pulling these from a SQL database, so all the parent tables have integer or tinyint ID columns, and I've made sure the columns added to the child tables are integer as well.

    Here's the code where I set up the relationships:
    Code:
      Private Function GetDataSet() As DataSet
                connection.Open()
                Dim ds As New DataSet
                Dim parent_cp As DataTable = Me.GetParentTable_CP()
                Dim child_market As DataTable = Me.GetChildTable_Market()
                Dim parent_etype As DataTable = Me.GetParentTable_EType()
                Dim child_ecat As DataTable = Me.GetChildTable_ECat()
                Dim child_prov As DataTable = Me.GetChildTable_Prov()
                Dim child_crr As DataTable = Me.GetChildTable_CRR()
                ds.Tables.Add(parent_cp)
                ds.Tables.Add(child_market)
                ds.Tables.Add(parent_etype)
                ds.Tables.Add(child_ecat)
                ds.Tables.Add(child_prov)
                ds.Tables.Add(child_crr)
    
    
                'Add a relationship between the ID of the parent
                'table and the ParentID of the child table.
                ds.Relations.Add("ParentChild", _
                                           parent_cp.Columns("CPID"), _
                                           child_market.Columns.Add("ParentID", GetType(Integer)))
                ds.Relations.Add("ParentChild", _
                                            parent_etype.Columns("ETypeID"), _
                                            child_ecat.Columns.Add("ParentID", GetType(Integer)))
                ds.Relations.Add("ParentChild", _
                                            parent_etype.Columns("ETypeID"), _
                                            child_prov.Columns.Add("ParentID", GetType(Integer)))
                ds.Relations.Add("ParentChild", _
                                            parent_etype.Columns("EtypeID"), _
                                            child_crr.Columns.Add("ParentID", GetType(Integer)))
    
                Return ds
                connection.Close()
        End Function
    Is there a vb.net data type that matches tinyint? Or do I need to change all my tinyint ID columns to integer? Or am I missing something else entirely?

    Thanks much in advance,
    Zoe
    "Never shall I fail my comrades, nor leave any to fall;
    For we are not separate persons, but one Guardian to All."

  22. #22

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,662

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    @ZoeWashburn

    bigint = Long
    int = Integer
    smallint = Short
    tinyint = Byte

    If your tables have relations between them in the database then all the columns must be compatible data types. That means that either you don't have foreign keys in the database, you're populating your DataTables incorrectly or you're creating your DataRelations incorrectly. The fact that you're using the same name for all four relations is a problem for a start.

    Maybe you should just create a Data Source. That way, all the adapters, tables and relations are created for you.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  23. #23
    Member
    Join Date
    Apr 2007
    Posts
    52

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    @jmcilhinney,

    I've modified the relationships to use the keys in the database (sorry, that was a total dumbass moment, should've set it up that way to begin with). I've verified all the data types match, and all the relationships are set up but one that the database isn't cooperating with me on. It keeps telling me I can't create the relationship because it conflicts with the relationship I'm trying to create. Grrr.

    Anyway... yeah, a data source might be a simpler solution here. I'm getting really tired of it telling me the data types don't match when I've gone to painstaking lengths to ensure that they do match.

    Thanks,
    Zoe
    "Never shall I fail my comrades, nor leave any to fall;
    For we are not separate persons, but one Guardian to All."

  24. #24
    Member lechip's Avatar
    Join Date
    Apr 2011
    Posts
    58

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    What if I what to view the PARENT of an actual record?

  25. #25

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,662

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Quote Originally Posted by lechip View Post
    What if I what to view the PARENT of an actual record?
    This data-binding technique is about filtering child records. There's nothing built in that I aware of that will display or select a parent record based on selection of a child.

    It's not too hard to do with a small amount of code though. Once you've got a child DataRow, you can call its GetParent method to get the parent DataRow. Assuming that you have two BindingSources bound to two DataTables with a DataRelation between them, that might look like this:
    vb.net Code:
    1. Private Sub childBindingSource_CurrentChanged(sender As Object, e As EventArgs) Handles childBindingSource.CurrentChanged
    2.     Dim childRow As DataRow = DirectCast(Me.childBindingSource.Current, DataRowView).Row
    3.     Dim parentRow As DataRow = childRow.GetParentRow("ParentChildRelation")
    4.     Dim parentIndex As Integer = Me.parentBindingSource.Find("ParentID", parentRow("ParentID"))
    5.  
    6.     Me.parentBindingSource.Position = parentIndex
    7. End Sub

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  26. #26
    Member lechip's Avatar
    Join Date
    Apr 2011
    Posts
    58

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Quote Originally Posted by jmcilhinney View Post
    This data-binding technique is about filtering child records. There's nothing built in that I aware of that will display or select a parent record based on selection of a child.

    It's not too hard to do with a small amount of code though. Once you've got a child DataRow, you can call its GetParent method to get the parent DataRow. Assuming that you have two BindingSources bound to two DataTables with a DataRelation between them, that might look like this:
    vb.net Code:
    1. Private Sub childBindingSource_CurrentChanged(sender As Object, e As EventArgs) Handles childBindingSource.CurrentChanged
    2.     Dim childRow As DataRow = DirectCast(Me.childBindingSource.Current, DataRowView).Row
    3.     Dim parentRow As DataRow = childRow.GetParentRow("ParentChildRelation")
    4.     Dim parentIndex As Integer = Me.parentBindingSource.Find("ParentID", parentRow("ParentID"))
    5.  
    6.     Me.parentBindingSource.Position = parentIndex
    7. End Sub
    Tnx for the help!
    Yet the Cast is not allowed.
    I tried using the specific dataTable row too, again, no valid cast.
    Tnx for getting off topic for me!

  27. #27

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,662

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Quote Originally Posted by lechip View Post
    Tnx for the help!
    Yet the Cast is not allowed.
    I tried using the specific dataTable row too, again, no valid cast.
    Tnx for getting off topic for me!
    If the cast is not allowed then that means that the Current property doesn't return a DataRowView, which means that you haven't bound a DataTable.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  28. #28
    Member lechip's Avatar
    Join Date
    Apr 2011
    Posts
    58

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    You're absolutely right, the exceptions actually happens... when I'm binding the DataTable!!! I always get to this loops where the answer gets me back to the problem *facepalm*

  29. #29
    Member lechip's Avatar
    Join Date
    Apr 2011
    Posts
    58

    Resolved Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Hey I figured it out, I just needed to initialize everything carefully!
    Tnx for the help as always jmcilhinney!

  30. #30
    Member chrisguk's Avatar
    Join Date
    Nov 2011
    Posts
    61

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    @jm

    Fantastic thread and a great read, I understand the Parent/Child concepts far better than I ever did.

    Many thanks for your time and effort in putting your answers together : A five start Contributor

  31. #31
    Fanatic Member
    Join Date
    Jul 2006
    Location
    Netherlands
    Posts
    794

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Dear JMC...

    I tried your code and it works perfect. Thanks for this.

    However I have one small question:

    When I select an item in the parent combobox, automatically the first item in the child combobox is shown. Is it possible to set the selected index of the child combobox to -1 (or another solution)?

    Thanks in advance!

  32. #32

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,662

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Quote Originally Posted by bodylojohn View Post
    Dear JMC...

    I tried your code and it works perfect. Thanks for this.

    However I have one small question:

    When I select an item in the parent combobox, automatically the first item in the child combobox is shown. Is it possible to set the selected index of the child combobox to -1 (or another solution)?

    Thanks in advance!
    Data-binding always selects the first item by default. You can't stop that happening so you would have to manually set the SelectedIndex to -1 or the SelectedItem to Nothing after selecting the parent.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  33. #33
    Fanatic Member
    Join Date
    Jul 2006
    Location
    Netherlands
    Posts
    794

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Thank you for the quick reply JMC...

    I tried to put the code in:
    Code:
     Private Sub cboZoekApplicatie_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboZoekApplicatie.SelectedIndexChanged
        Me.cboModule.SelectedIndex = -1
      End Sub
    But with no success.

    Is there a specific location in the code?
    I tried several but with no success.

    Thanks again!!!

  34. #34

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,662

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    @bodylojohn, try handling the ListChanged event of the child BindingSource. That will work after each time the user selects a different parent. You'll have to also clear the child control manually after setting its DataSource at the start.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  35. #35
    New Member
    Join Date
    Apr 2012
    Posts
    1

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Quote Originally Posted by jmcilhinney View Post
    3. Add two BindingSources to the form.
    In VS2002, is there some process to reveal the BindingSources widget, so that it shows up in the ToolBox?
    Or some code equivalent?

    In any case, a really good article. I plan to bookmark it and come back to it when I'm worthy.
    Thank-you.

  36. #36

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,662

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Quote Originally Posted by Zubla View Post
    In VS2002, is there some process to reveal the BindingSources widget, so that it shows up in the ToolBox?
    Or some code equivalent?

    In any case, a really good article. I plan to bookmark it and come back to it when I'm worthy.
    Thank-you.
    The BindingSource component was not added until .NET 2.0, which means it is available in VS 2005 and later. Is there a specific reason that you're still using VS.NET 2002? There have been free Express editions available since VS 2005 so, unless you're using some feature in the old paid-for edition that is not available in the newer Express editions, I cannot recommend strongly enough that you upgrade. VS.NET 2002 can only target .NET 1.0 as well, which a lot of users won't have installed. Many users won't have newer .NET versions installed either but at least they will need those for more apps as time goes on, not fewer. You're also missing out on lots of new Framework, VB and VS features that have been added in the last 10 years.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  37. #37
    Member
    Join Date
    Nov 2010
    Posts
    36

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Jm, in my case im trying to set up 2 comboboxes... i have a table which has city and state all in one table but i.e i want to pull from combobox1 the states GROUPED and in Combobox2 The City that belongs to the state... what would be the best route?

  38. #38

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,662

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Quote Originally Posted by mannyf50 View Post
    Jm, in my case im trying to set up 2 comboboxes... i have a table which has city and state all in one table but i.e i want to pull from combobox1 the states GROUPED and in Combobox2 The City that belongs to the state... what would be the best route?
    You would simply query the same database table twice to populate two DataTables with the appropriate data. Everything else is exactly the same.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

  39. #39
    Addicted Member coolwater's Avatar
    Join Date
    Dec 2004
    Location
    philippines
    Posts
    210

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    @jmcilhinney I used your code above by using two datagridviews. It worked perfectly. Thanks.

    Question: I would like to put a refresh button to see if there are changes in the child (BindingSource2)? For the moment the only way to see if there are changes in BindingSource2 is to close and open the form.

    btw. The form is only used for viewing. No insert, updates and deletes. The insert, update, delete transactions are being done on another form. That is why I need a solution to refresh the child datagridview if there are changes.
    Last edited by coolwater; Jul 4th, 2014 at 01:10 AM.

  40. #40

    Thread Starter
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    88,662

    Re: Master/Detail (Parent/Child) Data-binding (.NET 2.0+ WinForms)

    Quote Originally Posted by coolwater View Post
    @jmcilhinney I used your code above by using two datagridviews. It worked perfectly. Thanks.

    Question: I would like to put a refresh button to see if there are changes in the child (BindingSource2)? For the moment the only way to see if there are changes in BindingSource2 is to close and open the form.

    btw. The form is only used for viewing. No insert, updates and deletes. The insert, update, delete transactions are being done on another form. That is why I need a solution to refresh the child datagridview if there are changes.
    This doesn't really have anything to do with the topic of this thread. I'm quite willing to help with the issue but it doesn't belong here. Start a new thread in the VB.NET forum and, if you like, PM me a link. We can address the problem there.

    2007-2014

    Why is my data not saved to my database? | MSDN Data Walkthroughs
    MSDN "How Do I?" Videos: VB | C#
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts) | WP8 Turnstile Feather Transition with Pivot Control
    Beginner Tutorials: VB | C# | SQL

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.