none
How to Use a DataRelation between DataTables in a DataSet RRS feed

  • Question

  • After Creating A Dataset

    And Add to it 5 Datatables

    Like this

      DSsRpt.Tables.Add(DT0)
            DSsRpt.Tables.Add(DT1)
            DSsRpt.Tables.Add(DT2)
                DSsRpt.Relations.Add("Id1", DT0.Columns("Id"), DT1.Columns("Id"), False)
            DSsRpt.Relations.Add("ID2", DT0.Columns("ID"), DT2.Columns("ID"), False)
           
     now i tryed to use the dataset 

    Like this

        msgbox(DSsRpt.Tables(0).Rows(0).Item("ID").ToString)
                msgbox(DSsRpt.Tables(1).Rows(0).Item("ID").ToString)
                msgbox(DSsRpt.Tables(2).Rows(0).Item("ID").ToString)

    it is not the same

    i tryed like this

    msgbox(DSsRpt.Tables("Id1").Rows(0).Item("ID").ToString)  this makes a erorr 

    then how i have to use the dataset

    Thanks in advence

     

    Wednesday, June 12, 2019 8:20 PM

All replies

  • If you were expecting to get the related rows simply because a datarelation exists, then no, it doesn't work like that. You need to explicitly follow the datarelation by means of a call to GetChildRows:

    https://docs.microsoft.com/en-us/dotnet/api/system.data.datarow.getchildrows?view=netframework-4.8

    For example,

    MessageBox.Show(DSsRpt.Tables[0].Rows[0].GetChildRows("Id1")[0].Item["ID"].ToString());

    Note that since you asked in the C# forum I used the C# syntax instead of the VB syntax that you were using in your own examples.


    Thursday, June 13, 2019 6:33 AM
  • Hi MosheS,

    Thank you for posting here.

    Based on your description, you want to know how to use DataRelation between DataTables in a DataSet.

    You could look at the following link.

    https://stackoverflow.com/questions/12955766/how-do-i-define-a-datarelation-between-a-datasets-datatables-on-multiple-column

    Besides, If you want to show the datatable value, you could use the following code:

    Console.WriteLine(dataSet.Tables[0].Rows[0]["ID"].ToString());

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, June 13, 2019 7:33 AM
  • Hello,

    This shows how to get child rows.

    Dim rows = ds.Tables("Customers").Rows(3).GetChildRows("CustomersOrders")
    For Each row As DataRow In rows
        Console.WriteLine($"{row.Field(Of Integer)("Identifier")},{row.Field(Of Date)("OrderDate")} ")
    Next
    To put the above in context see the following data class on GitHub, if you were to place the code under this line it should make sense.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, June 13, 2019 9:52 AM
    Moderator
  • Thanks For Your All 

    But In Vb.net it dont work well

    DSsRpt.Tables(0).Rows(0).GetChildRows("D1")(0).Item("ID").ToString

    is making a error 

    System.IndexOutOfRangeException: 'Index was outside the bounds of the array.'

    i didnt found a a working sample for vb.net 

    Thursday, June 13, 2019 3:02 PM
  • Usually when using GetChildRows a DataSet is populated, relations set and the tables in the DataSet get assigned to BindingSource components. 

    Thinking master/details using two DataGridView controls where is DataGridView DataSource is set to a BindingSource where the BindingSource has been setup correctly when moving row to row in the master DataGridView that is when you access child rows.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, June 13, 2019 3:24 PM
    Moderator
  • DSsRpt.Tables(0).Rows(0).GetChildRows("D1")(0).Item("ID").ToString

    System.IndexOutOfRangeException: 'Index was outside the bounds of the array.'

    This error means that one of the values in the statement is not correct. Presuming that the Table number 0 exists and the datarelation is indeed called "D1" (note that in your first example it was called "Id1"), and that the "ID" field exists, then the error is in one of the (0). The first Rows(0) will throw an error if the Table(0) does not have any rows. The second (0) will throw an error if the first row of the table does not have any child rows as determined by the datarelation.

    Obviously, the single line containing all the code at once was only an example. In a real program, you have to do it step by step and add adequate "If" statements to determine if there are any rows, and you should not try to access any rows that do not exist.

    Thursday, June 13, 2019 3:42 PM
  • This error means that one of the values in the statement is not correct. Presuming that the Table number 0 exists and the datarelation is indeed called "D1" (note that in your first example it was called "Id1"), and that the "ID" field exists, then the error is in one of the (0). The first Rows(0) will throw an error if the Table(0) does not have any rows. The second (0) will throw an error if the first row of the table does not have any child rows as determined by the datarelation.

    Thanks for your reply

    i tryed to do    DSsRpt.Tables(0).GetChildRows("DT1")(0).Item("ID").ToString

    The error is

    "GetChildRows" is not a member of "dataTable"

    then i tryed to do  DSsRpt.GetChildRows("DT1")(0).Item("ID").ToString

    The error is

    "GetChildRows" is not a member of "dataSet"

    i looked around i dint see any help for  "GetChildRows" for vb.net 

    Microsoft is only providing sample for "C"

    now i am STUK with my work


    Thursday, June 13, 2019 4:32 PM
  • Microsoft is only providing sample for "C"

    Well, they do have C# to VB.NET code converters. Maybe you should consider using one that's online using a browser, becuase VB.NET code examples are far and in between.   

    Thursday, June 13, 2019 4:43 PM
  • This error means that one of the values in the statement is not correct. Presuming that the Table number 0 exists and the datarelation is indeed called "D1" (note that in your first example it was called "Id1"), and that the "ID" field exists, then the error is in one of the (0). The first Rows(0) will throw an error if the Table(0) does not have any rows. The second (0) will throw an error if the first row of the table does not have any child rows as determined by the datarelation.

    Thanks for your reply

    i tryed to do    DSsRpt.Tables(0).GetChildRows("DT1")(0).Item("ID").ToString

    The error is

    "GetChildRows" is not a member of "dataTable"

    then i tryed to do  DSsRpt.GetChildRows("DT1")(0).Item("ID").ToString

    The error is

    "GetChildRows" is not a member of "dataSet"

    i looked around i dint see any help for  "GetChildRows" for vb.net 

    Microsoft is only providing sample for "C"

    now i am STUK with my work


    Microsoft does have a VB.NET page.

    https://docs.microsoft.com/en-us/dotnet/api/system.data.datarow.getchildrows?view=netframework-4.8

    There is a link that might say C#, click it and pick VB.NET


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, June 13, 2019 4:53 PM
    Moderator
  • Thanks A milion
    Thursday, June 13, 2019 5:36 PM
  • Hi,
    to unterstand try this console demo:

    Module Module17
      Sub Main()
        Try
          With New Demo
            .Execute()
          End With
        Catch ex As Exception
          Console.WriteLine(ex.ToString)
        End Try
        Console.WriteLine("Continue enter key")
        Console.ReadKey()
      End Sub
    
      Public Class Demo
    
        Public Sub Execute()
          ' declare new DataSet
          Dim DSsRpt As New DataSet
    
          ' declare new Master table
          Dim DT0 As New DataTable
          ' add columns
          With DT0
            With .Columns
              .Add("Id", GetType(Integer)) ' Key for Master
              .Add("MasterInfo", GetType(String))
            End With
          End With
    
          ' declare new Child table
          Dim DT1 As New DataTable
          ' add columns
          With DT1
            With .Columns
              .Add("Id", GetType(Integer)) ' foreign-key to Master
              .Add("ChildInfo", GetType(String))
            End With
          End With
    
          ' add tables
          DSsRpt.Tables.Add(DT0)
          DSsRpt.Tables.Add(DT1)
          ' add Relation
          DSsRpt.Relations.Add("Id1", DT0.Columns("Id"), DT1.Columns("Id"), False)
    
          ' fill Data
          For i = 1 To 3
            DT0.Rows.Add(i, $"Master {i}")
            For k = 1 To 3
              DT1.Rows.Add(i, $"Child {i} {k}")
            Next
          Next
    
          ' iterate thru data
          For Each masterRow As DataRow In DSsRpt.Tables(0).Rows
            ' master data
            Console.WriteLine($" {masterRow.Field(Of Integer)("ID")}, {masterRow.Field(Of String)("MasterInfo")} ")
            For Each childRow In masterRow.GetChildRows("Id1")
              ' child data
              Console.WriteLine($"   {childRow.Field(Of String)("ChildInfo")} ")
            Next
          Next
    
        End Sub
      End Class
    End Module


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Thursday, June 13, 2019 6:32 PM
  • i tryed to do    DSsRpt.Tables(0).GetChildRows("DT1")(0).Item("ID").ToString

    The error is

    "GetChildRows" is not a member of "dataTable"

    Indeed, GetChildRow is not a member of DataTable. It is a member of the DataRow.

    So, in order to use GetChildRows, you first need to separate a row out of the DataTable, and then call GetChildRows on that Row. Of course, taking a row of the datatable will fail if the datatable does not have any rows.

    Dim dr as DataRow = DsRpt.Tables(0).Rows(0) 'First ensure that the datatable has at least one row, otherwise you can´t take rows(0)

    Dim childrows = dr.GetChildRows("Id1") 'Now childrows contains all the child rows in accordance with datareation Id1

    Thursday, June 13, 2019 7:09 PM