Data not sorting when paging

Hi I am trying to sort the data in ASC and DESC order. When I put a breakpoint, I see that the data is being sorted fine. The problem occurs when paging through the GridView. The data is not sorted anymore when I go to next page. Can someone please suggest what I need to fix or add to my code? Thanks is advance.

ASPX Page:

"GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None" PageSize="20"
OnPageIndexChanging="GridView1_PageIndexChanging" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting" OnSorting="GridView1_Sorting">
"White" ForeColor="#284775" />
<%-- COMMENT ID --%>
"false" ID="lblID" runat="server" Text='<%# Bind("BRD_COMMENT_ID") %>'>
"txtID" Visible="false" runat="server" Text='<%# Bind("BRD_COMMENT_ID") %>'>
<%-- COMMENT YEAR --%>
"Year">
"lblCommentYear" Visible="true" runat="server" Text='<%# Bind("METRIC_YEAR") %>'>
"txtCommentYear" runat="server" Text='<%# Bind("METRIC_YEAR") %>'>
<%-- COMMENT MONTH --%>
"Month">
"lblCommentMonth" Visible="true" runat="server" Text='<%# Bind("METRIC_MONTH_TXT") %>'>
"txtCommentMonth" runat="server" Text='<%# Bind("METRIC_MONTH_TXT") %>'>
<%-- COMMENT TAB --%>
"Tab">
"lblCommentTab" Visible="true" runat="server" Text='<%# Bind("TAB_NAME") %>'>
"txtCommentTab" runat="server" Text='<%# Bind("TAB_NAME") %>'>
<%-- COMMENT SECTION --%>
"Section">
"lblCommentSection" Visible="true" runat="server" Text='<%# Bind("SECTION_NAME") %>'>
"txtCommentSection" runat="server" Text='<%# Bind("SECTION_NAME") %>'>
<%-- COMMENT ORDER --%>
"Order" SortExpression="COMMENT_ORDER">
"lblCommentOrder" runat="server" Text='<%# Bind("COMMENT_ORDER") %>'>
"txtCommentOrder" Width="20px" runat="server" Text='<%# Bind("COMMENT_ORDER") %>'>
"rfvUpdateCommentOrder" ValidationGroup="UPDATE" runat="server"
ErrorMessage="Comment order is required." ForeColor="Brown" Display="Dynamic" ControlToValidate="txtCommentOrder">
<%-- COMMENT TEXT --%>
"Center" HeaderText="Comments">
"lblComment" runat="server" Text='<%# Bind("COMMENT_TEXT") %>'>
"txtEditComment" TextMode="MultiLine" Width="400px" Height="30px" MaxLength="300" runat="server" Text='<%# Bind("COMMENT_TEXT") %>'>
"rfvUpdateCommentText" ValidationGroup="UPDATE" runat="server"
ErrorMessage="Please enter a comment" ForeColor="brown" ControlToValidate="txtEditComment">
"lnkEdit" runat="server" CommandName="Edit" Text ="Edit">
"return confirm('Are you sure you want to delete this comment?');" ID ="lnkDelete" runat="server" CommandName="Delete" Text ="Delete">
"btn_Update" runat="server" Text="Update" CommandName="Update"/>
"btn_Cancel" runat="server" Text="Cancel" CommandName="Cancel"/>
"#999999" />
"#5D7B9D" Font-Bold="True" ForeColor="White" />
"Left" BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
"#284775" ForeColor="White" HorizontalAlign="Center" />
"#F7F6F3" ForeColor="#333333" />
"#E2DED6" Font-Bold="True" ForeColor="#333333" />
"#E9E7E2" />
"#506C8C" />
"#FFFDF8" />
"#6F8DAE" />

ASPX.CS Page:

