C# .NET - Not able to sort when using filters in grid view

Asked By amit agarwal
18-Jul-11 01:48 AM
Hi,

I am not able to sort the country column. please let me know how to achieve this.

Regards,
Amit Agarwal
  Ravi S replied to amit agarwal
18-Jul-11 01:51 AM
HI

refer this example

Sorting

The sorting uses an IComparer derived class, DataGridComparer, that sorts by each column in turn.

The code is quite simple. Firstly I cast the object parameters from the IComparer interface, then do the comparison in a separate function: 

public int Compare(object x, object y)
{
    DataGridViewRow lhs = x as DataGridViewRow;
    DataGridViewRow rhs = y as DataGridViewRow;

    return Compare(lhs.Cells, rhs.Cells);
}

public int Compare(DataGridViewCellCollection lhs, DataGridViewCellCollection rhs)
{
    foreach (SortColDefn colDefn in _sortedColumns)
    {
            int retval = Comparer.Default.Compare(
            lhs[colDefn.colNum].Value,
            rhs[colDefn.colNum].Value);

        if (retval != 0)
            return (colDefn.ascending ? retval : -retval);
    }

    // These two rows are indistinguishable.

    return 0;
}

Anonymous delegate

When a column is requested by the user to be sorted, the code needs to determine if that column is already being sorted. If it is then it is promoted to be the primary sort column (in basic user interface or order swapped if using the Control key).

The structure which holds the sort order is a SortColDefn: 

private struct SortColDefn
{
    internal Int16 colNum;
    internal bool ascending;

    internal SortColDefn(int columnNum, SortOrder sortOrder)
    {
        colNum = Convert.ToInt16(columnNum);
        ascending = (sortOrder != SortOrder.Descending);
    }
}

The columns that are currently sorted are stored in a simple array: 

    List<SortColDefn> _sortedColumns;

When you come to find out if a supplied column index is in the array, you could simply loop through the array and check if the SortColDefn.colNum was equal to the passed column index.

However, it's more fun to use the List.FindIndex function and an anonymous delegate: 

    int sortPriority = _sortedColumns.FindIndex(
            delegate(SortColDefn cd) { return cd.colNum == columnIndex; });

On return, sortPriority will be -1 if the columnIndex is not found or set equal to the index into the _sortedColumns array of the SortColDefn with the same columnIndex.



refer the link also
http://www.codeproject.com/KB/grid/multicolumn_sort.aspx
  Ravi S replied to amit agarwal
18-Jul-11 01:51 AM
HI

try this

<%@ Page language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

  void SortButton_Click(Object sender, EventArgs e)
  {

    String expression = "";
    SortDirection direction;

    // Create the sort expression from the values selected 
    // by the user from the DropDownList controls. Multiple
    // columns can be sorted by creating a sort expression
    // that contains a comma-separated list of field names.
    expression = SortList1.SelectedValue + "," + SortList2.SelectedValue;

    //  Determine the sort direction. The sort direction
    // applies only to the second column sorted.
    switch (DirectionList.SelectedValue)
    {
      case "Ascending":
        direction = SortDirection.Ascending;
        break;
      case "Descending":
        direction = SortDirection.Descending;
        break;
      default:
        direction = SortDirection.Ascending;
        break;
    }

    // Use the Sort method to programmatically sort the GridView
    // control using the sort expression and direction.
    CustomersGridView.Sort(expression, direction);

  }

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
    <title>GridView Sort Example</title>
