Solved

Sorting a Gridview

Posted on 2009-05-08
13
604 Views
Last Modified: 2013-12-26
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
Comment
Question by:rswanigan
  • 5
  • 5
  • 2
13 Comments
 
LVL 10

Expert Comment

by:cdebel
ID: 24341703
and what's the problem?
0
 
LVL 10

Expert Comment

by:cdebel
ID: 24341725
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
 
LVL 5

Expert Comment

by:Krummelz
ID: 24375680
You would also need to manually code the sort event in this case.
0
 
LVL 10

Expert Comment

by:cdebel
ID: 24375715
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
 
LVL 5

Expert Comment

by:Krummelz
ID: 24375780
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
 
LVL 5

Expert Comment

by:Krummelz
ID: 24375798
", then it can handle the sorting automatically."

Spelling error, sorry.
Strange how one cant edit your own posts.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 10

Expert Comment

by:cdebel
ID: 24375905
Its a good thing to know.  Thanks Krummelz.  
0
 

Author Comment

by:rswanigan
ID: 24380046
The application erros out when I  add "SortExpression="FieldName""
0
 
LVL 10

Expert Comment

by:cdebel
ID: 24380059
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
 
LVL 5

Expert Comment

by:Krummelz
ID: 24382303
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
 

Author Comment

by:rswanigan
ID: 24386366
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
 
LVL 5

Accepted Solution

by:
Krummelz earned 500 total points
ID: 24393214
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now