Manual sorting for gridview in vb.net

I have done research on how to manually create sorting in a grid-view and none of it is thorough enough for me to follow. I need more of a step by step solution, like what event should my sorting code go in, how would i enable the headers to allow sorting. normally, i just have .net do this but for some reason this time it doesn't allow it, maybe because i am not using a datasource.

that's my code that creates a datable and then binds to the gridview.

Function toptable()


        Dim reader As SqlDataReader
        cmd.Parameters.AddWithValue("@yeartoget", DropDownList1.SelectedValue)
        cmd.Parameters.AddWithValue("@mode", RadioButtonList1.SelectedValue)

        If TextBox1.Text = "" Then
            Dim d As Date = Date.Today
            TextBox1.Text = d.AddDays(-1)

        End If

        Dim pyear As Date
        Dim pyear1 As Date
        pyear = TextBox1.Text
        Dim year1 As Int16
        year1 = (pyear.Year - DropDownList1.SelectedValue)

        If pyear.Year <> Now.Year Then
            pyear1 = (pyear.AddYears(-(year1 + 1)))

        Else
            pyear1 = (pyear.AddYears(-year1))
        End If



        cmd.Parameters.AddWithValue("@current", TextBox1.Text)
        cmd.Parameters.AddWithValue("@search", pyear1)
        cmd.CommandText = "asoftour"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = conn
        cmd.CommandTimeout = 300
        conn.Open()
        reader = cmd.ExecuteReader()

        mytable.clear()

        myTable.Load(reader)

        conn.Close()


        Dim currentppax As New DataColumn
        currentppax = New DataColumn("AVGPPAX", GetType(Double))
        currentppax.Expression = "IIf([CurrentPAX] = 0, 0, [CurrentSales] / [CurrentPAX])"

        Dim selectedppax As New DataColumn
        selectedppax = New DataColumn("AVGpPAX1", GetType(Double))
        selectedppax.Expression = "IIf([asofPAX] = 0, 0, [asofSales] / [asofPAX])"

        Dim projsales As New DataColumn
        projsales = New DataColumn("ProjSales", GetType(Double))
        projsales.Expression = "IIF([PercentSales] = 0, [CurrentSales], [CurrentSales] / [PercentSales])"

        Dim projpax As New DataColumn
        projpax = New DataColumn("ProjPAX", GetType(Double))
        ' projpax.Expression = "IIF([PercentPAX] = 0, [CurrentPAX], [CurrentPAX] / [percentpax] )"
        projpax.Expression = "IIF([PercentPAX] =  0, [CurrentPAX], [CurrentPAX] / [percentpax] )"

        Dim remainingsales As New DataColumn
        remainingsales = New DataColumn("remainingsales", GetType(Double))
        remainingsales.Expression = "ProjSales - currentsales"

        Dim remainingpax As New DataColumn
        remainingpax = New DataColumn("remainingpax", GetType(Double))
        remainingpax.Expression = "Projpax - currentpax"

        Dim movementpax As New DataColumn
        movementpax = New DataColumn("movementPAX", GetType(Double))
        movementpax.Expression = "IIF([ASOFPAX] = 0, 2,(CurrentPAX / [ASOFPAX]))-1"

        Dim movementsales As New DataColumn
        movementsales = New DataColumn("movementsales", GetType(Double))
        movementsales.Expression = "IIF([ASOFsales] = 0, 2,(Currentsales / [ASOFsales]))-1"


        myTable.Columns.Add(selectedppax)
        myTable.Columns.Add(currentppax)
        myTable.Columns.Add(projsales)
        myTable.Columns.Add(projpax)
        myTable.Columns.Add(remainingsales)
        myTable.Columns.Add(remainingpax)
        myTable.Columns.Add(movementsales)
        myTable.Columns.Add(movementpax)

        GridView1.DataSource = myTable
        GridView1.DataBind()
        Dim row As New GridViewRow(0, -1, DataControlRowType.Header, DataControlRowState.Normal)

        'spanned cell that will span the columns I don't want to give the additional header 
        Dim compare As TableCell = New TableHeaderCell()
        compare.ColumnSpan = 8
        row.Cells.Add(compare)
        compare.Text = DropDownList1.Text


        'spanned cell that will span the columns i want to give the additional header
        Dim current As TableCell = New TableHeaderCell()
        current.ColumnSpan = 3
        current.Text = Year(Now())
        row.Cells.Add(current)

        Dim Projection As TableCell = New TableHeaderCell()
        Projection.ColumnSpan = 4
        Projection.Text = "Projections"
        row.Cells.Add(Projection)

        Dim Movements As TableCell = New TableHeaderCell()
        Movements.ColumnSpan = 2
        Movements.Text = "Movement"
        row.Cells.Add(Movements)

        'Add the new row to the gridview as the master header row
        'A table is the only Control (index[0]) in a GridView
        DirectCast(GridView1.Controls(0), Table).Rows.AddAt(0, row)