</head>
<body>
    <form id="form1" runat="server">

      <h3>GridView Sort Example</h3>

      <table>
        <tr>
          <td>
             Sort by:
            <asp:dropdownlist ID="SortList1"
              runat="server">
              <asp:listitem Selected="true">CustomerID</asp:listitem>
              <asp:listitem>CompanyName</asp:listitem>
              <asp:listitem>Address</asp:listitem>
              <asp:listitem>City</asp:listitem>
              <asp:listitem>PostalCode</asp:listitem>
              <asp:listitem>Country</asp:listitem>
            </asp:dropdownlist>
          </td>
          <td colspan="2">
            &nbsp;
          </td>
        </tr>
        <tr>
          <td>
            Then by:
              <asp:dropdownlist ID="SortList2"
                runat="server">
                <asp:listitem Selected="true">CustomerID</asp:listitem>
                <asp:listitem>CompanyName</asp:listitem>
                <asp:listitem>Address</asp:listitem>
                <asp:listitem>City</asp:listitem>
                <asp:listitem>PostalCode</asp:listitem>
                <asp:listitem>Country</asp:listitem>
              </asp:dropdownlist>
          </td>
          <td>
             Sort order:      
          </td>
          <td>
            <asp:radiobuttonlist id="DirectionList"
              runat="server">
              <asp:listitem selected="true">Ascending</asp:listitem>
              <asp:listitem>Descending</asp:listitem>
            </asp:radiobuttonlist>
          </td>
        </tr>
      </table>

      <asp:button id="SortButton"
        text="Sort"
        onclick="SortButton_Click" 
        runat="Server"/>  

      <br/>
      <hr/>
      <br/>

      <asp:gridview id="CustomersGridView" 
        datasourceid="CustomersSource" 
        autogeneratecolumns="true"
        emptydatatext="No data available." 
        allowpaging="true" 
        runat="server">
      </asp:gridview>

      <!-- This example uses Microsoft SQL Server and connects  -->
      <!-- to the Northwind sample database. Use an ASP.NET     -->
      <!-- expression to retrieve the connection string value   -->
      <!-- from the Web.config file.                            -->
      <asp:sqldatasource id="CustomersSource"
        selectcommand="Select [CustomerID], [CompanyName], [Address], [City], [PostalCode], [Country] From [Customers]"
        connectionstring="<%$ ConnectionStrings:NorthWindConnectionString%>" 
        runat="server"/>

    </form>
  </body>
</html>
  K S replied to amit agarwal
18-Jul-11 01:57 AM
Hi! I've found this source code online, but they use SQL, DataGrid, and repeater.

Please refer the code and apply the same for datagrid

<%@ Page Language="VB" %>

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">
  Dim strFilter As String
  Dim DBConn As New SqlConnection("Server=<SERVER>,<PORT>;UID=<USERNAM E>;PWD=<PASSWORD>;Database=<DATABASE>;")
  Dim DBComm As SqlCommand
  Dim DBAdapt As SqlDataAdapter

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
    Dim ThisFilter As Object = ViewState("vsFilter")
    If Not (ThisFilter Is Nothing) Then
      strFilter = CStr(ThisFilter)
    Else
      strFilter = "All"
    End If
    If Not Page.IsPostBack Then
      BindData(True)
    End If
  End Sub

  Sub BindData(ByVal GetFresh As Boolean)
    Dim DTable As DataTable = Nothing
    If ViewState("vsSortData") Is Nothing Or GetFresh Then
      DBComm = New SqlCommand("SELECT tblProductName FROM tblProducts", DBConn)
      DBAdapt = New SqlDataAdapter(DBComm)
      Dim DSet As New DataSet()
      Try
        DBAdapt.Fill(DSet)
        DTable = DSet.Tables(0)
      Catch EXC As SqlException
        Me.lblError.Text = EXC.Message
        Return
      Finally
        DBConn.Close()
      End Try
      ViewState("vsSortData") = DTable
    Else
      DTable = CType(ViewState("vsSortData"), DataTable)
    End If
    If strFilter = "All" Then
      DTable.DefaultView.RowFilter = String.Empty
    Else
      DTable.DefaultView.RowFilter = "tblProductName LIKE '" & strFilter & "%'"
    End If
    Me.dgProducts.DataSource = DTable.DefaultView
    Me.dgProducts.DataBind()
    BuildAlphaPager()
  End Sub

  Sub BuildAlphaPager()
    Dim DTable As DataTable
    If ViewState(("strLetter")) Is Nothing Then
      Dim arrLetters As String() = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "All"}
      DTable = New DataTable()
      DTable.Columns.Add(New DataColumn("Letter", GetType(String)))
      Dim i As Integer
      For i = 0 To arrLetters.Length - 1
        Dim DRow As DataRow = DTable.NewRow()
        DRow(0) = arrLetters(i)
        DTable.Rows.Add(DRow)
      Next i
      ViewState("strLetter") = DTable
    Else
      DTable = CType(ViewState("strLetter"), DataTable)
    End If
    Me.rptLetters.DataSource = DTable.DefaultView
    Me.rptLetters.DataBind()
  End Sub

  Protected Sub rptLetters_ItemDataBound(ByVal sender As Object, ByVal e As RepeaterItemEventArgs)
    If (e.Item.ItemType = ListItemType.Header) Then
    ElseIf (e.Item.ItemType = ListItemType.Item) Or (e.Item.ItemType = ListItemType.AlternatingItem) Then
      Dim lnkAlpha As LinkButton = CType(e.Item.FindControl("lnkAlpha"), LinkButton)
      lnkAlpha.Text = DataBinder.Eval(e.Item.DataItem, "Letter")
      lnkAlpha.CommandName = "Filter"
      lnkAlpha.CommandArgument = DataBinder.Eval(e.Item.DataItem, "Letter")
      Dim DRView As DataRowView = CType(e.Item.DataItem, DataRowView)
      If CStr(DRView(0)) = strFilter Then
        lnkAlpha.Enabled = False
      End If
    ElseIf (e.Item.ItemType = ListItemType.Footer) Then
    End If
  End Sub
