Weird interaction between Column AutoSort and DataView Sort

Posted by: j2associates on 8 September 2017, 12:48 pm EST

  • Posted 8 September 2017, 12:48 pm EST

    Hello all,

    Attached is a sample project that demonstrates some weird behavior when allowing the user to sort the data while also sorting the data programmatically. The form has column 1 set to AutoSort and also has a button to sort the data programmatically using GetDataView.

    To get the weird behavior, do the following:
        A. Open the Form
        B. Click the Sort Button immediately - Note that data is not sorted properly
        C. If B above doesn't work, Open the Form and click in one of the Numeric column 
             headers and then Click the Sort Button - Note that data is not sorted properly
        D. Click the Sort Indicator in Column 1 to sort the data one or more times
        E. Once the Data has been sorted at least once by clicking the Sort Indicator
           it will sort correctly from that point forward regardless of whether you
           Click the Sort Indicator on column 1 or Click the Sort Button


  • Replied 8 September 2017, 12:48 pm EST

    Hello Scott,

    Thanks for the reply! Do I put your sample code before the .GetDataView method or somewhere else?

  • Replied 8 September 2017, 12:48 pm EST


    You would put the code before the call to .GetDataView, so the DataView you get is not the sorted view.

  • Replied 8 September 2017, 12:48 pm EST


    Can someone explain what the above code is doing?

    The behavior I initially posted about is consistent with defining a DataView based on a DataTable. If I put a DataGridView and FpSpread side by side on a form with a DataView as the DataSource, both exhibit the odd unsorted behavior. If I set the Sort property of the DataView before making the DataView the DataSource, the DataGridView appears sorted correctly while the FpSpread still appears unsorted.

    Is this a bug? It seems to me that binding an FpSpread to a sorted DataView should pick up the sort similarly to the way that the DataGridView does. Needing to know that additional code is required to make an FpSpread sort properly is not intuitive. Thanks in advance for any ideas and/or suggestions!


  • Replied 8 September 2017, 12:48 pm EST


    I will try to explain this again. Try to think of the data being stored at different levels. You have your DataSource (in your case it is the DataTable object) that stores the data in certain rows. Then, you have a DataView, which is a view of that data. You can sort the rows in the DataView but the underlying DataTable will remain the same row indices. Then, when you bind to the Spread, the data from the DataView are bound to the DataModel of Spread. This 'layer' of the data should mirror the DataView. Then, you have the SheetView's View. Data at this 'layer' can be sorted such the indices of the rows are different from the DataModel/DataView, which could be different from the bound DataSource (DataTable).

    So what is happening is you are binding the Spread to an unsorted DataTable. Then you are sorting the Spread's View (this is causing the 1st row from the DataSource DataTable, which is also the 1st row in the DataView and DataModel to be map to the 2nd row in the Spread View). Then, you are getting the DataView from Spread and sorting this View. Now the 2nd row from the DataView becomes the first row. This is reflected in the DataModel, but since there is already a mapping from row 1 to row 2 between the DataModel and the Spread View, you see the data from the 2nd row of the DataTable (Employee = 9) show up as the second row in the new sort instead where you expect it be as the first row.

    The code I sent you takes away all the mappings of row indices between the DataModel and Spread's View, so you do not see the behavior as stated above and instead it works for you. You could, instead catch the AutoSortingColumn event and set the Cancel parameter to True. This will keep Spread from sorting the data automatically. Then you can write code in this event to preform your own sort (basically the code you have in your Command_Click) event.

  • Replied 8 September 2017, 12:48 pm EST


    You need to reset the indices before getting the DataView from the Spread control. Here is code to add before getting the DataField in you programatic sort.

          For i As Integer = 0 To .RowCount - 1
    .Models.SetViewRowIndex(i, i)
  • Replied 8 September 2017, 12:48 pm EST

    Scott, please accept my apologies. We always use DataViews for DataSources so that we can do things like filter and sort. How I missed that in my example and initially bound to a DataTable instead of a DataView is embarrassing. I was either up too late or too early, one or the other. Once I bound the spread to a DataView it worked just fine from the get go.
Need extra support?

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

Learn More

Forum Channels