Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 615
  • Last Modified:

Sorting a Gridview

I am trying to make the column headers clickable for sorting purposes.  Code provided below.
<asp:GridView ID="dgResults" runat="server" AllowPaging="True" GridLines="None" AutoGenerateColumns="False" PageSize="25" CssClass="MemberDirectorySearchResultsTable" AllowSorting="True">
    <Columns>
        <asp:CommandField SelectText="" ShowSelectButton="True" HeaderText="Select" >
            <HeaderStyle CssClass="MemberDirectorySearchResultsColumnSelectHeader" />
            <ItemStyle CssClass="MemberDirectorySearchResultsColumnSelect" />
        </asp:CommandField>
        <asp:BoundField >
            <ItemStyle CssClass="MemberDirectorySearchResultsColumnThumbnail" />
        </asp:BoundField>
        <asp:BoundField DataField="ID" >
            <ItemStyle CssClass="MemberDirectorySearchResultsColumnID" />
            <HeaderStyle CssClass="MemberDirectorySearchResultsColumnIDHeader" />
        </asp:BoundField>
        <asp:BoundField DataField="FullName" HeaderText="Name/Title" HtmlEncode="False" >
            <ItemStyle CssClass="MemberDirectorySearchResultsColumnName" />
        </asp:BoundField>
        <asp:BoundField DataField="MemberType" HeaderText="Member Type" >
            <ItemStyle CssClass="MemberDirectorySearchResultsColumnMemberType" />
        </asp:BoundField>
        <asp:BoundField DataField="Company" HeaderText="Organization" >
            <ItemStyle CssClass="MemberDirectorySearchResultsColumnOrganization" />
        </asp:BoundField>
        <asp:BoundField DataField="City" HeaderText="City" >
            <ItemStyle CssClass="MemberDirectorySearchResultsColumnCity" />
        </asp:BoundField>
        <asp:BoundField DataField="State" HeaderText="State" >
            <ItemStyle CssClass="MemberDirectorySearchResultsColumnState" />
        </asp:BoundField>
        <asp:BoundField DataField="ZipCode" HeaderText="Post" >
            <ItemStyle CssClass="MemberDirectorySearchResultsColumnPostalCode" />
        </asp:BoundField>
        <asp:BoundField DataField="Country" HeaderText="Country" >
            <ItemStyle CssClass="MemberDirectorySearchResultsColumnCountry" />
        </asp:BoundField>
    </Columns>
    <RowStyle CssClass="MemberDirectorySearchResultsRow" />
    <HeaderStyle CssClass="MemberDirectorySearchResultsHeader" />
</asp:GridView>

Open in new window

0
rswanigan
Asked:
rswanigan
  • 5
  • 5
  • 2
1 Solution
 
Christian de BellefeuilleProgrammerCommented:
and what's the problem?
0
 
Christian de BellefeuilleProgrammerCommented:
Forget what i said.  I just noticed that you forgot something...

SortExpression="fieldName" for each sortable field.

Like this:
       <asp:BoundField DataField="City" HeaderText="City" SortExpression="City">
            <ItemStyle CssClass="MemberDirectorySearchResultsColumnCity" />
        </asp:BoundField>
0
 
KrummelzCommented:
You would also need to manually code the sort event in this case.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Christian de BellefeuilleProgrammerCommented:
Krummelz, can you explain why it work in my case without any sort?

When i click a column, it does exactly what its supposed to do
0
 
KrummelzCommented:
If you have a datasource specified on the page, then i can handle the sorting automatically. Whereas if you bind the datasource to the gridview from the code, then you also need to code the sort event.
0
 
KrummelzCommented:
", then it can handle the sorting automatically."

Spelling error, sorry.
Strange how one cant edit your own posts.
0
 
Christian de BellefeuilleProgrammerCommented:
Its a good thing to know.  Thanks Krummelz.  
0
 
rswaniganAuthor Commented:
The application erros out when I  add "SortExpression="FieldName""
0
 
Christian de BellefeuilleProgrammerCommented:
As Krummelz specified, if you don'T specify a DataSourceID then you need to define your own Sort method.  On this part, i think that Krummelz is better because i've never done that.
0
 
KrummelzCommented:
I can help yes, I have had a bit of a struggle with that in the past. However the way you do this depends on how you retrieve the data in your code. If you could give me some sample code to show me how you retrieve your data, then I could show you how to do this.
0
 
rswaniganAuthor Commented:
Code from the corresponding ascx.vb file
Imports System.Data
Imports System.Data.SqlClient
 