#63728;
  Protected Sub rptLetters_ItemCommand(ByVal source As Object, ByVal e As RepeaterCommandEventArgs)
    If e.CommandName = "Filter" Then
      strFilter = CStr(e.CommandArgument)
      ViewState("vsFilter") = strFilter
      BindData(False)
    End If
  End Sub
</script>

<html>
<head runat="server">
  <title>Untitled Page</title>
</head>
<body>
  <form runat="server">
    <asp:Label ID="lblError" runat="server" EnableViewState="False" />
    <asp:Repeater ID="rptLetters" runat="server" OnItemDataBound="rptLetters_ItemDataBound" OnItemCommand="rptLetters_ItemCommand">
      <ItemTemplate>
        <asp:LinkButton ID="lnkAlpha" runat="server" />
      </ItemTemplate>
    </asp:Repeater>
    <asp:DataGrid ID="dgProducts" runat="server" Width="100%" CellPadding="3" BackColor="#DEBA84" BorderWidth="1px" CellSpacing="2" BorderStyle="None" BorderColor="#DEBA84">
      <ItemStyle ForeColor="#8C4510" BackColor="#FFF7E7" />
      <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#A55129" />
    </asp:DataGrid>
  </form>
</body>
</html>
  Vickey F replied to amit agarwal
18-Jul-11 01:57 AM

Using Sort Property of DataView you can sort GridView.

You have to also implement GridView1_Sorting() event,.

Use this code-

private const string ASCENDING = " ASC";
private const string DESCENDING = " DESC";

public SortDirection GridViewSortDirection
{
get
{
if (ViewState["sortDirection"] == null)
ViewState["sortDirection"] = SortDirection.Ascending;

return (SortDirection) ViewState["sortDirection"];
}
set { ViewState["sortDirection"] = value; }
}


protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
string sortExpression = e.SortExpression;

if (GridViewSortDirection == SortDirection.Ascending)
{
GridViewSortDirection = SortDirection.Descending;
SortGridView(sortExpression, DESCENDING);
}
else
{
GridViewSortDirection = SortDirection.Ascending;
SortGridView(sortExpression, ASCENDING);
}

}

private void SortGridView(string sortExpression,string direction)
{
// You can cache the DataTable for improving performance
DataTable dt = GetData().Tables[0];

DataView dv = new DataView(dt);
dv.Sort = sortExpression + direction;

GridView1.DataSource = dv;
GridView1.DataBind();
}

Try this code and let me know.

