Solved

How to sort gridview when header column is clicked based on multiple queries

Posted on 2011-03-02
11
526 Views
Last Modified: 2013-11-08
My program consists of two buttons named as Show Results and search. when buttons are clicked data is displayed in Grid view.
Now i need to sort the gridview either asc or desc when header columns are clicked. The above two buttons consists of different queries.

Please provide the solution for sorting grid with multiple queries.


Help would be appreciated.
0
Comment
Question by:rosary99
  • 6
  • 3
11 Comments
 
LVL 9

Accepted Solution

by:
gdupadhyay earned 250 total points
Comment Utility

As you explained, You can populate the data in grid view with any default sorting.

Now after filling the data in grid view, you need to write event for sorting on grid view.
See the code below:


Note:
For more information about how to build sorting in gridview, please see below URL

http://stackoverflow.com/questions/702600/sorting-and-paging-with-gridview-asp-net

http://forums.asp.net/p/956540/1177923.aspx

Let me know if you are looking different.

<asp:GridView ID="gridView" OnSorting="gridView_Sorting" runat="server" />



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();
   }
}

Open in new window

0
 

Author Comment

by:rosary99
Comment Utility
gdupadhyay,


Thanks for the response..

Sorting is not working with the solution you mentioned.
0
 

Author Comment

