Wednesday, 15 August 2012

Filtering on each column in GridView as Excel Sheet functionality

As customers are using Excel application in various situations, they are much happier when providing the functionalities like the same in ASP.NET applications. Sometime the customer will be asking such kind of functionalities in the application.

Today in this post, I am planning to post one of an interesting feature from the Excel sheet - that is filtering on each column. For Example, I have an excel sheet with some column and I have enabled filtering on each column. The excel sheet will look as below –


As we seen, the filtering window shows on each column (which can be decided by the used). When user pressing the down array on a particular column, it shows a list which contains the distinct values of all the values on that column.

I have implemented the same in the GridView. The GridView with filtering window will be as below.
The requirement for this implementation is as below –
  1. The GridView should show down arrow on each column, which can be decided by the programmer.
  2. When clicking the down arrow on a particular column, the system should show distinct list of the values in a dropdown window.
  3. There should be two button OK and Cancel in that dropdown window.
  4. The user can select one or more values in the list and press OK button. When clicking the OK button, the system should query the filtered values based on the selected values in the dropdown list and show in the GridView.
  5. When selecting multiple columns, the values must be filtered as per the multiple column values. Note: The filtering will be based on AND operator on all the columns.
    For Ex: When the user selects some countries on Country column and some courier types in CourierType column, then the query will be selected the rows where the countries are in selected countries and courier types are in selected courier types. (There is no OR operator involved anywhere here)
  6. When clicking Cancel button, the window should hide. When pressing mouse outside of the window the dropdown window must hide.
  7. There should be a clear all icon in the dropdown window, which helps the user to clear all the selection done in that particular column. So, by pressing that clear all icon, the system should clear the values and call the server to bind the records by clearing the selection.
Note: This implementation differs little bit from Excel functionalities such as -
  1. The values in the dropdown list will not be selected by default. When all the records are not selected, the system will consider the user needs to retrieve all the rows without any filtering for that column.
  2. There is no Select All checkbox in the dropdown list, as if all the values are unchecked - it will be considered as selected all.
  3. The Clear All icon, will clear the selected values in the dropdown list, it means – the user needs all the values to be retrieved for that column.
  4. By default the excel sheet will show the dropdown list based on the rows which are filtered by the previous filtering condition.
    For Ex: Consider we have an Excel sheet which shows all Countries, States and Cities in the world. The user selected Country = India, State = Tamil Nadu. When user pressing the filtering icon on City column, the excel sheet never shows distinct of all the cities in that column - rather it will first filter India, Tamil Nadu and shows the cities available in that two condition. So the cities will be in Tamil Nadu and in India.
    But in this example, we have no implementation to do that automatically. So the developer needs to write the SQL query in the code behind and return records based on other filtering condition.
The Concept
The concept of this implementation is very simple. When GridView gets rendered, I show the down arrow on each column (or decided by the programmer) by getting each column object and assigning css class to it. When user presses the down arrow, the JQuery calls the WCF service by passing the column name. The service will return the distinct values of that particular column. The JQuery will then create a dropdown window and show the list of values.

When the user selects some values and press OK button, the JQuery build a JSON object using the filter values on all the columns and assign to a hidden control. It also calls Refresh button click event on which the server gets the hidden control JSON value and decrypt it then build the SQL Query dynamically and bind to the GridView.

I am using Northwind database in this implementation. So make sure to have the same database for running this source code.

The implementation
The ASPX Script
<asp:GridView ID="grdViewOrders" runat="server"
    AllowPaging="True" AutoGenerateColumns="False" TabIndex="1"
    DataKeyNames="OrderID" Width="100%" GridLines="None"
    CellPadding="3" CellSpacing="1" AllowSorting="false" BorderWidth="1px" 
    onpageindexchanging="grdViewOrders_PageIndexChanging">
    <Columns>
        <asp:BoundField DataField="OrderID" HeaderText="Order ID" 
            SortExpression="OrderID" >
        <ItemStyle Width="10%" />
        </asp:BoundField>
        <asp:BoundField DataField="OrderDate" HeaderText="Order Date" 
            SortExpression="OrderDate" DataFormatString="{0:dd-MMM-yyyy}" >
        <ItemStyle Width="10%" />
        </asp:BoundField>
        <asp:BoundField DataField="ShipName" HeaderText="Ship Name" 
            SortExpression="ShipName" >
        <ItemStyle Width="20%" />
        </asp:BoundField>
        <asp:BoundField DataField="ShipCity" HeaderText="Ship City" 
            SortExpression="ShipCity" >
        <ItemStyle Width="20%" />
        </asp:BoundField>
        <asp:BoundField DataField="Freight" HeaderText="Freight" 
            SortExpression="Freight" DataFormatString="{0:#0.00}" >
        <ItemStyle Width="10%" />
        </asp:BoundField>
        <asp:BoundField DataField="ShipPostalCode" HeaderText="Ship Postal Code" 
            SortExpression="ShipPostalCode" >
        <ItemStyle Width="10%" />
        </asp:BoundField>
        <asp:BoundField DataField="ShipCountry" HeaderText="Ship Country" 
            SortExpression="ShipCountry" >
        <ItemStyle Width="20%" />
        </asp:BoundField>
    </Columns>
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Right" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <EditRowStyle BackColor="#999999" />
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
<asp:Button ID="Refresh" runat="server" Text="Refresh" OnClick="Refresh_Click" />
<asp:HiddenField ID="hndSelectedValue" runat="server" Value="" />
The C# Code
protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        BindGrid();
    }
}