Partial Class MemberDirectorySearch
    Inherits System.Web.UI.UserControl
    Dim objDataSet As DataSet
 
    Public Event SelectMember(ByVal memberID As Integer)
 
    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim objConnection As SqlConnection
        Dim objCommand As SqlCommand
        Dim objParameter(8) As SqlParameter
        Dim objAdapter As SqlDataAdapter
        Dim strSQLQuery As String
        Dim intRecordCount As Integer
        Dim strDataKeyNames(0) As String
        Dim i As Integer
 
        objConnection = New Data.SqlClient.SqlConnection("Data Source=xyz;Initial Catalog=xyz;User Id=xyz;Password=xyz;")
 
        strSQLQuery = "EXEC spPCMAListMembers @FirstName, @LastName, @CompanyName, @Chapter, @City, @Region, @ZipCode, @Distance, '', '', '', @Country"
        strDataKeyNames(0) = "ID"
 
        objCommand = New SqlCommand(strSQLQuery, objConnection)
        For i = 0 To 8
            objParameter(i) = New SqlParameter
        Next
 
        objParameter(0).ParameterName = "@FirstName"
        objParameter(0).Value = txtFirstName.Text
 
        objParameter(1).ParameterName = "@LastName"
        objParameter(1).Value = txtLastName.Text
 
        objParameter(2).ParameterName = "@CompanyName"
        objParameter(2).Value = txtCompanyName.Text
 
        objParameter(3).ParameterName = "@Chapter"
        objParameter(3).Value = lstChapter.SelectedValue()
 
        objParameter(4).ParameterName = "@City"
        objParameter(4).Value = txtCity.Text
 
        objParameter(5).ParameterName = "@Region"
        objParameter(5).Value = lstRegion.Text
 
        objParameter(6).ParameterName = "@ZipCode"
        objParameter(6).Value = txtZipCode.Text
 
        objParameter(7).ParameterName = "@Distance"
        Try
            objParameter(7).Value = CType(txtDistance.Text, Integer)
        Catch
            objParameter(7).Value = 0
        End Try
 
        objParameter(8).ParameterName = "@Country"
        objParameter(8).Value = lstCountry.SelectedValue()
 
        For i = 0 To 8
            objCommand.Parameters.Add(objParameter(i))
        Next
 
        'if doing a zip code search, need more time before timeout
        If txtZipCode.Text <> "" Then
            objCommand.CommandTimeout = 45
        End If
 
        Try
            objAdapter = New SqlDataAdapter(objCommand)
 
            objDataSet = New DataSet()
            Session("dataset") = objDataSet
            objAdapter.Fill(objDataSet)
 
            dgResults.DataSource = objDataSet
            dgResults.DataKeyNames = strDataKeyNames
            dgResults.DataBind()
 
            intRecordCount = objDataSet.Tables(0).Rows.Count
 
            If intRecordCount = 500 Then
                recordCount.Text = "500 Members Found; The member directory has a search limit of 500 -- there may be more members that match your search critera."
            Else
                recordCount.Text = CType(intRecordCount, String) & " Members Found"
            End If
        Catch ex As Exception
            recordCount.Text = "Sorry, we had problems processing your search.  Please try again.  (" & ex.Message & ")"
        End Try
 
        recordCount.CssClass = "MemberDirectorySearchRecordCount"
 
        objConnection.Close()
    End Sub
 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Me.Page.Session.Item("__aptify_user_object_PersonID") Is Nothing Then
            Me.Visible = False
        Else
            Dim objConnection As SqlConnection
            Dim objCommandCountry As SqlCommand
            Dim objAdapterCountry As SqlDataAdapter
            Dim objDataSetCountry As DataSet
            Dim strSQLQueryCountry As String
            Dim objCommandRegion As SqlCommand
            Dim objAdapterRegion As SqlDataAdapter
            Dim objDataSetRegion As DataSet
            Dim strSQLQueryRegion As String
 
            If IsPostBack Then
                objDataSet = CType(Session("dataset"), Data.DataSet)
                dgResults.PageIndex = 0
            Else
                Try
                    objConnection = New Data.SqlClient.SqlConnection("Data Source=xyz;Initial Catalog=xyz;User Id=xyz;Password=xyz;")
 
                    'load countries
                    strSQLQueryCountry = "SELECT DISTINCT Country FROM vwPersons WHERE MemberType = 'Member' AND Status = 1 ORDER BY Country"
 
                    objCommandCountry = New SqlCommand(strSQLQueryCountry, objConnection)
                    objAdapterCountry = New SqlDataAdapter(objCommandCountry)
                    objDataSet = New DataSet()
                    objAdapterCountry.Fill(objDataSet)
 
                    lstCountry.DataSource = objDataSet
                    lstCountry.DataBind()
 
                    'load states
                    strSQLQueryRegion = "SELECT DISTINCT [State] from vwPersons where MemberType = 'Member' AND [Status] = 1 ORDER BY [State]"
 
                    objCommandRegion = New SqlCommand(strSQLQueryRegion, objConnection)
                    objAdapterRegion = New SqlDataAdapter(objCommandRegion)
                    objDataSet = New DataSet()
                    objAdapterRegion.Fill(objDataSet)
 
                    lstRegion.DataSource = objDataSet
                    lstRegion.DataBind()
 
                    objConnection.Close()
                Catch
                    Response.Write("Sorry, we couldn't connect to the database at this time.  Please try again later.")
                    Me.Visible = False
                End Try
            End If
        End If
    End Sub
 
    Protected Sub dgResults_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles dgResults.PageIndexChanging
        dgResults.PageIndex = e.NewPageIndex
        dgResults.DataSource = objDataSet
        dgResults.DataBind()
    End Sub
 
    Protected Sub dgResults_RowCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles dgResults.RowCreated
        'adds javascript to every row to support "click anywhere" and the color changing on hover
        If e.Row.DataItemIndex > -1 Then
            e.Row.Attributes.Add("onmouseover", "this.className='MemberDirectorySearchResultsRowHighlight';this.style.cursor='pointer'")
            e.Row.Attributes.Add("onmouseout", "this.className='MemberDirectorySearchResultsRow';")
            e.Row.Attributes.Add("onclick", "javascript:__doPostBack('" & Me.ID & "$dgResults', 'Select$" & CType(e.Row.RowIndex, String) & "')")
        End If
 
        'determine if the thumbnail file exists...if it does, change the first column in the
        'data grid to display it...if it doesn't, then chagne the first column in the data grid
        'to display a placeholder
 
        'we can't use the standard image column type because the datasource only has the id, not
        'whole image name, so this has to be a dynamic evaluation
        If e.Row.RowType() = DataControlRowType.DataRow And Not e.Row.DataItem() Is Nothing Then
            e.Row.DataBind()
            Dim rowID As String
 
            rowID = e.Row.Cells(2).Text
 
            Dim fileName As String
 
            fileName = "http://xyz/memberpictures/profileimages/thumb/" & rowID & ".jpg"
 
            If System.IO.File.Exists("\\xyzpub\memberpictures\profileimages\thumb\" & rowID & ".jpg") Then
                e.Row.Cells(1).Text = "<img src='" & fileName & "'>"
            Else
                e.Row.Cells(1).Text = "<img src='images/noPictureThumb.gif'>"
            End If
        End If
    End Sub
 
    Protected Sub dgResults_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgResults.SelectedIndexChanged
        RaiseEvent selectMember(dgResults.SelectedDataKey.Value)
    End Sub
End Class

Open in new window

0
 
KrummelzCommented:
Ok, in your case, there are two methods that could work.

The first, would be to do the following:
On the sort method for the GridView, you run your same query again, but with an OrderBy clause that changes based on e.SortDirection and e.SortExpression. (The same query that you use to put data into your dgResults GridView.)

The second method,
would be similar to what I did in the attached code snippet. The snippet is in C#, but C#.NET and VB.NET are very much the same. The code is my sorting method for a gridview. I create two string variables to store the column and sort direction, then put the data in the gridview's datasource, into a DataTable variable.

To reverse the sort direction every time a column is clicked:
I make use of the ViewState to store the sort direction, and I check what the direction was previously, and do the opposite of what was stored. So if it wasnt set, then I set it to descending. The next time the user sorts, I check and see that it was sorted descending, so this time we sort ascending. And the next time we check it will be ascending, so we sort descending. etc etc..

You can then use the DataTable's Sort method to sort the data how you want it, and then just set it as the datasource again.
protected void grdAccountTransactions_Sorting(object sender, GridViewSortEventArgs e)
    {
        string column = e.SortExpression.ToString();
        string direction = e.SortDirection.ToString();
 
        DataTable dt = (DataTable)grdAccountTransactions.DataSource;
 
        if ((ViewState["grdAccTransSortDirection"] == null) || (ViewState["grdAccTransSortDirection"].ToString() == ""))
        {
            ViewState["grdAccTransSortDirection"] = "desc";
        }
        else
        {
            if (ViewState["grdAccTransSortDirection"].ToString() == "desc")
            {
                ViewState["grdAccTransSortDirection"] = "asc";
            }
            else
            {
                ViewState["grdAccTransSortDirection"] = "desc";
            }
        }
 
        dt.DefaultView.Sort = column + " " + ViewState["grdAccTransSortDirection"].ToString();
        
        grdAccountTransactions.DataSource = dt;
        grdAccountTransactions.DataBind();
    }

Open in new window

0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now