by:rosary99
Comment Utility
For better picture, Here is my .cs code


  protected void btnResults_Click(object sender, EventArgs e)
        {

            int locationID = 0;
            string courseTitle = " ";
            string facultyname = " ";
            string roombuilding = " ";
            string courseType = " ";
            string queryresult = "SELECT CourseType, CourseTitle, FacultyName,  RoomBuilding From CourseSchedule_fall10 WHERE ";
            if (ddlType.SelectedIndex != 0)
            {

                courseType = ddlType.SelectedValue;
                queryresult += "coursetype='" + courseType + "'";
                queryresult += " AND ";
            }
            if (ddlLocation.SelectedIndex != 0)
            {
                locationID = Convert.ToInt32(ddlLocation.SelectedValue);
                queryresult += "locationID='" + locationID + "'";
                queryresult += " AND ";
            }

            if (ddlCourse.SelectedIndex != 0)
            {
                courseTitle = ddlCourse.SelectedValue;
                queryresult += "coursetitle ='" + courseTitle + "'";
                queryresult += " AND ";
            }

            if (ddlInstruct.SelectedIndex != 0)
            {
                facultyname = ddlInstruct.SelectedItem.ToString();
                queryresult += "FacultyName = '" + facultyname + "'";
                queryresult += " AND ";
            }
            if (ddlRoom.SelectedIndex != 0)
            {
                roombuilding = ddlRoom.SelectedItem.ToString();
                queryresult += "RoomBuilding = '" + roombuilding + "'";
                queryresult += " AND ";
            }

            if (ddlType.SelectedIndex != 0)
            {
                courseType = ddlType.SelectedValue;
                queryresult += "coursetype='" + courseType + "'";


            }
            SqlConnection con = new SqlConnection(strConnection);
            con.Open();
            //    string query = "SELECT  CourseType, CourseTitle, FacultyName,  RoomBuilding From CourseSchedule WHERE coursetype='" + courseType + "' AND coursetitle ='" + courseTitle + "' OR FacultyName = '" + facultyname + "' OR RoomBuilding = '" + roombuilding + "' ";
            SqlCommand cmd = new SqlCommand(queryresult, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
     
            con.Close();

            if (ds.Tables[0].Rows.Count != 0)
            {
                dgdShowResults.DataSource = ds;

                dgdShowResults.DataBind();
            }
            else
            {

                ShowNoResultFound(ds.Tables[0], dgdShowResults);
            }
        }
       
        protected void SearchButton_Click(object sender, EventArgs e)
        {
            int locationID = Convert.ToInt32(ddlLocation.SelectedValue);
            SqlConnection con = new SqlConnection(strConnection);
            con.Open();
            string query = "SELECT CourseType, CourseTitle, FacultyName, RoomBuilding From CourseSchedule where (CourseType='" + txtsearch.Text + "' or CourseTitle='" + txtsearch.Text + "' or FacultyName='" + txtsearch.Text + "' or RoomBuilding='" + txtsearch.Text + "')";
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            txtsearch.Text = "";
            da.Fill(ds);
         //   DataView dv = ds.Tables[0].DefaultView;
         //   dv.Sort = "FacultyName ASC";
            con.Close();

            if (ds.Tables[0].Rows.Count != 0)
            {
                dgdShowResults.DataSource = ds;

                dgdShowResults.DataBind();
            }
            else
            {
                ShowNoResultFound(ds.Tables[0], dgdShowResults);
            }
        }


        private void ShowNoResultFound(DataTable source, GridView gv)
        {
            source.Rows.Add(source.NewRow()); // create a new blank row to the DataTable
            //Bind the DataTable which contain a blank row to the GridView
            gv.DataSource = source;
            gv.DataBind();
            // Get the total number of columns in the GridView to know what the Column Span should be
            int columnsCount = gv.Rows[0].Cells.Count;
            gv.Rows[0].Cells.Clear();// clear all the cells in the row
            gv.Rows[0].Cells.Add(new TableCell()); //add a new blank cell
            gv.Rows[0].Cells[0].ColumnSpan = columnsCount; //set the column span to the new added cell

            //You can set the styles here
            gv.Rows[0].Cells[0].HorizontalAlign = HorizontalAlign.Center;
            // gv.Rows[0].Cells[0].ForeColor = System.Drawing.Color.Gold;
            gv.Rows[0].Cells[0].Font.Bold = true;
            //set No Results found to the new added cell
            gv.Rows[0].Cells[0].Text = "No Courses Found";
        }
        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_Sorting(object sender, GridViewSortEventArgs e)
        {
            DataTable dataTable = dgdShowResults.DataSource as DataTable;

            if (dataTable != null)
            {
                DataView dataView = new DataView(dataTable);
                dataView.Sort = e.SortExpression + " " + ConvertSortDirectionToSql(e.SortDirection);

                dgdShowResults.DataSource = dataView;
                dgdShowResults.DataBind();
            }
        }


.aspx code

<asp:GridView ID="dgdShowResults"  runat="server"   AllowSorting="True"
             OnSorting="gridView_Sorting" />
0
 
LVL 9

Expert Comment

by:gdupadhyay
Comment Utility
Did you add  OnSorting="gridView_Sorting"  with grid view.
It should work if you added. I posted sample code, you might need to update according to your requirement.  

Please debug and let me know the error message.
0
 

Author Comment

by:rosary99
Comment Utility
Yes i did. When i debug there are no errors.
I have posted the code. Please check to see if any errors.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 9

Expert Comment

by:gdupadhyay
Comment Utility
Its working but sort direction is always asc. You need to change the direction on every click.

Please follow the below URL for more detail.

http://stackoverflow.com/questions/250037/gridview-sorting-sortdirection-always-ascending

It will work.
0
 
LVL 9

Expert Comment

by:gdupadhyay
Comment Utility
0
 
LVL 9

Expert Comment

by:gdupadhyay
Comment Utility
I suggested correct solution. Author just need to change the direction for sort every call.
I was helped to identify the issue and send several URL to know more about sorting expression asc and desc and how to change the direction etc.

Without asking any further question and not specifying any good reason to close this question, It is not fair.

0
 
LVL 9

Expert Comment

by:gdupadhyay
Comment Utility
I like to accept ID:35019252 as answer.
0
 

Expert Comment

by:WhackAMod
Comment Utility
Starting closing process on behalf of the asker.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

728 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

13 Experts available now in Live!

Get 1:1 Help Now