private string ArrayToString(IEnumerable<string> strValues)
{
    string strValue = string.Empty;
    foreach (string strVal in strValues)
        strValue = strValue + "'" + strVal + "', ";
    if (strValue.Length > 0)
        strValue = "(" + strValue.Substring(0, strValue.Length - 2) + ")";

    return strValue;
}
private void BindGrid()
{
    string strQuery = "SELECT OrderID, OrderDate, ShipName, ShipCity, Freight, ShipPostalCode, ShipCountry FROM Orders";
    string strShipName = string.Empty, strShipCity = string.Empty;
    var objJavaScriptSerializer = new JavaScriptSerializer();

    if (hndSelectedValue.Value.Trim().Length > 0)
    {
        SelectedDataCollection objSelectedDataCollection = new SelectedDataCollection();
        objSelectedDataCollection = objJavaScriptSerializer.Deserialize<SelectedDataCollection>(hndSelectedValue.Value);

        foreach (SelectedData selectedData in objSelectedDataCollection.DataCollection)
        {
            if (selectedData.SelectedValue.Count<string>() > 0)
                strQuery = strQuery + ((strQuery.Contains(" WHERE ") == true) ? " AND " : " WHERE ") + selectedData.ColumnName + " IN " + ArrayToString(selectedData.SelectedValue);
        }
    }

    OrderDAO objProductDAO = new OrderDAO();

    IList<OrderView> orderViewList = objProductDAO.GetOrders(strQuery);
    if (orderViewList.Count == 0)
        orderViewList.Add(new OrderView());

    grdViewOrders.DataSource = orderViewList;
    grdViewOrders.DataBind();

    for (int intRowIndex = 0; intRowIndex < grdViewOrders.Columns.Count; intRowIndex++)
    {
        TableCell tblCell = ((TableCell)grdViewOrders.HeaderRow.Cells[intRowIndex]);
        tblCell.CssClass = "filter";
        tblCell.Attributes.Add("alt", grdViewOrders.Columns[intRowIndex].SortExpression);
    }
}

protected void Refresh_Click(object sender, EventArgs e)
{
    BindGrid();
}