namespace ChngMgmt_Comments_and_Legends
{
public partial class Comments : System.Web.UI.Page
{
private static readonly ILog logger = LogManager.GetLogger("LoggerName");
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
GridView1.DataSource = RefreshGrid();
GridView1.DataBind();
}
}
public DataTable RefreshGrid()
{
//Gets all the data in grid view for the following columns
String queryStr= "SELECT BRD_COMMENT_ID, METRIC_YEAR, METRIC_MONTH_TXT, TAB_NAME, SECTION_NAME, COMMENT_ORDER, COMMENT_TEXT FROM CHNGMETRICS.VW_BRD_COMMENT WHERE 1=1";
//If Year drop down is not null the data gets filtered by year
if (DropDownYear.SelectedValue != "")
{
queryStr += " AND METRIC_YEAR = :METRIC_YEAR";
}
//If Month drop down in not null the data gets filtered by month.
if (DropDownMonth.SelectedValue != "")
{
queryStr += " AND METRIC_MONTH = :METRIC_MONTH";
}
//if section drop down is not null the data gets filtered by section name
if (DropDownSectionName.SelectedValue != "")
{
queryStr += " AND SECTION_NAME = :SECTION_NAME";
}
//if TAB drop down is not null the data gets filtered by TAB Name
if (DropDownTabName.SelectedValue != "")
{
queryStr += " AND TAB_NAME = :TAB_NAME";
}
//Added parameters for Year,Month,Section,Tab drop downs
OracleCommand command = new OracleCommand(queryStr);
if (DropDownYear.SelectedValue != "")
{
command.Parameters.AddWithValue("METRIC_YEAR",DropDownYear.SelectedValue);
}
if (DropDownMonth.SelectedValue != "")
{
command.Parameters.AddWithValue("METRIC_MONTH", DropDownMonth.SelectedValue);
}
if (DropDownSectionName.SelectedValue != "")
{
command.Parameters.AddWithValue("SECTION_NAME", DropDownSectionName.SelectedValue);
}
if (DropDownTabName.SelectedValue != "")
{
command.Parameters.AddWithValue("TAB_NAME", DropDownTabName.SelectedValue);
}
DataTable GridComments = Utility.GetData(command);
return GridComments;
}
//Drop Downs are auto post back so each time user selects a selection the Grid View needs to refresh
protected void DropDownYear_SelectedIndexChanged(object sender, EventArgs e)
{
RefreshGrid();
}
protected void DropDownMonth_SelectedIndexChanged(object sender, EventArgs e)
{
RefreshGrid();
}
protected void DropDownSectionName_SelectedIndexChanged(object sender, EventArgs e)
{
RefreshGrid();
}
protected void DropDownTabName_SelectedIndexChanged(object sender, EventArgs e)
{
RefreshGrid();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
RefreshGrid();
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
DataTable dt = RefreshGrid();
if (ViewState["Sort"] != null)
{
dt.DefaultView.Sort = (string)ViewState["Sort"];
}
GridView1.DataSource = dt;
GridView1.DataBind();
}
// Grid View updating is enabled so users can edit Comments and order
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
logger.Debug("Debugging update statement");
logger.Error("Catching error(s) while updating data");
if (Page.IsValid)
{
Label id = GridView1.Rows[e.RowIndex].FindControl("txtID") as Label;
TextBox comment = GridView1.Rows[e.RowIndex].FindControl("txtEditComment") as TextBox;
TextBox order = GridView1.Rows[e.RowIndex].FindControl("txtCommentOrder") as TextBox;
var updateQuery = ("UPDATE CHNGMETRICS.VW_BRD_COMMENT SET COMMENT_ORDER = :COMMENTORDER , COMMENT_TEXT = :COMMENTTEXT WHERE BRD_COMMENT_ID = :BRDCOMMENTID ");
OracleCommand command = new OracleCommand(updateQuery);
//Add PARAMETERS FOR UPDATE STATEMENT
command.Parameters.Add(new OracleParameter("COMMENTORDER", order.Text));
command.Parameters.Add(new OracleParameter("COMMENTTEXT", comment.Text));
command.Parameters.Add(new OracleParameter("BRDCOMMENTID", Convert.ToInt32(id.Text)));
GridView1.EditIndex = -1;
Utility.Exec(command);
RefreshGrid();
//Year
DropDownYear.Items.Clear();
DropDownYear.DataBind();
DropDownYear.ClearSelection();
DropDownYear.Items.Insert(0, new ListItem("Select year", ""));
DropDownYear.SelectedIndex = -1;
//Month
DropDownMonth.Items.Clear();
DropDownMonth.DataBind();
DropDownMonth.ClearSelection();
DropDownMonth.Items.Insert(0, new ListItem("Select month", ""));
DropDownMonth.SelectedIndex = -1;
//Tab
DropDownTabName.Items.Clear();
DropDownTabName.DataBind();
DropDownTabName.ClearSelection();
DropDownTabName.Items.Insert(0, new ListItem("Select tab name", ""));
DropDownTabName.SelectedIndex = -1;
//Section
DropDownSectionName.Items.Clear();
DropDownSectionName.DataBind();
DropDownSectionName.ClearSelection();
DropDownSectionName.Items.Insert(0, new ListItem("Select section name", ""));
DropDownSectionName.SelectedIndex = -1;
}
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
RefreshGrid();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
logger.Debug("Debugging delete statement");
logger.Error("Catching error(s) while deleting data");
Label id = GridView1.Rows[e.RowIndex].FindControl("lblID") as Label;
var deleteQuery = ("DELETE FROM CHNGMETRICS.VW_BRD_COMMENT WHERE BRD_COMMENT_ID = :BRDCOMMENTID ");
OracleCommand command = new OracleCommand(deleteQuery);
//Add PARAMETERS FOR DELETE STATEMENT
command.Parameters.Add(new OracleParameter("BRDCOMMENTID", Convert.ToInt32(id.Text)));
Utility.Exec(command);
RefreshGrid();
//Year
DropDownYear.Items.Clear();
DropDownYear.DataBind();
DropDownYear.ClearSelection();
DropDownYear.Items.Insert(0, new ListItem("Select year", ""));
DropDownYear.SelectedIndex = -1;
//Month
DropDownMonth.Items.Clear();
DropDownMonth.DataBind();
DropDownMonth.ClearSelection();
DropDownMonth.Items.Insert(0, new ListItem("Select month", ""));
DropDownMonth.SelectedIndex = -1;
//Tab
DropDownTabName.Items.Clear();
DropDownTabName.DataBind();
DropDownTabName.ClearSelection();
DropDownTabName.Items.Insert(0, new ListItem("Select tab name", ""));
DropDownTabName.SelectedIndex = -1;
//Section
DropDownSectionName.Items.Clear();
DropDownSectionName.DataBind();
DropDownSectionName.ClearSelection();
DropDownSectionName.Items.Insert(0, new ListItem("Select section name", ""));
DropDownSectionName.SelectedIndex = -1;
}
protected void btnAdd_Click(object sender, EventArgs e)
{
logger.Debug("Debugging insert statement");
logger.Error("Catching error(s) while adding data");
int month;
if (drpDownMonth.Text == "January")
{
month = 01;
}
else if (drpDownMonth.Text == "February")
{
month = 02;
}
else if (drpDownMonth.Text == "March")
{
month = 03;
}
else if (drpDownMonth.Text == "April")
{
month = 04;
}
else if (drpDownMonth.Text == "May")
{
month = 05;
}
else if (drpDownMonth.Text == "June")
{
month = 06;
}
else if (drpDownMonth.Text == "July")
{
month = 07;
}
else if (drpDownMonth.Text == "August")
{
month = 08;
}
else if (drpDownMonth.Text == "September")
{
month = 09;
}
else if (drpDownMonth.Text == "October")
{
month = 10;
}
else if (drpDownMonth.Text == "November")
{
month = 11;
}
else { month = 12;}
//var insertQuery = ("INSERT INTO CHNGMETRICS.VW_BRD_COMMENT VALUES ('" + "', '" + "', '" + "', '" + "', '" +drpDownYear.Text+ "', '" +month+ "', '" +drpDownMonth.Text+ "', '" +drpDownTabName.Text+ "', '" +drpDownSectionName.Text+ "', '" +drpDownCommentOrder.Text+ "', '" +txtComment.Text+ "')");
var insertQuery = ("INSERT INTO CHNGMETRICS.VW_BRD_COMMENT (METRIC_YEAR, METRIC_MONTH, METRIC_MONTH_TXT, TAB_NAME, SECTION_NAME, COMMENT_ORDER, COMMENT_TEXT) VALUES (:YEAR, :MONTH_NUM, :MONTH_TEXT, :TAB_NAME, :SECTION_NAME, :COMMENT_ORDER, :COMMENT_TEXT)");
OracleCommand command = new OracleCommand(insertQuery);
//ADD PARAMETERS FOR INSERT STATEMENT
command.Parameters.Add(new OracleParameter("YEAR", drpDownYear.Text));
command.Parameters.Add(new OracleParameter("MONTH_NUM", month));
command.Parameters.Add(new OracleParameter("MONTH_TEXT", drpDownMonth.Text));
command.Parameters.Add(new OracleParameter("TAB_NAME", drpDownTabName.Text));
command.Parameters.Add(new OracleParameter("SECTION_NAME", drpDownSectionName.Text));
command.Parameters.Add(new OracleParameter("COMMENT_ORDER", txtCmntOrder.Text));
command.Parameters.Add(new OracleParameter("COMMENT_TEXT", txtComment.Text));
Utility.Exec(command);
RefreshGrid();
//Year
DropDownYear.Items.Clear();
DropDownYear.DataBind();
DropDownYear.ClearSelection();
DropDownYear.Items.Insert(0, new ListItem("Select year", ""));
DropDownYear.SelectedIndex = -1;
//Month
DropDownMonth.Items.Clear();
DropDownMonth.DataBind();
DropDownMonth.ClearSelection();
DropDownMonth.Items.Insert(0, new ListItem("Select month", ""));
DropDownMonth.SelectedIndex = -1;
//Tab
DropDownTabName.Items.Clear();
DropDownTabName.DataBind();
DropDownTabName.ClearSelection();
DropDownTabName.Items.Insert(0, new ListItem("Select tab name", ""));
DropDownTabName.SelectedIndex = -1;
//Section
DropDownSectionName.Items.Clear();
DropDownSectionName.DataBind();
DropDownSectionName.ClearSelection();
DropDownSectionName.Items.Insert(0, new ListItem("Select section name", ""));
DropDownSectionName.SelectedIndex = -1;
txtComment.Text = String.Empty;
txtCmntOrder.Text = String.Empty;
drpDownYear.Text = "-1";
drpDownMonth.Text = "-1";
drpDownTabName.Text = "-1";
drpDownSectionName.Items.Clear();
drpDownSectionName.Items.Insert(0, new ListItem("Select section name", "-1"));
drpDownSectionName.Items.Add(new ListItem("Performance", "Performance"));
drpDownSectionName.Items.Add(new ListItem("Compliance", "Compliance"));
drpDownSectionName.Items.Add(new ListItem("Main", "Main"));
drpDownSectionName.Text = "-1";
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts","");
}
protected void drpDownTabName_SelectedIndexChanged(object sender, EventArgs e)
{
drpDownSectionName.Items.Clear();
if (drpDownTabName.SelectedItem.Text == "Watch List")
{
drpDownSectionName.Items.Add(new ListItem("Performance", "Performance"));
drpDownSectionName.Items.Add(new ListItem("Compliance", "Compliance"));
}
else if ((drpDownTabName.SelectedValue == "General Stats") || (drpDownTabName.SelectedValue == "Planning") || (drpDownTabName.SelectedValue == "CAB"))
{
drpDownSectionName.Items.Add(new ListItem("Main", "Main"));
}
}
//protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
//{
//}
protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
DataTable dt = RefreshGrid();
//Sort the data.
dt.DefaultView.Sort = e.SortExpression + " " + GetSortDirection(e.SortExpression);
GridView1.DataSource = dt.DefaultView;
GridView1.DataBind();
}
private string GetSortDirection(string column)
{
// By default, set the sort direction to ascending.
string sortDirection = "ASC";
// Retrieve the last column that was sorted.
string sortExpression = ViewState["SortExpression"] as string;
if (sortExpression != null)
{
// Check if the same column is being sorted.
// Otherwise, the default value can be returned.
if (sortExpression == column)
{
string lastDirection = ViewState["SortDirection"] as string;
if ((lastDirection != null) && (lastDirection == "ASC"))
{
sortDirection = "DESC";
}
}
}
// Save new values in ViewState.
ViewState["SortDirection"] = sortDirection;
ViewState["SortExpression"] = column;
return sortDirection;
}
}
}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Midhun Tp9y ago
Hi,

Please try changing your Pageindexchanging function as below -
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    DataTable dt = RefreshGrid();
    if (ViewState["Sort"] != null)
    {
        dt.DefaultView.Sort = (string)ViewState["SortExpression"]+" "+ (string)ViewState["SortDirection"];
    }
    GridView1.DataSource = dt;
    GridView1.DataBind();
}
0