Solved

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

Posted on 2011-03-02
11
529 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
ID: 35019252

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
ID: 35019358
gdupadhyay,


Thanks for the response..

Sorting is not working with the solution you mentioned.
0
 

Author Comment

by:rosary99
ID: 35019400
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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 9

Expert Comment

by:gdupadhyay
ID: 35019404
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
ID: 35019452
Yes i did. When i debug there are no errors.
I have posted the code. Please check to see if any errors.
0
 
LVL 9

Expert Comment

by:gdupadhyay
ID: 35020446
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
ID: 35020471
0
 
LVL 9

Expert Comment

by:gdupadhyay
ID: 35166529
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
ID: 35215189
I like to accept ID:35019252 as answer.
0
 

Expert Comment

by:WhackAMod
ID: 35275404
Starting closing process on behalf of the asker.
0

Featured Post

How our DevOps Teams Maximize Uptime

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

Question has a verified solution.

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

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

685 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