Solved

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

Posted on 2011-03-02
11
531 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

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

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

726 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