protected void grdViewOrders_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    grdViewOrders.PageIndex = e.NewPageIndex;
    BindGrid();
}
The DAO Class
public class OrderDAO
{
    public IList<OrderView> GetOrders()
    {
        return GetOrders(string.Empty);
    }
    public IList<OrderView> GetOrders(string strQuery)
    {
        SqlConnection connection = null;

        try
        {
            connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
            if (strQuery == string.Empty)
                strQuery = @"SELECT OrderID, OrderDate, ShipName, ShipCity, Freight, ShipPostalCode, ShipCountry FROM Orders";

            SqlCommand command = new SqlCommand();
            command.CommandText = strQuery;
            command.Connection = connection;

            command.Connection.Open();
            SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);

            IList<OrderView> orderViewList = new List<OrderView>();

            while (dataReader.Read())
            {
                OrderView orderView = new OrderView();
                orderView.OrderID = Convert.ToInt32(dataReader["OrderID"].ToString());
                orderView.OrderDate = Convert.ToDateTime(dataReader["OrderDate"].ToString());
                orderView.ShipName = dataReader["ShipName"].ToString();
                orderView.ShipCity = dataReader["ShipCity"].ToString();
                orderView.Freight = Convert.ToDouble(dataReader["Freight"].ToString());
                orderView.ShipPostalCode = dataReader["ShipPostalCode"].ToString();
                orderView.ShipCountry = dataReader["ShipCountry"].ToString();

                orderViewList.Add(orderView);
            }

            connection.Close();

            //return
            return orderViewList;
        }
        catch (Exception ex)
        {
            if (connection != null) connection.Close();

            // Log
            throw ex;
        }
    }
}
The OrderView entity class
public class OrderView
{
    public int OrderID { get; set; }
    public DateTime OrderDate { get; set; }
    public string ShipName { get; set; }
    public string ShipCity { get; set; }
    public double Freight { get; set;}
    public string ShipPostalCode { get; set; }
    public string ShipCountry { get; set; }
}
The WCF Service code
[ServiceContract]
public interface IService1
{
    [OperationContract]
    [WebGet(UriTemplate = "/data?ColumnName={strColumnName}", ResponseFormat = WebMessageFormat.Json)]
    FilterValueSet[] GetDistinctValue(string strColumnName);
}
public class Service1 : IService1
{
    public FilterValueSet[] GetDistinctValue(string strColumnName)
    {
        SqlConnection connection = null;

        try
        {

            connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());

            string strSQL = string.Empty;
            switch (strColumnName) // This is the column name in the GridView defined
            {
                case "OrderID":
                    strSQL = @"SELECT distinct OrderID, OrderID FROM Orders Order By 1";
                    break;
                case "OrderDate":
                    strSQL = @"SELECT distinct OrderDate, CONVERT(VARCHAR(11), OrderDate, 106) AS [OrderDate] FROM Orders Order By 1";
                    break;
                case "ShipName":
                    strSQL = @"SELECT distinct ShipName, ShipName FROM Orders Order By 1";
                    break;
                case "ShipCity":
                    strSQL = @"SELECT distinct ShipCity, ShipCity FROM Orders Order By 1";
                    break;
                case "Freight":
                    strSQL = @"SELECT distinct Freight, Freight FROM Orders Order By 1";
                    break;
                case "ShipPostalCode":
                    strSQL = @"SELECT distinct ShipPostalCode, ShipPostalCode FROM Orders Order By 1";
                    break;
                case "ShipCountry":
                    strSQL = @"SELECT distinct ShipCountry, ShipCountry FROM Orders Order By 1";
                    break;

            }

            SqlCommand command = new SqlCommand();
            command.CommandText = strSQL;
            command.Connection = connection;

            command.Connection.Open();

            SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);

            IList<FilterValueSet> filterValueList = new List<FilterValueSet>();

            while (dataReader.Read())
            {
                filterValueList.Add(new FilterValueSet
                {
                    Id = dataReader[0].ToString(),
                    Value = dataReader[1].ToString()
                });
            }
            connection.Close();

            return filterValueList.ToArray<FilterValueSet>();
        }
        catch (Exception ex)
        {
            if (connection != null) connection.Close();

            // Log
            throw ex;
        }
    }
}
<system.serviceModel>
  <behaviors>
    <serviceBehaviors>
      <behavior>
        <serviceMetadata httpGetEnabled="true"/>
        <serviceDebug includeExceptionDetailInFaults="false"/>
      </behavior>
    </serviceBehaviors>
    <endpointBehaviors>
      <behavior name="WebBehavior">
        <webHttp />
      </behavior>
    </endpointBehaviors>
  </behaviors>
  <bindings>
    <wsHttpBinding>
      <binding name="NoSecurity">
        <security mode="None"/>
      </binding>
    </wsHttpBinding>
  </bindings>
  <services>
    <service name="FilterGridView.Service1">
      <endpoint address="" behaviorConfiguration="WebBehavior" binding="webHttpBinding" contract="FilterGridView.IService1"/>
    </service>
  </services>
  <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />
</system.serviceModel>
Helper classes
public class SelectedData
{
    public string ColumnName { get; set; }
    public IEnumerable<string> SelectedValue { get; set; }
}
public class SelectedDataCollection
{
    public IEnumerable<selecteddata> DataCollection { get; set; }
}
public class FilterValueSet
{
    public string Id { get; set; }
    public string Value { get; set; }
}
The Style sheet
<style type="text/css">
    th.filter
    {
     background: url(../../images/Down.png) right center no-repeat;
     cursor:pointer;
    }
