Solved

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

Posted on 2011-03-02
11
530 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 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