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


    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









  • Replied 8 September 2017, 12:28 pm EST


    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.

    	<SPAN class=kwd>Private Sub</SPAN> Form1_Load(<SPAN class=kwd>ByVal</SPAN> sender <SPAN class=kwd>As</SPAN> System.<SPAN class=kwd>Object</SPAN>, <SPAN class=kwd>ByVal</SPAN> e <SPAN class=kwd>As</SPAN> System.EventArgs) <SPAN class=kwd>Handles MyBase</SPAN>.Load
    <SPAN class=kwd>Dim</SPAN> ds <SPAN class=kwd>As New</SPAN> DataSet
    <SPAN class=kwd>Dim</SPAN> dt1 <SPAN class=kwd>As New</SPAN> DataTable(<SPAN class=st>"Table1"</SPAN>)
    <SPAN class=kwd>Dim</SPAN> dt2 <SPAN class=kwd>As New</SPAN> DataTable(<SPAN class=st>"Table2"</SPAN>)
    <SPAN class=kwd>Dim</SPAN> dt3 <SPAN class=kwd>As New</SPAN> DataTable(<SPAN class=st>"Table3"</SPAN>)
    <SPAN class=kwd>Dim</SPAN> dr <SPAN class=kwd>As</SPAN> DataRow

    dt1.Columns.AddRange(<SPAN class=kwd>New</SPAN> DataColumn() {<SPAN class=kwd>New</SPAN> DataColumn(<SPAN class=st>"P1"</SPAN>), <SPAN class=kwd>New</SPAN> DataColumn(<SPAN class=st>"P2"</SPAN>), <SPAN class=kwd>New</SPAN> DataColumn(<SPAN class=st>"Title"</SPAN>)})
    dt2.Columns.AddRange(<SPAN class=kwd>New</SPAN> DataColumn() {<SPAN class=kwd>New</SPAN> DataColumn(<SPAN class=st>"C1"</SPAN>), <SPAN class=kwd>New</SPAN> DataColumn(<SPAN class=st>"Title"</SPAN>)})
    dt3.Columns.AddRange(<SPAN class=kwd>New</SPAN> DataColumn() {<SPAN class=kwd>New</SPAN> DataColumn(<SPAN class=st>"C2"</SPAN>), <SPAN class=kwd>New</SPAN> DataColumn(<SPAN class=st>"Title"</SPAN>)})
    <SPAN class=kwd>For</SPAN> i <SPAN class=kwd>As Integer</SPAN> = 0 <SPAN class=kwd>To</SPAN> 9
    dr = dt1.NewRow
    dr(0) = i
    dr(1) = i + 100
    dr(2) = <SPAN class=st>"Test "</SPAN> & i
    <SPAN class=kwd>Next

    For</SPAN> i <SPAN class=kwd>As Integer</SPAN> = 0 <SPAN class=kwd>To</SPAN> 49
    dr = dt2.NewRow
    dr(0) = Int(10 * Rnd())
    dr(1) = <SPAN class=st>"Test Child 1 - "</SPAN> & i
    <SPAN class=kwd>Next

    For</SPAN> i <SPAN class=kwd>As Integer</SPAN> = 0 <SPAN class=kwd>To</SPAN> 49
    dr = dt3.NewRow
    dr(0) = <SPAN class=kwd>CInt</SPAN>(Int(10 * Rnd()) + 100)
    dr(1) = <SPAN class=st>"Test Child 2 - "</SPAN> & i
    <SPAN class=kwd>Next</SPAN>

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

    Private Sub</SPAN> FpSpread1_CellClick(<SPAN class=kwd>ByVal</SPAN> sender <SPAN class=kwd>As Object</SPAN>, <SPAN class=kwd>ByVal</SPAN> e <SPAN class=kwd>As</SPAN> FarPoint.Win.Spread.CellClickEventArgs) <SPAN class=kwd>Handles</SPAN> FpSpread1.CellClick
    <SPAN class=kwd>If</SPAN> e.View.Sheets(0).ParentRelationName = <SPAN class=st>"relB"</SPAN> <SPAN class=kwd>And Not</SPAN> (e.View.Sheets(0).Parent <SPAN class=kwd>Is Nothing</SPAN>) <SPAN class=kwd>Then
    Dim</SPAN> dr <SPAN class=kwd>As</SPAN> DataRow = <SPAN class=kwd>CType</SPAN>(<SPAN class=kwd>CType</SPAN>(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))

    <SPAN class=kwd>End If
    End Sub</SPAN>
     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


    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:

    <span class="kwd">Dim</span> dr <span class="kwd">As</span> DataRow = <span class="kwd">CType</span>(<span class="kwd">CType</span>(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))</pre>
    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


    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