</style>
Finally the JQuery code
$(document).ready(function () {
    $('.filter').click(function () {

        var pos = $(this).position();
        var width = $(this).outerWidth();
        var height = $(this).outerHeight();
        var colName = $(this).attr("alt");

        jQuery.support.cors = true;

        $.ajax({

            type: "GET",
            url: "http://localhost:1609/Service1.svc/data?ColumnName=" + colName,
            processData: false,
            contentType: "application/json; charset=utf-8",
            dataType: "json",

            success: function (response) {

                var filterdiv = $("<div id='divFilterList'></div>");
                var div = $("<div></div>");
                var valuesdiv = $("<div id='filterChkBox'></div>");
                var table = $("<table />");
                var SelectedValue = {};
                var hiddenControlValue = $('#MainContent_hndSelectedValue').val();

                if (hiddenControlValue.length > 0) {
                    var alreadySelectedValueInfo = jQuery.parseJSON(hiddenControlValue);
                    for (var index = 0; index < alreadySelectedValueInfo.DataCollection.length; index++) {
                        if (alreadySelectedValueInfo.DataCollection[index].ColumnName == colName) {
                            SelectedValue = alreadySelectedValueInfo.DataCollection[index].SelectedValue;
                        }
                    }
                }

                $.each(response, function (index, obj) {
                    var IsSelected = false;
                    for (var index = 0; index < SelectedValue.length; index++) {
                        if (SelectedValue[index] == obj.Id)
                            IsSelected = true;
                    }

                    if (IsSelected == true)
                        table.append("<tr><td><input name='options' checked='checked' type='checkbox' value='" + obj.Id + "'>" + obj.Value + "</td></tr>");
                    else
                        table.append("<tr><td><input name='options' type='checkbox' value='" + obj.Id + "'>" + obj.Value + "</td></tr>");
                });

                valuesdiv.append(table);
                div.append(valuesdiv);
                filterdiv.append(div);
                $('body').append(filterdiv);

                var buttondiv = $("<div></div>").css({
                    textAlign: "right",
                    paddingTop: "5px",
                    paddingRight: "5px"
                }).show();

                var clearFilterBtn = $("<button>.</button>");
                clearFilterBtn.css({ height: "22px",
                    width: "25px",
                    border: "none",
                    background: "url(../../images/ClearFilter.png) center center no-repeat",
                    cursor: "pointer"
                });
                buttondiv.append(clearFilterBtn);

                var okBtn = $("<button>OK</button>");
                okBtn.css({ height: "22px", width: "70px" });
                buttondiv.append(okBtn);

                var cancelBtn = $("<button>Cancel</button>");
                cancelBtn.css({ height: "22px", width: "70px" });
                buttondiv.append(cancelBtn);

                cancelBtn.click(function () {
                    var container = $("#divFilterList");
                    container.detach();
                });

                okBtn.click(function () {

                    var SelectedDataCollection = {};

                    var ArrSelectedColumnsInfo = new Array(); // It hold all column selected info

                    var SelectedData = {};
                    SelectedData["ColumnName"] = colName;

                    var ArrSelectedValue = new Array();
                    $('#filterChkBox table tr td input:checked').each(function () {
                        ArrSelectedValue.push($(this).val());
                    });
                    SelectedData["SelectedValue"] = ArrSelectedValue;

                    var IsExist = false;
                    var alreadySelectedValueInfo;

                    if (hiddenControlValue.length > 0) {
                        var alreadySelectedValueInfo = jQuery.parseJSON(hiddenControlValue);
                        for (var index = 0; index < alreadySelectedValueInfo.DataCollection.length; index++) {
                            if (alreadySelectedValueInfo.DataCollection[index].ColumnName == colName) {
                                alreadySelectedValueInfo.DataCollection[index] = SelectedData;
                                SelectedDataCollection = alreadySelectedValueInfo;
                                IsExist = true;
                                break;
                            }
                        }
                    }

                    if (IsExist == false) {
                        var alreadySelectedValueInfo = new Array();
                        if (hiddenControlValue.length > 0) {
                            alreadySelectedValueInfo = jQuery.parseJSON(hiddenControlValue);
                            if (SelectedData.SelectedValue.length > 0)
                                alreadySelectedValueInfo["DataCollection"].push(SelectedData);
                            SelectedDataCollection = alreadySelectedValueInfo;
                        }
                        else {
                            alreadySelectedValueInfo.push(SelectedData);
                            SelectedDataCollection["DataCollection"] = alreadySelectedValueInfo;
                        }
                    }

                    $('#<%= hndSelectedValue.ClientID %>').val(JSON.stringify(SelectedDataCollection));
                    document.getElementById('<%= Refresh.ClientID %>').click();
                });

                clearFilterBtn.click(function () {
                    if (hiddenControlValue.length > 0) {
                        var alreadySelectedValueInfo = jQuery.parseJSON(hiddenControlValue);
                        for (var index = 0; index < alreadySelectedValueInfo.DataCollection.length; index++) {
                            if (alreadySelectedValueInfo.DataCollection[index].ColumnName == colName) {
                                alreadySelectedValueInfo.DataCollection[index].selectedValue = new Array();
                                $('#<%= hndSelectedValue.ClientID %>').val(JSON.stringify(alreadySelectedValueInfo));
                                break;
                            }
                        }
                    }
                    document.getElementById('<%= Refresh.ClientID %>').click();
                });

                filterdiv.append(buttondiv);

                var tblwidth = table.outerWidth();
                valuesdiv.css({ width: tblwidth + "px" }).show();

                div.css({
                    overflow: "auto",
                    textAlign: "left",
                    paddingLeft: "5px",
                    paddingTop: "1px",
                    marginLeft: "1px",
                    marginTop: "1px",
                    backgroundColor: "White",
                    border: "1px solid gray",
                    height: "207px",
                    width: "200px"
                }).show();

                var left = 0;
                if (pos.left + width - 200 - 10 < 0)
                    left = 0;
                else
                    left = pos.left + width - 200 - 10;

                filterdiv.css({
                    position: "absolute",
                    textAlign: "left",
                    backgroundColor: "white",
                    border: "1px solid black",
                    color: "black",
                    height: "243px",
                    width: "209px",
                    top: pos.top + height + 1 + "px",
                    left: left + "px"
                }).show();
            },
            error: function (xhr, status, error) {
                alert(error);
            }
        });

    });
    $(document).mouseup(function (e) {
        var container = $("#divFilterList");

        if (container.has(e.target).length === 0) {
            container.detach();
        }
    });
});
This implementation works well with the IE 9.0, Firefox 10.0 and Chrome 21.0 (other browsers I have not tested).