And thats my client side code

                    <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" CellSpacing="2" AutoGenerateColumns="False" ShowFooter="True" Font-Size="Smaller" AllowSorting="True" OnRowDataBound="GridView开发者_StackOverflow社区1_RowDataBound" OnSorting="GridView1_Sorting">
                        <RowStyle BackColor="#EFF3FB" />
                        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                        <EditRowStyle BackColor="#2461BF" />
                        <AlternatingRowStyle BackColor="White" />
                        <Columns>
                            <asp:TemplateField FooterText="Totals:" HeaderText="Regions">
                                <EditItemTemplate>
                                    <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Tour") %>'></asp:TextBox>
                                </EditItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("Tour") %>'></asp:Label>
                                </ItemTemplate>
                                <FooterStyle Font-Bold="True" />
                            </asp:TemplateField>

<asp:TemplateField HeaderText="As Of Sales">
<ItemTemplate>
 <%#Getsales(Decimal.Parse(Eval("asofsales").ToString())).ToString("C0")%>
</ItemTemplate>
<FooterTemplate>
<%#Getsales1().ToString("C0")%>
</FooterTemplate>
    <FooterStyle Font-Bold="True" />
</asp:TemplateField>

<asp:TemplateField HeaderText="YE Sales">
<ItemTemplate>
 <%#Getyesales(Decimal.Parse(Eval("yesales").ToString())).ToString("C0")%>
</ItemTemplate>
<FooterTemplate>
<%#Getyesales1().ToString("C0")%>
</FooterTemplate>
    <FooterStyle Font-Bold="True" />
</asp:TemplateField>


<asp:TemplateField HeaderText="PCT Sales">
<ItemTemplate>
 <%#GetAvesales(Decimal.Parse(Eval("percentSales").ToString())).ToString("P1")%>
</ItemTemplate>
<FooterTemplate>
<%#GetAvesales1().ToString("P1")%>
</FooterTemplate>
    <FooterStyle Font-Bold="True" />
</asp:TemplateField>      



<asp:TemplateField HeaderText="As Of PAX">
<ItemTemplate>
 <%#Getpax(Decimal.Parse(Eval("asofpax").ToString())).ToString("N0")%>
</ItemTemplate>
<FooterTemplate>
<%#Getpax1().ToString("N0")%>
</FooterTemplate>
    <FooterStyle Font-Bold="True" />
</asp:TemplateField>



 <asp:TemplateField HeaderText="YE PAX">
<ItemTemplate>
 <%#Getyepax(Decimal.Parse(Eval("YEpax").ToString())).ToString("N0")%>
</ItemTemplate>
<FooterTemplate>
<%#Getyepax1().ToString("N0")%>
</FooterTemplate>
     <FooterStyle Font-Bold="True" />
</asp:TemplateField>


<asp:TemplateField HeaderText="PCT PAX">
<ItemTemplate>
 <%#GetAvepax(Decimal.Parse(Eval("percentpax").ToString())).ToString("P2")%>
</ItemTemplate>
<FooterTemplate>
<%#GetAvepax1().ToString("P1")%>
</FooterTemplate>
    <FooterStyle Font-Bold="True" />
</asp:TemplateField>      


<asp:TemplateField HeaderText="AVG PPAX">
<ItemTemplate>
 <%#GetAveppax(Decimal.Parse(Eval("AVGppax1").ToString())).ToString("C0")%>
</ItemTemplate>
<FooterTemplate>
<%#GetpreviousAveppax1().ToString("C0")%>
</FooterTemplate>
<FooterStyle Font-Bold="True" />
</asp:TemplateField>                           



<asp:TemplateField HeaderText="Current Sales">
<ItemTemplate>
 <%#GetCsales(Decimal.Parse(Eval("CurrentSales").ToString())).ToString("C0")%>
</ItemTemplate>
<FooterTemplate>
<%#GetCsales1().ToString("C0")%>
</FooterTemplate>
    <FooterStyle Font-Bold="True" />
</asp:TemplateField>



<asp:TemplateField HeaderText="Current PAX">
<ItemTemplate>
 <%#GetCpax(Decimal.Parse(Eval("CurrentPAX").ToString())).ToString("N0")%>
</ItemTemplate>
<FooterTemplate>
<%#GetCpax1().ToString("N0")%>
</FooterTemplate>
    <FooterStyle Font-Bold="True" />
</asp:TemplateField>



                            <asp:TemplateField HeaderText="AVG PPAX">
<ItemTemplate>
 <%#GetAveppax(Decimal.Parse(Eval("AVGppax").ToString())).ToString("C0")%>
</ItemTemplate>
<FooterTemplate>
<%#GetAveppax1().ToString("C0")%>
</FooterTemplate>
                                <FooterStyle Font-Bold="True" />
</asp:TemplateField>


<asp:TemplateField HeaderText="Proj. Sales">
<ItemTemplate>
 <%#GetPsales(Decimal.Parse(Eval("ProjSales").ToString())).ToString("C0")%>
</ItemTemplate>
<FooterTemplate>
<%#GetPsales1().ToString("C0")%>
</FooterTemplate>
    <FooterStyle Font-Bold="True" />