{get{if (ViewState["sortDirection"] == null)ViewState["sortDirection"] = SortDirection.Ascending;return (SortDirection) ViewState["sortDirection"]; }set { ViewState["sortDirection"] = value; } }(object sender, GridViewSortEventArgs e){string sortExpression = e.SortExpression;if (GridViewSortDirection == SortDirection.Ascending){GridViewSortDirection = SortDirection.Descending;SortGridView(sortExpression, DESCENDING);}else{GridViewSortDirection = SortDirection.Ascending;SortGridView(sortExpression, ASCENDING); } }{// You can cache the DataTable for improving performanceDataTable dt = GetData().Tables[0]; DataView dv = new DataView(dt); dv.Sort = sortExpression + direction; GridView1.DataSource = dv;GridView1.DataBind(); }

  Riley K replied to amit agarwal
18-Jul-11 01:58 AM
You may refer to the following links to get help, it teach you how to build a drop-down filter list for a gridview column header cell.

 http://www.eggheadcafe.com/tutorials/aspnet/c67c4daa-83c2-4baa-aea4-2c8855527acb/aspnet-gridview-filterin.aspx
 http://www.aspdotnetcodes.com/Asp.Net_GridView_Filter.aspx

  James H replied to amit agarwal
18-Jul-11 01:59 AM
For the Use Query Extender

Many ASP.NET websites allow users to filter data presented to them on the basis of certain conditions. A general approach used in such cases is to re-construct the database query again and again for each such filter parameter. Though this works well as far as end user requirements are concerned, it can make your code a bit tricky and cumbersome to manage. This is because you need to manipulate the WHERE parameters of the data source control under consideration (LINQ Data Source for example) in your source code. That is where the Query Extender control comes handy.

The ASP.NET Query Extender server control allows you to filter data retrieved from a data source without changing the underlying query. It does so using a declarative syntax without any need to programmatically manipulate query parameters. This article explores the Query Extender control with the help of several examples.

Available Filter Options

The Query Extender control allows you to filter data on the basis of several "expressions". They include:

  • Search expression
  • Range expression
  • Property expression
  • Dynamic Filter expression
  • Control Filter expression
  • Custom expression
  • Order By expression

The "search expression" is possibly the most commonly used expression of the Query Extender control. It allows you to filter data on the basis of some string value. The filter can be applied using "contains", "starts with" and "ends with" searching. For example, suppose you have BlogPosts table that stores blog posts. Using "search expression" you can find all blog posts whose title "contains" a specified string value.

The "range expression" allows you to filter data on the basis of a numeric or date range. For example, you may use range expression to find out all blog posts published between certain start and end date.

The "property expression" allows you to filter data on the basis of a column value. For example, you may want to filter only those blog posts on which comments are allowed (Boolean value).

Dynamic Filter expression and Control Filter expression are used with ASP.NET Dynamic Data and we won't discuss them in this article.

If none of the filtering options mentioned above meet your requirements you can always specify a custom LINQ expression to filter the data using a "custom expression".

The "order by expression" doesn't filter the data as such but it simply sorts the data on the basis of certain column and certain direction (ascending / descending).

Please refer this link

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.queryextender.aspx

  amit agarwal replied to James H
18-Jul-11 02:02 AM
Hi All,

Thanks for the quick reply. Please refer to the post below and guide me how to use sorting on the country column which also has a filter drop down box.
  Reena Jain replied to amit agarwal
18-Jul-11 02:25 AM
Hi,

you can do the sorting in case of autogeneratedcolumns=false, because auto generate column only disallow the generation of new column, sorting does not related with it

try this
on aspx page
<asp:GridView ID="GridView1"
  runat="server"
  CellPadding="2"
  AllowSorting="True"
  OnSorting="GridView1_Sorting"
  AutoGenerateColumns="False"
  Width="500px">

on aspx.cs page
private string ConvertSortDirectionToSql(SortDirection sortDirection)
{
   string newSortDirection = String.Empty;
 
   switch (sortDirection)
   {
  case SortDirection.Ascending:
   newSortDirection = "ASC";
   break;
 
  case SortDirection.Descending:
   newSortDirection = "DESC";
   break;
   }
 
   return newSortDirection;
}
 
protected void gridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
BindGridControl();  
gridView.PageIndex = e.NewPageIndex;
gridView.DataBind();
}
 
protected void gridView_Sorting(object sender, GridViewSortEventArgs e)
{
   DataTable dataTable = gridView.DataSource as DataTable;
 
   if (dataTable != null)
   {
  DataView dataView = new DataView(dataTable);
  dataView.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection);
 
  gridView.DataSource = dataView;
  gridView.DataBind();
   }
}

hope this will help you
  Devil Scorpio replied to amit agarwal
19-Jul-11 03:25 AM

Hi Amit,