The screen output as below






Note: I have used the dropdown icon little different then Excel dropdown icon. I have included another image in the project which is same as Excel dropdown icon.

So to use the same icon, you can change the style sheet as below
th.filter
{
 background: url(../../images/ShowFilter.png) right bottom no-repeat;
 cursor:pointer;
}
The output of this stylesheet will look like as below

download the working copy of the source code in C# here and in VB here.

Important Note: I call the service with localhost port number 1609 (http://localhost:1609/Service1.svc) as the service published like that in my system. If your service running in different port, please change the port number. If you are downloading the source file and running, make sure the port number is same, or change the source code as per the new port number the service running. (To know the port number, you can run the application and get to know from localhost url or check in the Web tab in project properties.)

[Update 25 Dec 2014 06:30 PM]
Addition to this, I have made two more example which avoids the service call made to the server from the Javascript to get list of values while click the dropdown icon on each column. To avoid service call, there are various ways to do, but considering only two of the methods here -

  1. Using Generic Handler - HTTP handlers are used to intercept the HTTP calls to the server and respond instead of using aspx pages. For more information HTTP Handlers and HTTP Modules Overview.
    The example in the download link uses the Generic Handler to get the list of items for each column and respond the list to the Javascript.
  2. But looking at the previous example, there is a call happening from Client to server. Instead, I can get the list of values for all columns and keep it in variable (here hidden control) before loading the page. When Javascript required the list, it can get from the variable and use it without calling to the server again. This method will be useful while getting limited data from the db as the page load might take time. The download link is here.

The Next post on this same topic explains more functionalities.


68 Responses to “Filtering on each column in GridView as Excel Sheet functionality”

  • Anonymous says:
    21 January 2013 at 23:47

    Very good article! Thank you so much for sharing this precious information!
    Can you help me indicating where should i change the code so it can be addapted to another sql database?
    thank you soo much in advance.

  • Thirumalai M says:
    22 January 2013 at 11:09

    In ASPX Script - Change the columns in such a way the new db query field name adopted.
    In C# Code - Only in Bind Query. Change the Query, Line 21. (Line #22, not used. So you can remove it)
    DAO Class - Line 15, Line 24 and Line 28 to 38 (Change the query and the entity class collection.
    OrderView entity class - Entity class
    WCF Service code - Line 15 to 35
    Registering the WCF service - Line 3
    JQuery code - Line #14

    Change the namespace of yours whereever required. It must work properly. Pls let me know if you have any issues.

  • Anonymous says:
    22 January 2013 at 23:06

    It works perfectly! Thank you, but i have a problem! I need this project to do all this, but without the webservices part. Is it possible to do that?

  • Anonymous says:
    23 January 2013 at 16:52

    Can you answer me please, asap? it´s urgent... thank you!

  • Anonymous says:
    23 January 2013 at 21:15

    Help?!

  • Thirumalai M says:
    24 January 2013 at 11:18

    Hi, Sorry for late reply.

    It is possible. You need to consider using GenericHanlder or ICallbackEventHandler, which can be called from Javascript directly or you can even use another aspx page which can be consumed using XML HTTP AJAX call. But you need to change the JQuery code little bit to decode the response to JSON object and use it in the logic.

    I can point some of my post url, which can be useful -
    http://www.dotnettwitter.com/2011/06/implementing-icallbackeventhandler-in.html
    http://www.dotnettwitter.com/2011/08/calling-server-method-from-client-side.html
    http://www.dotnettwitter.com/2012/02/implementing-xml-http-ajax-with-json-in.html

    Pls let me know if you are not able to follow.

  • Anonymous says:
    24 January 2013 at 15:15

    Hello! thanks for your reply.
    I´m very new in this area and i´m having some difficulties following you´re advice.
    Can you make a sample code where i can see how to do the changes?
    Thank you soo much in advance :)

  • Thirumalai M says:
    24 January 2013 at 15:30

    Hi. I need some time. May be this week end. I will update you once done.

  • Anonymous says:
    24 January 2013 at 15:44

    Hello! Thank you soo much for you´re effort in trying to help me.
    I need this datagrid project ready asap, because i need to include it, in a project i´m developing for my company.
    Can we stay in contact by e-mail?
    My e-mail is arturjmpires@outlook.com
    I´ll be waiting for feedback. Thank you soo much!

  • Syed Hashmi says:
    16 March 2013 at 19:11

    Just one word “Awesome”

  • Rassique Mukkarram says:
    4 April 2013 at 14:04

    I have tried your article and downloaded it. Its working fine. But am not able to see any filter in any column, please let me know where am wrong ?

  • Rassique Mukkarram says:
    4 April 2013 at 14:05

    This is my app file

    http://www.filesend.net/download.php?f=a0c6dffe1731acdbe6dd08206cb2f2e7

  • Thirumalai M says:
    4 April 2013 at 16:51

    Hi Rassique Mukkarram, I dont see any difference in the code you provided. Can you tell me where is not working. The filtering query will be build in the code behind in BindData() function.

    When you select the filter and press OK, if the control not come to code behind there is some issue. If the control comes to code behind, check the SQL Query which dynamically built.

    Additionally, which browser you are using. Is any issue in the browser? Pls verify. Try to run the sample code in IE 8+, it must work.

  • Rassique Mukkarram says:
    4 April 2013 at 17:26

    Hi,
    Thanx for your quick reply.
    Am just able to see the grid like this.
    See the image:-
    http://tinypic.com/r/10n84nt/6

    I have checked in IE 9 and i have chrome Version 26.0.1410.43 m

    What should i do to get rid off this? Please help

  • This comment has been removed by the author.
    Rassique Mukkarram says:
    4 April 2013 at 17:33

    This comment has been removed by the author.

  • Rassique Mukkarram says:
    4 April 2013 at 17:59

    Also whenever i go and see the default.aspx.cs code, its shows tooltip error as the name grdViewOrders does not exist in the current content. And also its shows same tooltip message for hndSelectedValue this control.


    Can you send me your actual working code, which will help me alot.

  • Thirumalai M says:
    4 April 2013 at 18:43

    Hi Rassique Mukkarram,

    Please send a test mail to pm.thirumalai@gmail.com and send the code you had tested.

    Let me verify and get back to you.

  • Rassique Mukkarram says:
    4 April 2013 at 22:20

    Hi,

    Thank you so much for your kind help and support.

    I have mailed the code which i had tested

    Also i have posted a thread in asp.net forum.

    http://forums.asp.net/t/1895076.aspx/1?Filter+like+excel+in+asp+net

  • Saptarshi Ghosh says:
    12 April 2013 at 13:04

    Hi,

    The filtering doesn't work if i want to put fixed header scrolling in the grid view.

    how do i solve the problem?

    Thank you.

  • Thirumalai M says:
    15 April 2013 at 16:43

    Hi. Filtering doesn't impacts fixed header, you can send sample code to me. I can verify and let you know.

  • Priya says:
    15 May 2013 at 16:23

    Hi Thirumalai.. I am able to populate the grid.. but when i click on the arrow am getting a message as "Bad Request" from javascript.. can you please help me....

  • Thirumalai M says:
    15 May 2013 at 17:44

    Hopefully, you are referring the WCF service url wrongly. Please verify the aspx page in the asp:scriptmanager node on asp:servicereference. If suppose to be pointing to WCF service hosted. Try to access the WCF service from browser. If that works, then there might be issue in code.

    Try to verify the url of the service mentioned in aspx file. If that not works, take a screenshot and send it to me.

  • Priya says:
    17 May 2013 at 14:37

    Hi Thirumalai, can you please tell me where do I paste this script manager code? currently I have pasted that i the aspx page.. is it right? cos I dont see tht code in the downloaded version. hence please help me..


  • Thirumalai M says:
    19 May 2013 at 17:43

    Hi, I verified the code, there is no script manager is required. Unfortunately the page contains the script manager, which I removed today. You don't required to add script manager.

    In the Site.Master page, I added Jquery. Please verify the sample project if that runs, change the same way in your project.

  • Anonymous says:
    29 May 2013 at 09:09

    hi,i tried server side method instead of WCF service.but while returning array from server side seems to be problem for me.can u please tell me how to handle $.each(response.funtion()) because obj s null in my function..can u please help me in this asap

  • Thirumalai M says:
    7 October 2013 at 12:27

    If you use Server Side methods, you need to use AJAX call to get the values and that should return JSON values. If you are returning Arrays and not convertible to JSON, you need to use the object returning from server to array in the JQuery. You can provide some code how you call the server side and how you returning values to client side. So I can tell you something.

  • Raghu says:
    23 December 2013 at 15:02

    Hi Thirumalai

    I’ve customized to point it to our application DataSource. But before it can load the page in browser, it breaks with an error “Microsoft JScript runtime error: Object expected” in JQuery. I’ve saved the JQuery in Javascript file and referenced it in my application.

    When I hit continue, it displays the Gridview with data. Nothing happens when I click on the downarrow button on any column. In JQuery, I’ve pointed it to the my localhost WCF service hosted at line 14. I’m not a JQuery expert, could you please help me here. I’m using IE8. Not sure if that will cause any problem as you have stated you tested in IE9.

  • Kishor Kumar says:
    24 February 2014 at 12:10

    Hi Thirumalai,

    I am not able to download the source code from the link that you have mentioned.
    Please help me out giving an alternative link.

  • Anonymous says:
    18 April 2014 at 12:37

    Header text not showing

  • Anonymous says:
    24 April 2014 at 13:51

    Microsoft JScript runtime error: Object expected i get that error plz help

  • Anonymous says:
    20 November 2014 at 11:18

    Hi Thirumalai.
    i am getting error n "FilterValueSet" word please help

  • Anonymous says:
    4 December 2014 at 11:08

    Hi Thirumalai,
    thanks a lot..its working perfect.but i waant same thing without WCF service ..can u please provide complete code without WCF service..most of the people need this..thanks in advance
    waiting your reply

  • Anonymous says:
    5 December 2014 at 15:24

    Please I am Still waiting your response..plzzzzzzzzzz

  • Thirumalai M says:
    5 December 2014 at 16:06

    Hi,

    To get the data from c# code to Javascript, you required to user either of the one - service, http module / handler, call AJAX call to aspx, callback handler or hidden control etc,. You can try either of this method to do this. Just try the articles which are under "Client & Server conversation in ASP.NET" heading under "Series" page on this blog.

    I am bit busy with the work. I will try in this week end and post.

    Meanwhile, you can try

  • Anonymous says:
    18 December 2014 at 15:57

    Hi Thirumalai,
    thanks a lot..its working perfect.but i waant same thing without WCF service ..can u please provide complete code without WCF service..most of the people need this..thanks in advance
    still waiting your reply

  • Thirumalai M says:
    25 December 2014 at 18:44

    updated the examples. pls look at the end of the post.

    Sorry for the delay.

  • Anonymous says:
    6 January 2015 at 15:55

    Hi thirumalai
    we are geetin error WEB PAGE NOT AVILABLE when Click the link
    plz help

  • Anonymous says:
    8 January 2015 at 17:01

    Hi thirumalai
    we are geetin error WEB PAGE NOT AVILABLE when Click the link
    I M STILL WAITING
    plz help

  • Thirumalai M says:
    11 January 2015 at 13:01

    I hope WEB PAGE NOT AVILABLE is not error because of the code from this example. Please remove the code written as per example provided here and test once. If the same error comes, issue from IIS server / port number etc., otherwise need to validate where the error comes from this example. As I am not getting the error I am unable to get any solution.

  • Anonymous says:
    12 January 2015 at 11:56

    When I click DownLoad Link in your post to see the new code then i can not open/download new code
    plz help

  • Anonymous says:
    12 January 2015 at 12:00

    I am not able to download the source code from the link that you have mentioned(updated on 25 Dec 2014).
    Please help me out giving an alternative link.

  • Thirumalai M says:
    12 January 2015 at 13:08

    Could you pls send me a test mail to me pm.thirumalai@gmail.com, will send u back.

  • Anonymous says:
    13 January 2015 at 17:53

    I have sent a test mail.my email id start with tanvir------@yahoo.com
    waiting your response
    thanks
    tanvir

  • Anonymous says:
    19 January 2015 at 17:05

    Sir
    Please send the database script

  • Thirumalai M says:
    21 January 2015 at 01:18

    Pls download from below link

    http://www.dotnettwitter.com/2011/05/northwind-database-scripts-for-sql.html

  • Anonymous says:
    17 March 2015 at 15:35

    Hi. I got a problem in clicking the header..javascript message says "not found".
    can you help me on this? It's very urgent.. thank you

  • Anonymous says:
    18 March 2015 at 08:06

    Hi. why my handler was not called in javascript? I used GenericHandler.. It's alwasy go to error and not on success.

  • Anonymous says:
    11 June 2015 at 20:17

    Hej
    How to solve problems with ÆØÅ, if use ShipCity = Århus in dropdown, all other dropdown is empty. I have many Fieldnames and values with ÆØÅ. Please Help

  • Jonata Biondi says:
    10 July 2015 at 20:10

    I get error 400 bad request in service1.svc.vb class

    what means ?

  • Raju Bisht says:
    19 August 2015 at 19:39

    can you please provide me the above solution code at my email id raju.bisht1984@gmail.com

  • This comment has been removed by the author.
    Dilip Kumar says:
    19 October 2015 at 20:13

    This comment has been removed by the author.

  • Anonymous says:
    7 November 2015 at 12:55

    Hi,
    I am getting error in the below line
    FilterValueSet[] GetDistinctValue(string strColumnName);
    please suggest.

  • RISHABH says:
    1 December 2015 at 13:38

    Hi Can you please tell me how to apply sorting in this filtered grid?

  • Thirumalai M says:
    3 January 2016 at 20:08

    You need to combine sorting and filtering functionality together in the grid or you can sort the grid by default ascending while filter the text and showing.

  • vaishali a says:
    4 May 2016 at 12:59

    Hi Thirumalai...I used your code....but unfortunately unable to see or click the filter icon...any idea what can be the issue?? I am working on oracle 11g..so I modified the queries accordingly....

  • Baishali Aich says:
    18 May 2016 at 16:24

    hey..hii...this time it worked for me...thank you a lot....I have a question though....how can we session data from .aspx.cs page to .svc.cs file??

  • Thirumalai M says:
    20 May 2016 at 19:17

    hi .svc is used for Services which should not be using session data. The Service layer should always be a stateless session. If you need some session data to be used in service operation, you can pass with the data to the service call..

  • Baishali Aich says:
    23 May 2016 at 15:46

    hii...got another question...when I am placing the jquery code in separate file and linking the code externally, it gives error "Cannot read property 'click' of null" on this line :
    document.getElementById('<%= Refresh.ClientID %>').click();

    Any reason why?
    I want to use the same jquery for filters in multiple pages and I have modified queries accordingly. While using filter, the dropdown values are visible but click operation is giving error from external file.

  • Thirumalai M says:
    24 May 2016 at 19:33

    Hi. Once the page rendered into browser, all the Javascript are considered as single page. No differences on it. So you can call one file to another if that is referenced in the page which is rendering. There should be some other issue. Pls look at that.

    If required, you can package an example page which I can validate and come back.

  • Baishali Aich says:
    27 May 2016 at 16:01

    How to package the example page?
    Kindly help with steps.

  • Baishali Aich says:
    27 May 2016 at 16:29

    Hii, I did some more exploration and found that '<%= Refresh.ClientID %>' is is not finding the control when jscript is referenced externally. But when on same aspx page, the controls are found. Then '<%= Refresh.ClientID %>' changes to 'MainContent_Refresh'. In my case, it is not changing. I use your example only referencing jscript externally. It is giving the same issue.

  • Thirumalai M says:
    27 May 2016 at 19:09

    I mean package is to create a small project and repro this issue as what you have done in the actual project and send me the code. So that I can understand what is the issue.

    <%= Refresh.ClientID %> will provide the client id of the control. If you can find the actual client id by right click and view source, you can even directly give the value instead of <%= %> like document.getElementById('masterpage_cl001_Refresh').

    Other wise pls send me a mail the structure of the files and how you are referencing the file one to another. Where the code is been written. Let me try to think what could be an issue and send you the solution if I can.

  • Baishali Aich says:
    31 May 2016 at 15:40

    Hi, I think javascript is not able to identify <%= %> syntax when called from external source. I will follow your suggestion of finding actual client of the control and use it directly.

  • Jan Cimler says:
    16 June 2016 at 12:42

    I downloaded your example and everthin is all right.
    If I don't use code file ( default.aspx.vb ) but code will be put in to default.aspx then Visual studio did not knows about class SelectedDataCollection which is defined in javascript in file default.apsx.
    I checked reference system.web.Extension and namespace "System.Web.Script.Serialization". I did not why is it so.

  • Unknown says:
    24 June 2016 at 00:17

    please provide project files so that i coulud download and use it

  • Unknown says:
    22 October 2016 at 16:34

    this code is not working.

  • krishna sheladiya says:
    22 October 2016 at 18:24

    Hi Thirumalai.. I am able to populate the grid.. but when i click on the arrow am getting a message as "Bad Request" from javascript.. can you please help me....

  • John D says:
    10 March 2017 at 19:13

    Hi, has anyone modified this to include a "Select all" option when the arrow is clicked at the top of each column?

Post a Comment