</asp:TemplateField>

<asp:TemplateField HeaderText="Proj. PAX">
<ItemTemplate>
 <%#GetPpax(Decimal.Parse(Eval("ProjPax").ToString())).ToString("N0")%>
</ItemTemplate>
<FooterTemplate>
<%#GetPpax1().ToString("N0")%>
</FooterTemplate>
    <FooterStyle Font-Bold="True" />
</asp:TemplateField>


<asp:TemplateField HeaderText="Remaining Sales">
<ItemTemplate>
 <%#GetRPsales(Decimal.Parse(Eval("remainingsales").ToString())).ToString("C0")%>
</ItemTemplate>
<FooterTemplate>
<%#GetRPsales1().ToString("C0")%>
</FooterTemplate>
    <FooterStyle Font-Bold="True" />
</asp:TemplateField>

<asp:TemplateField HeaderText="Remaining PAX">
<ItemTemplate>
 <%#GetRPpax(Decimal.Parse(Eval("RemainingPax").ToString())).ToString("N0")%>
</ItemTemplate>
<FooterTemplate>
<%#GetRPpax1().ToString("N0")%>
</FooterTemplate>
    <FooterStyle Font-Bold="True" />
</asp:TemplateField>                                             


<asp:TemplateField HeaderText="PCT. Pax">
<ItemTemplate>
 <%#Getmovepax(Decimal.Parse(Eval("movementPAX").ToString())).ToString("P1")%>
</ItemTemplate>
<FooterTemplate>

<%#Getmovepax1().ToString("P1")%>
</FooterTemplate>
    <FooterStyle Font-Bold="True" />
</asp:TemplateField>

<asp:TemplateField HeaderText="PCT. Sales">
<ItemTemplate>
 <%#Getmovesales(Decimal.Parse(Eval("movementsales").ToString())).ToString("P1")%>
</ItemTemplate>
<FooterTemplate>
<%#Getmovesales1().ToString("P1")%>

</FooterTemplate>
    <FooterStyle Font-Bold="True" />
</asp:TemplateField>
                            <asp:TemplateField HeaderText="Theme">
                                <ItemTemplate>
                                    <asp:Label ID="lbltheme" runat="server" Text="Label"></asp:Label><br />
                                </ItemTemplate>
                            </asp:TemplateField>

                        </Columns>                     
                    </asp:GridView>

This is what i tried but no links show on my headers.

 Public Property GridViewSortDirection() As SortDirection


        Get


            If ViewState("sortDirection") Is Nothing Then

                ViewState("sortDirection") = SortDirection.Ascending
            End If


            Return DirectCast(ViewState("sortDirection"), SortDirection)
        End Get

        Set(ByVal value As SortDirection)
            ViewState("sortDirection") = value
        End Set
    End Property

    Protected Sub GridView1_Sorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs)


        Dim sortExpression As String = e.SortExpression

        If GridViewSortDirection = SortDirection.Ascending Then


            GridViewSortDirection = SortDirection.Descending


            SortGridView(sortExpression, SortDirection.Descending)
        Else



            GridViewSortDirection = SortDirection.Ascending


            SortGridView(sortExpression, SortDirection.Ascending)
        End If

    End Sub

    Private Sub SortGridView(ByVal sortExpression As String, ByVal direction As String)


        ' You can cache the DataTable for improving performance

        Dim dv As New DataView(myTable)

        dv.Sort = sortExpression & direction

        GridView1.DataSource = dv

        GridView1.DataBind()

    End Sub

You need to set the SortExpression.

You can do it in code behind using something like the following:

Private Sub setSortExpression()
Try

Dim field As DataControlField
For Each field In gridView.Columns

If TypeOf field Is BoundField Then

Dim dbf As BoundFielddbf = DirectCast(field, BoundField)
field.SortExpression = dbf.DataField

End If

Next

Catch ex As Exception
End Try

End Sub

Actually, I figured it out. I had to add the sort expression to the client side code.

To fix my problem the first step, was to add the sort expression, to the client side code.

<asp:TemplateField HeaderText="As Of Sales" SortExpression= "asofsales">

This fixed my problem to allow sorting, but it would only sort in one direction ascending. I had the correct sorting code the problem is I had to add a couple things.

Private Sub SortGridView(ByVal sortExpression As String, ByVal direction As String)


            If direction = "1" Then
                direction = "ASC"
            Else
                direction = "DESC"
            End If
            ' You can cache the DataTable for improving performance


            transferview.Sort = sortExpression + " " + direction

            GridView1.DataSource = transferview

            GridView1.DataBind()

        End Sub


    End Class

Before i was using transferview.Sort = sortExpression + direction , which would not work because it would concatenate, the two strings together and use them as a column name, which would cause the program not to find the column. So i simply changed it to

    transferview.Sort = sortExpression + " " + direction

and it worked perfectly.

Convert the sort order

dv.Sort = sortExpression & " " & ConvertSortDirectionToSql(direction)