Sorting event in GridView in ASP.Net using VB

Question

I am having a problem regarding Sort in Gridview. I am not an expert in vb.net but I have to solve this problem. I want to explain how my data is coming in GridView.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
//also there is some logic in there but I think that part of the code will not effect
  loadgrid()
End Sub

loadgrid() will load for the query data by going through some steps

//This function is used for some logic

Private Sub loadgrid() Handles B_SEARCH.Click, chkLegacy.CheckedChanged, gvEmployer.PageIndexChanged
        GridDataLoader() 
    End Sub

eEmployer will get all the data for the query for gridView

Public Sub GridDataLoader()
       //some code was there because of searching
        Dim dataTable = Employer.getEmployers(eEmployer, chkLegacy.Checked)
        gvEmployer.DataBind()
    End Sub

Public Function GetEmployers(ByVal eEmployer As tblEmployer, ByVal All As Boolean, Optional ByVal sortExpression As String = Nothing) As DataTable
        Dim query = ""
        query =
                "select employer.EmployerID as EmployerId,
                employer.Employer_Name as EmployerName,
           // the query is so large so i delete all for better understanding
                on (employer.Modified_by=tum.UserID)
                where employer.LegacyID IS NULL  and address.ValidityTo is null"
        'End If

        Dim params = ""
        If All = False Then
            query += " AND employer.ValidityTo is null"
        End If

        If (params.Trim() IsNot "") Then
            query = query & params
        End If
        data.setSQLCommand(query, CommandType.Text)
        Return data.Filldata
    End Function

finally, the data is returning into grid view. but my problem is I am not understanding how can I implement the sorting thing. i changed something is view AllowSorting="true" SortExpression="EmployerName" and i don't know what should i do further. I was following this Articel

Albert D. Kallal · Accepted Answer · 2021-11-21 08:56:42Z

1

Well, the basic setup can be like this:

AND ALWAYS have the Not IsPost back stub in ALL of your web pages.

So, I have this markup:

(I used the wizard - create data source). I then BLOW OUT the data source setting, and delete the DataSourc1 from the web page.

so, I have this markup:

   <div style="width:40%;padding:25px">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            DataKeyNames="ID" CssClass="table table-hover" AllowSorting="True" >
            <Columns>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
                <asp:BoundField DataField="HotelName" HeaderText="HotelName" SortExpression="HotelName" />
                <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
                <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
            </Columns>
        </asp:GridView>
        <br />
    </div>

and my code to load is this: (note how my LoadGrid view has a "default" sort

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not IsPostBack Then
        LoadGrid
    End If
End Sub

Sub LoadGrid(Optional strSort = "HotelName")

    GridView1.DataSource = MyRst("SELECT * FROM tblHotels Order by " & strSort)
    GridView1.DataBind()

End Sub

And my sort event stub is this:

Protected Sub GridView1_Sorting(sender As Object, e As GridViewSortEventArgs) Handles GridView1.Sorting

    LoadGrid(e.SortExpression)

End Sub

And the results are this:

Now of course I always get tired of having to type connection and code to create a record set (DataTable), so I have this global helper routine:

Public Function MyRst(strSQL As String) As DataTable

    Dim rstData As New DataTable
    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL As New SqlCommand(strSQL, conn)
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
        End Using
    End Using

    Return rstData
End Function

Edit:

so the additional question is what about sort ASC and DESC.

Well, we could add if you click the heading again, we reverse the sort.

this takes a bit more code, but this would work:

Sub LoadGrid(Optional strSort As String = "HotelName",
             Optional SortASC As Boolean = True)

    Dim rstTable As DataTable
    rstTable = MyRst("SELECT * FROM tblHotels")
    rstTable.DefaultView.Sort = strSort & " " & If(SortASC, "ASC", "DESC")
    GridView1.DataSource = rstTable
    GridView1.DataBind()

    ViewState("Sort") = strSort
    ViewState("SortASC") = SortASC
End Sub

Protected Sub GridView1_Sorting(sender As Object, e As GridViewSortEventArgs) Handles GridView1.Sorting

    If ViewState("Sort") = e.SortExpression Then
        ViewState("SortASC") = Not ViewState("SortASC")
    Else
        ViewState("SortASC") = True
    End If
    LoadGrid(e.SortExpression, ViewState("SortASC"))

End Sub

edited Nov 21, 2021 at 8:56

answered Nov 20, 2021 at 5:38

Albert D. Kallal

50.1k3 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Julker Nien Akib Over a year ago

I yet not completed but I understand this is the solution.

Julker Nien Akib Over a year ago

@albert-d-kallal thank you for the nice code and I am glad that I managed to finish my work on time.

Julker Nien Akib Over a year ago

Hello, bro, I am having one problem. From this solution, this only do Desc or Asc. What will be the logic behind this??

Albert D. Kallal Over a year ago

Ok, that is more complex - I would thus change things a bit. But see my Edit - I have posted how you could click on the heading - if you click again, it reverses the sort for that column.

Julker Nien Akib Over a year ago

Thank you for your reply but I solved it just before you Edit but this means a lot to me and glad that replied.

Collectives™ on Stack Overflow

Sorting event in GridView in ASP.Net using VB

1 Answer 1

Edit:

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit:

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related