Sorting multiple columns

Posted by: lg_sony_27 on 8 September 2017, 1:22 pm EST

  • Posted 8 September 2017, 1:22 pm EST

    Hi! How do i sort multiple columns? for example Columns A,B,C,D has the following LastName, FirstName MiddleName, Gender. I would like to separate the ladies from gentlemen but the lastname is arranged alphabetically ascending and the gender - descending.


     Btw, the gender column is a hidden column.


     

  • Replied 8 September 2017, 1:22 pm EST

    Hi! 


    I want to do this programmatically. I accessed the whole table and display it in spread with some columns hidded. Is there a way to sort column A (LastName as secondary key, sort Ascending) and column D (Gender as primary key, sort Descending).  I dont like the user to click the header to sort the column, I want it sorted automatically when i display the records.


    Any help please?

  • Replied 8 September 2017, 1:22 pm EST

    Hello,


    You can use the SortRows method to implement this.

    <FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>Dim</FONT></FONT><FONT size=2> si() </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>As</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo() {</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(3, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>False</FONT></FONT><FONT size=2>), </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>New</FONT></FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(0, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>True</FONT></FONT><FONT size=2>)}


    FpSpread1.Sheets(0).SortRows(0, FpSpread1.Sheets(0).RowCount, si)

    </FONT>
  • Replied 8 September 2017, 1:22 pm EST

    Hello scotts,


    Actually, when I did a searched on sorting I've seen your post and I've tried it and it works but...something is not right with my output. I have a list of names, and I am sorting records alphabetically using Gender column as my primary key in Descending order and Lastname column as my secondary key in Ascending order. I am confused why is it that there are name which are not sorted right. I want all the Male first followed by all Female but there are some Male students still displayed after Female.


    I have 700 plus columns and i put these in datatable. I'll just state here the first few columns (Fields in my table) in order.


    Column A -->Ukey


    Column B -->IDNo


    Column C -->LastName


    Column D -->FirstName


    Column E -->MiddleName


    Column F-->Gender


    A,B and F are hidden columns in the spread. In my understanding (correct me if I'm wrong) your code sorts the spread using Column A and Column B as basis?


    here is the part of the code i have right now

    <FONT size=2>

    </FONT><FONT color=#0000ff size=2>Private</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>Sub</FONT><FONT size=2> btnDisplay_Click(</FONT><FONT color=#0000ff size=2>ByVal</FONT><FONT size=2> sender </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> System.Object, </FONT><FONT color=#0000ff size=2>ByVal</FONT><FONT size=2> e </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> System.EventArgs) </FONT><FONT color=#0000ff size=2>Handles</FONT><FONT size=2> btnDisplay.Click


    </FONT><FONT size=2>SuspendLayout()


    cnn.ConnectionString = </FONT><FONT color=#800000 size=2>"Data Source=.\sqlexpress;Initial Catalog=NDDU-IBED;Integrated Security=True"

    </FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2>If</FONT><FONT size=2> cnn.State = ConnectionState.Closed </FONT><FONT color=#0000ff size=2>Then</FONT><FONT size=2> cnn.Open()


    cmdGrade = cnn.CreateCommand


    cmdGrade.CommandText = </FONT><FONT color=#800000 size=2>"SELECT DISTINCT * FROM tblEnrol WHERE SectionName = '"</FONT><FONT size=2> & cboSection.Text & </FONT><FONT color=#800000 size=2>"' AND SubjectName = '"</FONT><FONT size=2> & cboSubjectName.Text & </FONT><FONT color=#800000 size=2>"' AND YearLevel = '"</FONT><FONT size=2> & cboYearLevel.Text & </FONT><FONT color=#800000 size=2>"'"

    </FONT><FONT size=2>

    daGrade.MissingSchemaAction = MissingSchemaAction.AddWithKey


    dsGrade.Clear()


    dsSection.Clear()


    dsSubject.Clear()


    daGrade.SelectCommand = cmdGrade


    daGrade.Fill(dsGrade, </FONT><FONT color=#800000 size=2>"tblEnrol"</FONT><FONT size=2>)


    cnn.Close()


    </FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> si() </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> FarPoint.Win.Spread.SortInfo = </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> FarPoint.Win.Spread.SortInfo() {</FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(1, </FONT><FONT color=#0000ff size=2>True</FONT><FONT size=2>), </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(2, </FONT><FONT color=#0000ff size=2>True</FONT><FONT size=2>)}


    </FONT><FONT color=#008000 size=2>''HideSheets()

    </FONT><FONT size=2>

    </FONT><FONT color=#008000 size=2>'bind FpSpread2 to datasource

    </FONT><FONT size=2>

    </FONT><FONT color=#008000 size=2>'1st Grading

    </FONT><FONT size=2>

    FpSpread2.Sheets(0).DataSource = dsGrade


    FpSpread2.Sheets(0).DataMember = </FONT><FONT color=#800000 size=2>"tblEnrol"

    </FONT><FONT size=2>

    </FONT><FONT color=#008000 size=2>'Create CellType and specify number decimal for 1st grading sheet

    </FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2>With</FONT><FONT size=2> FpSpread2.Sheets(0)


    .DataAutoCellTypes = </FONT><FONT color=#0000ff size=2>False

    </FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2>Dim</FONT><FONT size=2> num </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> FarPoint.Win.Spread.CellType.NumberCellType


    num.DecimalPlaces = 0


    .Columns(12, 81).CellType = num </FONT><FONT color=#008000 size=2>'column 12 to 81

    </FONT><FONT size=2>

    .Columns(82).CellType = num


    .Columns(86).CellType = num


    .Columns(90).CellType = num


    .Columns(94).CellType = num


    .Columns(98).CellType = num


    .Columns(102).CellType = num


    .Columns(106).CellType = num


    .Columns(84, 85).CellType = num


    .Columns(88, 89).CellType = num


    .Columns(92, 93).CellType = num


    .Columns(96, 97).CellType = num


    .Columns(100, 101).CellType = num


    .Columns(104, 105).CellType = num


    .Columns(109).CellType = num


    </FONT><FONT color=#008000 size=2>'sort multi-column

    </FONT><FONT size=2>

    .SortRows(0, FpSpread2.Sheets(0).RowCount, si)


    </FONT><FONT color=#0000ff size=2>End</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>With

    </FONT><FONT size=2>

    FormatSpread()

    </FONT>

       :


       :


    End Sub


    Thanks for your reply.

  • Replied 8 September 2017, 1:22 pm EST

    Hello,


    The columns are 0 based, so if you want to sort column A and then B, then the indexes in the sortinfo array you created should be 0 and 1. However, from your schematic, it looks like the columns you want to sort by are 4 and 2. Also, column 4, you said you wanted to be descending, so you need to pass false into the first SortInfo object in the aray.


    <FONT color=#0000ff size=2>Dim</FONT><FONT size=2> si() </FONT><FONT color=#0000ff size=2>As</FONT><FONT size=2> FarPoint.Win.Spread.SortInfo = </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> FarPoint.Win.Spread.SortInfo() {</FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(4, </FONT><FONT color=#0000ff size=2>False</FONT><FONT size=2>), </FONT><FONT color=#0000ff size=2>New</FONT><FONT size=2> FarPoint.Win.Spread.SortInfo(2, </FONT><FONT color=#0000ff size=2>True</FONT><FONT size=2>)}

    </FONT>
  • Replied 8 September 2017, 1:22 pm EST

    Thanks! this works fine.
Need extra support?

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

Learn More

Forum Channels