using a bindingsource with child/parent dataset with farpoint grid issues

Posted by: smithabc1212 on 8 September 2017, 12:28 pm EST

  • Posted 8 September 2017, 12:28 pm EST

    Hi,


    I have a BindingSource whose datasource is a dataset that contains two datatables with a child parent relationship.


    On my Farpoint grid, I set it's datasource to the BindingSource mentioned above. There are two issues I am having


    1) Getting the underlying child DataRow from the bindingsource when the user clicks the respective child row.


    2) When sorting the bindingsource, the child rows do not go along with their parent.


    For example 1parent


                            1a child


                            2a child


                      2 parent


    now sort the binding source and expand the first row again, which is now 2 parent


                   2 parent


                      1a child


                      2a child


                   1 parent


     


    I am interested in what are the best ways at approaching both these problems


     


    Thanks


                      


                   


     


     


     


     

  • Replied 8 September 2017, 12:28 pm EST

    Hello,


    1) Look at the code in the Spread's CellClick event below for code to get the child DataRow from the CellClick event of Spread.

     
    	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim ds As New DataSet
    Dim dt1 As New DataTable("Table1")
    Dim dt2 As New DataTable("Table2")
    Dim dt3 As New DataTable("Table3")
    Dim dr As DataRow

    Randomize()
    dt1.Columns.AddRange(New DataColumn() {New DataColumn("P1"), New DataColumn("P2"), New DataColumn("Title")})
    dt2.Columns.AddRange(New DataColumn() {New DataColumn("C1"), New DataColumn("Title")})
    dt3.Columns.AddRange(New DataColumn() {New DataColumn("C2"), New DataColumn("Title")})
    For i As Integer = 0 To 9
    dr = dt1.NewRow
    dr(0) = i
    dr(1) = i + 100
    dr(2) = "Test " & i
    dt1.Rows.Add(dr)
    Next

    For
    i As Integer = 0 To 49
    dr = dt2.NewRow
    dr(0) = Int(10 * Rnd())
    dr(1) = "Test Child 1 - " & i
    dt2.Rows.Add(dr)
    Next

    For
    i As Integer = 0 To 49
    dr = dt3.NewRow
    dr(0) = CInt(Int(10 * Rnd()) + 100)
    dr(1) = "Test Child 2 - " & i
    dt3.Rows.Add(dr)
    Next

    ds.Tables.AddRange(New DataTable() {dt1, dt2, dt3})
    ds.Relations.Add("relA", dt1.Columns("P1"), dt2.Columns("C1"))
    ds.Relations.Add("relB", dt1.Columns("P2"), dt3.Columns("C2"))
    FpSpread1.Sheets(0).DataSource = ds
    End Sub

    Private Sub
    FpSpread1_CellClick(ByVal sender As Object, ByVal e As FarPoint.Win.Spread.CellClickEventArgs) Handles FpSpread1.CellClick
    If e.View.Sheets(0).ParentRelationName = "relB" And Not (e.View.Sheets(0).Parent Is Nothing) Then
    Dim
    dr As DataRow = CType(CType(FpSpread1.Sheets(0).DataSource, DataSet).Tables(0).Rows(e.View.Sheets(0).ParentRowIndex).GetChildRows(e.View.Sheets(0).ParentRelationName), DataRow())(e.View.Sheets(0).ActiveRowIndex))

    End If
    End Sub
     2) What code are you using to sort your data? I would suggest sorting the Spread control and then it should work. If you sort the DataSource, you may have to reset the DataSource property on the Spread control.
  • Replied 8 September 2017, 12:28 pm EST

    I use a BindingSource as the datasource for my sheet, and I also sort by the binding source


    I don't have actual code with me now, but hopefully this will help


     


    BindingSource b = new BindingSource();


    b.DataSource = MyDataSet; // data set with 2 tables(parent child relationship)


    MyFarPointGrid.DataSource = b;


    FarPointSheetSelectionChangedEvent(args e)


    {


    /* from DataRowView can easily get parent DataRow by casting the datarowview to a datarow, BindingSource's position(b.Position) is updated to equal e.Row */


    DataRowView drv = b.current;


    // but I also want to get the child datarow or DataRowView from the BindingSource, when a child row is selected


    }


    /* to sort, if I sorted by using farpoint, the binding source does not sort and the order of the items on the farpoint sheet and what is in the binding source would be out of sink, and make it more difficult to get the current selected DataRow from the BindingSource, plus the BindingSource's Sort is much quicker than farpoints */


    b.Sort("ColumnName" asc);


    /*However, it seems that if a row was expanded already, the expanded ChildSheet is saved by the grid, and the previously expanded childsheet will be displayed on the same row in farpoint even though the parent changed*/

  • Replied 8 September 2017, 12:28 pm EST

    Hello,


    This is correct, that you would need to reset the DataSource on the Spread control after sorting the BindingSource.


    You should be able to use code like i gave you to get the child DataRow object clicked.

  • Replied 8 September 2017, 12:28 pm EST

    I'm happy to say the CellClick code you offered (post 44483) did show me the light  for a similar question.

    But it gives rise to another question as I try to wrap my brain around FPSpread, trying to develop some self-sufficiency...

     How would a newbie best familiarize him/herself to be able to divine the necessity and construction of the line:

    Dim dr As DataRow = CType(CType(FpSpread1.Sheets(0).DataSource, DataSet).Tables(0).Rows(e.View.Sheets(0).ParentRowIndex).GetChildRows(e.View.Sheets(0).ParentRelationName), DataRow())(e.View.Sheets(0).ActiveRowIndex))
    It just seems that such a thing, followed by use of one of the row elements, ie: msgbox(dr.item("FieldName"), would be a normal course of business in using FPSpread, but is there a central document that covers such "standard" matters? 
     

     

  • Replied 8 September 2017, 12:28 pm EST

    Hello,


    There is not any such document other than the Users Guide that ships in the online help file. But code like above is pretty advanced code and not something you coul dgather from the Users Guide. We do have a company that has produced and offers FarPoint Spread training. I can give you their contact information if you are interested in Spread training.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels