Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Programmatically creating filters on GridView

Avatar of rss2
rss2 asked on
.NET ProgrammingASP.NETMicrosoft SQL Server 2008
5 Comments1 Solution2272 ViewsLast Modified:
Hi Experts,

I have an aspx page whereby I call a stored procedure (SQL) and bind the results to a gridview. I am doing all the paging and sorting in the code behind. Now I would like to do filtering in the code behind.

Could you please tell me how to:
1. Create a filter drop down list in the code behind underneath the first row of the dataset (i.e. the header column names) returned by the stored procedure (which is just a select * on a table)..

2. Re-bind the gridview after the filter drop-down list has been changed and a value selected.

3. Also and example  of the above, but uing a textbox and just filtering the gridview on that column's textbox value.. please.

Thank you very much. I'm really stuck.

rss2

ASPX:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Collateral_Management_Web._Default" %>

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

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Collateral Management Web - Securities Update Form</title>
    <link href="Collateral_Management_Sharepoint.css" rel="Stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <div class="menu">
        <asp:GridView ID="GridView1" runat="server"
         AllowSorting="True"
         OnSorting="gvSamples_OnSorting"
         Font-Names="Arial"
         GridLines="None"
         CellPadding="0" ForeColor="#333333" Width="4000px" CssClass="menu">
         <RowStyle BackColor="#F7F6F3" ForeColor="#333333" Width="0.5" />
        <FooterStyle BackColor="red" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="red" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <HeaderStyle Width="300" BackColor="red" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#999999" />
     <AlternatingRowStyle BackColor="White" Font-Names="Arial" ForeColor="#284775" />
        </asp:GridView>
        <a href="" id="FirstPageLink" runat="server">First</a>
        <a href="" id="PrevPageLink" runat="server">Prev</a>
        <asp:Label id="lblGoToPage" Text="Go to page:" runat="server"></asp:Label>
        <asp:TextBox ID="tbGoToPage" runat="server" Width="40"></asp:TextBox>
        <asp:Button ID="btnGo" runat="server" Text="GO" onclick="btnGo_Click" />
        <a href="" id="NextPageLink" runat="server">Next</a>
        <a href="" id="LastPageLink" runat="server">Last</a>
        <asp:Label ID="CurrentPage" runat="server"></asp:Label>
        <asp:Button ID="btnExportGrid" runat="server" Text="Export to Excel" OnClick="BtnExportGrid_Click" />
        <asp:Label ID="lblExportTimeMessage" runat="server" Text="" Font-Size="X-Small"></asp:Label>
    </div>
    </form>
</body>
</html>

C#:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;
using System.Data;
using System.Web.Security;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

namespace Collateral_Management_Web
{
    public partial class _Default : System.Web.UI.Page
    {
        private int pageCount = 0;
        private int pageIndex = 0;
        private int pageSize = 30;
        private int pageTotalRows = 0;

        string SortField = "";
        /*
         * Maintain the direction of the sort with the ViewState, which is
         * very handy for variables specific to this page. Using ViewState is
         * analogous to having 'hidden' form fields to store data. 
         * This takes 3 values, asc, desc and flip to invert the current value
         */
        string SortDirection
        {
            get
            {
                if (ViewState["SortDirection"] == null)
                {
                    ViewState["SortDirection"] = "ASC";
                }
                return ViewState["SortDirection"].ToString();
            }
            set
            {
                string s = SortDirection;

                if (value == "flip")
                {
                    s = s == "ASC" ? "DESC" : "ASC";
                }
                else
                {
                    s = value;
                }

                ViewState["SortDirection"] = s;
            }
        }


        protected void Page_Load(object sender, EventArgs e)
        {
            if (Request["page"] == null)
            {
                pageIndex = 1;
            }
            else
            {
                pageIndex = Int16.Parse(Request["page"]);
            }

            if (Session["pageCount"] == null)
            {
                // Get the total number of pages in the table
                using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["GBMReportsConnString"].ConnectionString))
                {
                    object returnvalue;
                    cn.Open();
                    SqlCommand cm = cn.CreateCommand();
                    cm.CommandText = "usp_hsbc_liquidity_collateral_mngmt_eligible_assets_web_getTotalpages";
                    cm.CommandType = CommandType.StoredProcedure;
                    SqlParameter totalrows = cm.Parameters.Add("@totalRows", SqlDbType.Int);
                    totalrows.Direction = ParameterDirection.Output;
                    returnvalue = cm.ExecuteScalar();
                    pageCount = Convert.ToInt32(totalrows.Value.ToString());//Convert.ToInt16(cm.ExecuteScalar());
                    pageTotalRows = pageCount;
                    pageCount = pageCount / pageSize;

                    Session["pageCount"] = pageCount;
                    Session["pageTotalRows"] = pageTotalRows;

                }


                }
                else{
                    pageCount = (int)Session["pageCount"];

                    GetCurrentPageData();
                }


            try
            {
                if (Page.IsPostBack == false)
                {
                    GridViewDataLoad();
                    UpdatePagination();
                }
            }
            catch (Exception ex)
            {
                string ErrorMessage = "An error occurred when loading this page.";
                ErrorMessage += Environment.NewLine;
                ErrorMessage += Environment.NewLine;
                ErrorMessage += ex.Message;
            }

            // display the error message somewhere

            // Log the error
        }

        private void GetCurrentPageData()
        {
            if (pageIndex > pageCount || pageIndex < 1) return;

            SqlDataSource sds = new SqlDataSource();
            sds.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["GBMReportsConnString"].ConnectionString;
            sds.SelectCommand = "usp_hsbc_liquidity_collateral_mngmt_eligible_assets_web";
            sds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
            sds.SelectParameters.Add("pageIndex", pageIndex.ToString());
            sds.SelectParameters.Add("pageSize", pageSize.ToString());
            
            GridView1.DataSource = sds;
            GridView1.DataBind();

            UpdatePagination();


        }

        private void UpdatePagination()
        {
            FirstPageLink.HRef = "Default.aspx?page=1";
            PrevPageLink.HRef = "Default.aspx?page=" + (pageIndex - 1);
            NextPageLink.HRef = "Default.aspx?page=" + (pageIndex + 1);
            LastPageLink.HRef = "Default.aspx?page=" + pageCount;

            // On first page
            if (pageIndex <= 1)
            {
                FirstPageLink.HRef = String.Empty;
                PrevPageLink.HRef = String.Empty;
            }

            // On last page
            else if (pageIndex >= pageCount)
            {
                NextPageLink.HRef = String.Empty;
                LastPageLink.HRef = String.Empty;
            }

            CurrentPage.Text = "Page " + pageIndex.ToString() + " of " + pageCount.ToString(); 
        }
        private void GridViewDataLoad()
        {
            string spstr = "usp_hsbc_liquidity_collateral_mngmt_eligible_assets_web " + pageIndex.ToString() + ", " + pageSize.ToString();
            string connectionString = ConfigurationManager.ConnectionStrings["GBMReportsConnString"].ConnectionString;
            System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);

            System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(spstr, connection);
            System.Data.DataSet ds = new System.Data.DataSet();

            da.Fill(ds);
            Session["ds"] = ds;

            GridView1.DataSource = ds;
            GridView1.DataBind();



        }


        protected void btnGo_Click(object sender, EventArgs e)
        {
            if (tbGoToPage.Text != "")
            {
                int goToPage = Convert.ToInt32(tbGoToPage.Text);

                if (goToPage <= pageCount)
                {
                    pageIndex = goToPage;
                    GridViewDataLoad();
                    UpdatePagination();
                }
            }
        }

        protected void BtnExportGrid_Click(object sender, EventArgs args)
        {
            lblExportTimeMessage.Text = "Export to excel could take up to 10 minutes..";
            pageIndex = 1;
            //pageSize = (int)Session["pageTotalRows"];
            GridViewDataLoad();
            //  pass the grid that for exporting ...
            GridViewExportUtil.Export("Collateral_Management_Securities.xls", GridView1);

            if (Request["page"] == null)
            {
                pageIndex = 1;
            }
            else
            {
                pageIndex = Int16.Parse(Request["page"]);
            }

            pageSize = 30;
            lblExportTimeMessage.Text = "";

        }


        private String GridViewSortExpression
        {
            get { return ViewState["SortExp"] as String ?? ""; }
            set { ViewState["SortExp"] = value; }
        }

        private String GridViewSortDirection
        {
            get { return ViewState["SortDir"] as String ?? ""; }
            set { ViewState["SortDir"] = value; }
        }

        

        protected DataView SortTable(DataTable dt)
        {
            DataView dv = new DataView();
            if (dt != null)
            {
                dv = new DataView(dt);
                if (GridViewSortExpression != "")
                {
                    dv.Sort = GridViewSortExpression + " " +
                    GridViewSortDirection;
                }
            }
            return dv;
        }

        protected void gvSamples_OnSorting(object sender, GridViewSortEventArgs e)
        {
            SortField = e.SortExpression;
            /*
             * The GridViewSortEventArgs argument has a 'SortDirection' element but
             * its not maintained by the control. I think its probably only used when
             * you are using a SQL or object datasource, but I'm not sure. In any
             * even we are responsible for remembering what the sort direction is. 
             * Here we just flip it from ASC to DESC or vice-versa each time the 
             * column is sorted.
             */
            SortDirection = "flip";
        }
        protected override void OnPreRender(EventArgs e)
        {
            base.OnPreRender(e);
            /*
             * In a real application we'd be doing a SQL call with with the 
             * SortField and SortDirection being used in the SELECT statement. 
             * Here we get some sample data and use the very cool DataView 
             * class to perform a sort on the DataTable containing the data.
             */

            //pageIndex = 1;
            //pageSize = (int)Session["pageTotalRows"];

            //string spstr = "usp_hsbc_liquidity_collateral_mngmt_eligible_assets_web " + pageIndex.ToString() + ", " + pageSize.ToString();
            //string connectionString = ConfigurationManager.ConnectionStrings["GBMReportsConnString"].ConnectionString;
            //System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);

            //System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(spstr, connection);
            //System.Data.DataSet ds = new System.Data.DataSet();

            //da.Fill(ds);

            //GridViewDataLoad();


            DataSet ds = (DataSet)Session["ds"];
            DataTable dt = ds.Tables[0];
            DataView dv = new DataView(dt);
            if (SortField != "") dv.Sort = SortField + " " + SortDirection;
            /*
             * Load up datagrid.
             */
            GridView1.DataSource = dv;
            GridView1.DataBind();
            /*
             * Add an up/down arrow to the sort column
             */
            //AddSortImage();
        }



    }
}
ASKER CERTIFIED SOLUTION
Avatar of Kumaraswamy R
Kumaraswamy RFlag of India image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answers