The Sorting event is raised when the hyperlink to sort a column is clicked, but before the GridView control handles the sort operation. This enables you to provide an event-handling method that performs a custom routine, such as canceling the sorting operation, whenever this event occurs.

A GridViewSortEventArgs object is passed to the event-handling method, which enables you to determine the sort expression for the column and to indicate that the selection operation should be canceled. To cancel the selection operation, set the Cancel property of the GridViewSortEventArgs object to true.

The following example demonstrates how to use the Sorting event to perform the sorting functionality when the GridView control is bound to a DataTable object by setting the DataSource property programmatically.


http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.sorting.aspx#Y508
Create New Account
help
funzione: Protected Sub gridView_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging GridView1.PageIndex = e.NewPageIndex GridView1.DataBind() End Sub Private Function ConvertSortDirectionToSql(ByVal Function Protected Sub gridView_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles GridView1.Sorting Dim dv As Data.DataView = SqlDataSource1.Select(DataSourceSelectArguments.Empty) If dv IsNot Mi sapreste dire come mai? Grazie!! :-P ASP.NET - Italian Discussions System.Web.UI.WebControls.GridViewPageEventArgs (1) System.Web.UI.WebControls.GridViewSortEventArgs (1) GridViewSortEventArgs (1) GridViewPageEventArgs (1) DataSourceSelectArguments.Empty (1) DataSourceSelectArguments (1) SqlDataSource1.Select (1) GridView1.DataSource (1) Problema conosciuto. Si
enabled. and I have functions to handles these as follows: protected void ChangePage(object sender, GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; / / Refresh the list BindGrid(); } protected void SortRecords(object sender, GridViewSortEventArgs e) { DataTable dataTable = GridDataSource(); if (dataTable ! = null) { DataView dataView = new DataView(dataTable); dataView.Sort = GetSortExpression then close it. if (SQLconnection.State = = ConnectionState .Open) { SQLconnection.Close(); } } protected void GridView1_Sorting( object sender, GridViewSortEventArgs e) { bindGridView(e.SortExpression, sortOrder); } public string sortOrder { get { if (ViewState[ "sortOrder" ].ToString() = = "desc" ) { ViewState AllowPaging = True for gridview and write this code in codebehind protected void GridView1_PageIndexChanging( object sender, GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; BindGrid(); / / Bind Grid } hi, By default, when a sorting or SortDirection.Ascending; return (SortDirection) ViewState["sortDirection"]; } set { ViewState["sortDirection"] = value; } } protected void GridView_Sorting(object sender, GridViewSortEventArgs e) { string sortExpression = e.SortExpression; if (GridViewSortDirection = = SortDirection.Ascending) { GridViewSortDirection = SortDirection.Descending; SortGridView(sortExpression, DESCENDING
GridView Paging I have written this code: protected void GridView1_PageIndexChanging( object sender, GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; } I have taken all templated fields in gridview. But when gridview again with the data, on page index changing like; protected void GridView1_PageIndexChanging( object sender, GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; GridView1.DataBind(); } Also go thr this; http: / / msdn.microsoft.com Ans modify your code on page index changing like this; protected void GridView1_PageIndexChanging( object sender, GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; bindDataToGridview(); } when i click next button the same data is DataBind(); con.Close(); } catch ( Exception P) { Response.Write(P.Message); } } protected void GridView1_PageIndexChanging( object sender, GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; } Do one thing change your code like this; Button MAE were missing bindDataToGrid(); } catch ( Exception P) { Response.Write(P.Message); } } protected void GridView1_PageIndexChanging( object sender, GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; bindDataToGrid (); } void bindDataToGrid () { con = new OleDbConnection ( Class2 .GetConnection()); string st
mit Protected Sub Repeater_Menue_1_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.RepeaterCommandEventArgs) Handles Repeater_Menue_1.ItemCommand Wenn nun der Button geklickt wird, m?chte ich gerne ein Popupwindow werden. Wie mache ich das am besten? ASP.NET - German Discussions System.Web.UI.WebControls.RepeaterCommandEventArgs (1) RepeaterCommandEventArgs (1) Repeater (1) Button (1) ItemCommand (1) MacBook (1) Repeatercommand (1) Popupwindow (1) Am 04