none
how to assign 2 tables (Parent,Child) to 2 datagridview programatically?,whats wrong with my code? RRS feed

  • Question

  • hello

    i am trying to bring to tables ETA,Containers (parent/child) to the form

    the problem is when is use inner join it brings 1 table contains both tables so i can put them in 2 different table

    and when i use 2 dataadapter to bring each table alone it works and each table is assigned to a datagrid view but there is no relation between them(when i choose an item from the parent table the child table should only show me the related items only)

    this is my code

      Dim accessconnection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=e:\global.mdb")
            accessconnection.Open()
            Dim etaadapter As New OleDbDataAdapter("Select * FROM ETA", accessconnection)
            Dim containersadapter As New OleDbDataAdapter("SELECT containers.rel,CONTAINERS.CONTAINERS, CONTAINERS.PALLETS, CONTAINERS.CARTON
    FROM ETA INNER JOIN CONTAINERS ON ETA.ID = CONTAINERS.REL", accessconnection)
            Dim etadataset As New DataSet
            Dim a As New DataTable
            Dim b As New DataTable
            etaadapter.Fill(etadataset, "ETA")
            containersadapter.Fill(etadataset, "Containers")
            a = etadataset.Tables("ETA")
            b = etadataset.Tables("containers")
            grdETA.DataSource = a
            grdContainers.DataSource = b


    I am A Medical Doctor

    Monday, August 21, 2017 3:23 PM

Answers

  • You need to create a relationship between the two DataTables, and you also need to use that relationship to set up BindingSources to use as your grid DataSources.

    To create the relationship:

    etadataset.Relations.Add("MyRelation", a.Columns("ID"), b.Columns("REL")

    And set up the BindingSources:

    ' I have the VB syntax wrong, I can never remember it (different than C#)
    BindingSource bsParent = new BindingSource()
    BindingSource bsChild = new BindingSource()
    bsParent.DataSource = etadataset
    bsParent.DataMember = "ETA"
    bsChild.DataSource = bsParent
    bsChild.DataMember = "MyRelation"

    And, lastly the two grids:

    grdETA.DataSource = bsParent
    grdContainers.DataSource = bsChild
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, August 22, 2017 1:12 AM
  • New questions should be asked in a new thread, but I'll take a stab it here anyway.

    I don't ever use the .Update() method anyway (so I won't be much help with that particular problem) ... I do it the other way you mentioned, updating each row one at a time with .ExecuteNonQuery(). The .Update() does that behind the scenes anyway, plus the .Update() makes an extra round-trip to the database. My advice is to stick with the .ExecuteNonQuery().

    You might also have a problem with uncommitted changes to your child rows that can happen frequently in this kind of scenario (which *may* be what caused problems with the .Update() method, but I don't know for sure). I wrote a blog post that covers this scenario and what to do about it:

    http://geek-goddess-bonnie.blogspot.com/2010/03/uncommitted-child-table-changes.html


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, August 22, 2017 2:39 PM

  • First, let me apologize for my use of C#. My blog posts are all C# too. Hopefully you can figure out how it should be in VB.

    So, check out my 3-part series on Data Access for some basic ideas. I'm using a SQL database, but the same would apply to other databases (except you'd use OleDb classes instead of Sql classes):

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

    Each post adds extra complexity to the Data Access classes, but more flexibility. The first post is enough to get you going in the right direction and give you a general idea of the concept, but the second post is more useful. The third post has a more complete example class, but it gets into using anonymous delegates and may be too much for a beginner (which, to be honest, I don't use the anonymous delegates anymore, but I used to on previous projects several years ago). However, even if you don't want to use the anonymous delegate approach, the more complete example class in the third post should show you how to save data, which the examples in the first two posts don't do.

    Those 3 posts are old, I wrote them back in 2009. They are still relevant, but I needed to add two things: implementing IDisposable and using TransactionScope for transactions. So, I added this post to my Data Access series:

    Several of my readers have gotten back to me with some questions about this code and I realized that I'm still missing a few important items, such as DataAdapter.TableMappings!! So, I finally wrote a new post about that:


    I hope this helps you a little bit.  =0)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, August 22, 2017 8:06 PM

All replies

  • You need to create a relationship between the two DataTables, and you also need to use that relationship to set up BindingSources to use as your grid DataSources.

    To create the relationship:

    etadataset.Relations.Add("MyRelation", a.Columns("ID"), b.Columns("REL")

    And set up the BindingSources:

    ' I have the VB syntax wrong, I can never remember it (different than C#)
    BindingSource bsParent = new BindingSource()
    BindingSource bsChild = new BindingSource()
    bsParent.DataSource = etadataset
    bsParent.DataMember = "ETA"
    bsChild.DataSource = bsParent
    bsChild.DataMember = "MyRelation"

    And, lastly the two grids:

    grdETA.DataSource = bsParent
    grdContainers.DataSource = bsChild
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, August 22, 2017 1:12 AM
  • what about the batch update

    i let the user do all the changes in both tables then he press an update button so it updates all the changes at once

    i tried the command builder  and dataadapter.update feature but i always get error

    till i start to extract the changes in the tables using the getchange(added/modified/deleted) and do each one seperately using the excutenonquery method

    i belive there is an easier solution than this ryt

    this is the old method that i get errors with

            'n = New OleDbCommandBuilder(etaadapter)
            'o = New OleDbCommandBuilder(containersadapter)
            'etaadapter.UpdateCommand = n.GetUpdateCommand
            'etaadapter.DeleteCommand = n.GetDeleteCommand
            'etaadapter.InsertCommand = n.GetInsertCommand
            'containersadapter.UpdateCommand = o.GetUpdateCommand
            'containersadapter.DeleteCommand = o.GetDeleteCommand
            'containersadapter.InsertCommand = o.GetInsertCommand
            'etaadapter.Update(etadataset.Tables("eta"))     
       'containersadapter.Update(etadataset.Tables("containers"))

    of course it is in a comment cause i used the other method

    this is the error message and it occurs when it perform this code

      'etaadapter.Update(etadataset.Tables("eta")) 

    Syntax error (missing operator) in query expression '((ID = ?) AND ((? = 1 AND DATE IS NULL) OR (DATE = ?)) AND ((? = 1 AND CT IS NULL) OR (CT = ?)) AND ((? = 1 AND CONTENTS IS NULL) OR (CONTENTS = ?)) AND ((? = 1 AND VESSEL IS NULL) OR (VESSEL = ?)) AND ((? = 1 AND BL IS NULL) OR (BL = ?)) AND ((? = 1 AND '.

    thanks for your reply


    I am A Medical Doctor

    Tuesday, August 22, 2017 1:48 PM
  • New questions should be asked in a new thread, but I'll take a stab it here anyway.

    I don't ever use the .Update() method anyway (so I won't be much help with that particular problem) ... I do it the other way you mentioned, updating each row one at a time with .ExecuteNonQuery(). The .Update() does that behind the scenes anyway, plus the .Update() makes an extra round-trip to the database. My advice is to stick with the .ExecuteNonQuery().

    You might also have a problem with uncommitted changes to your child rows that can happen frequently in this kind of scenario (which *may* be what caused problems with the .Update() method, but I don't know for sure). I wrote a blog post that covers this scenario and what to do about it:

    http://geek-goddess-bonnie.blogspot.com/2010/03/uncommitted-child-table-changes.html


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, August 22, 2017 2:39 PM
  • the problem with executenonquery is for table with many columns,writing its sql command three times for insert,update and delete seems exhausting

    unless there is a sql code generator it would sounds lovely

    thanks for you reply


    I am A Medical Doctor

    Tuesday, August 22, 2017 6:13 PM
  • I'll give you some links to my DataAccess blog posts in a bit (after lunch, I'm just about to sit down and eat it), but here's something helpful for adding parameters automatically:

    protected void SetAllParameters(DataRow Row)
    {
        this.ClearParameters();
        for (int i = 0; i < Row.Table.Columns.Count; i++)
        {
            this.AddParms(Row.Table.Columns[i].ColumnName, Row[i]);
        }
    }

    This is part of my DataAccess class (which is why it has this.Methods in it), but it might give you some ideas how to make setting the parameters a whole lot easier!

    Links to (and a brief explanation of) my DataAccess blog posts coming after lunch ...


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, August 22, 2017 7:04 PM

  • First, let me apologize for my use of C#. My blog posts are all C# too. Hopefully you can figure out how it should be in VB.

    So, check out my 3-part series on Data Access for some basic ideas. I'm using a SQL database, but the same would apply to other databases (except you'd use OleDb classes instead of Sql classes):

    http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
    http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

    Each post adds extra complexity to the Data Access classes, but more flexibility. The first post is enough to get you going in the right direction and give you a general idea of the concept, but the second post is more useful. The third post has a more complete example class, but it gets into using anonymous delegates and may be too much for a beginner (which, to be honest, I don't use the anonymous delegates anymore, but I used to on previous projects several years ago). However, even if you don't want to use the anonymous delegate approach, the more complete example class in the third post should show you how to save data, which the examples in the first two posts don't do.

    Those 3 posts are old, I wrote them back in 2009. They are still relevant, but I needed to add two things: implementing IDisposable and using TransactionScope for transactions. So, I added this post to my Data Access series:

    Several of my readers have gotten back to me with some questions about this code and I realized that I'm still missing a few important items, such as DataAdapter.TableMappings!! So, I finally wrote a new post about that:


    I hope this helps you a little bit.  =0)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, August 22, 2017 8:06 PM