locked
Sorting Oracle Data in GridView RRS feed

  • Question

  • User-2055741253 posted

    Hello I have a GridView which is connected to Oracle Database. I would like to sort the data in ASC and DESC order. Can you please help me or guide me on how to do that?

    Utility. CS

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.OracleClient;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Web;
    
    
    namespace ChngMgmt_Comments_and_Legends.Library
    {
        public class Utility
        {
    
            static DbConnection CreateDbConnection(string providerName, string connectionString)
            {
                // Assume failure.
                DbConnection connection = null;
    
                // Create the DbProviderFactory and DbConnection.
                if (connectionString != null)
                {
                    try
                    {
                        DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
    
                        connection = factory.CreateConnection();
                        connection.ConnectionString = connectionString;
                    }
                    catch (Exception ex)
                    {
                        // Set the connection to null if it was created.
                        if (connection != null)
                        {
                            connection = null;
                        }
                        Console.WriteLine(ex.Message);
                    }
                }
                // Return the connection.
                return connection;
            }
    
            internal static void Exec(OracleCommand comm)
            {
                string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                string prov = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
    
    
                using (OracleConnection connection = new OracleConnection(constr))
                {
                    using (comm)
                    {
                        connection.Open();
                        comm.Connection = connection;
                        comm.ExecuteNonQuery();
                        connection.Close();
                    }
    
    
                }
            }
    
    
            public static DataTable GetData(OracleCommand comm)
            {
                string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                string prov = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
    
    
                using (OracleConnection connection = new OracleConnection(constr))
                {
                    using (comm)
                    {
                        using (OracleDataAdapter da = new OracleDataAdapter())
                        {
                            comm.Connection = connection;
                            da.SelectCommand = comm;
    
                            using (DataSet ds = new DataSet())
                            {
                                DataTable dt = new DataTable();
                                da.Fill(dt);
                                return dt;
                            }
                        }
                    }
                        
                    
                }
            }
    
        }
    }
    

    GridView:

    <asp:GridView ID="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">
                <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                <Columns>
    
    
                    <%-- COMMENT ID --%>
                    <asp:TemplateField>
                        <ItemTemplate>
                            <asp:Label  Visible="false" ID="lblID" runat="server" Text='<%# Bind("BRD_COMMENT_ID") %>'>
    
                            </asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:Label ID="txtID" Visible="false" runat="server" Text='<%# Bind("BRD_COMMENT_ID") %>'>
    
                            </asp:Label>
                        </EditItemTemplate>
                    </asp:TemplateField>
    
                    <%-- COMMENT YEAR --%>
                    <asp:TemplateField HeaderText="Year">
                        <ItemTemplate>
                            <asp:Label ID="lblCommentYear" Visible="true" runat="server" Text='<%# Bind("METRIC_YEAR") %>'>
    
                            </asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:Label ID="txtCommentYear" runat="server" Text='<%# Bind("METRIC_YEAR") %>'>
    
                            </asp:Label>
                        </EditItemTemplate>
                    </asp:TemplateField>
    
                    <%-- COMMENT MONTH --%>
                    <asp:TemplateField HeaderText="Month">
                        <ItemTemplate>
                            <asp:Label ID="lblCommentMonth" Visible="true" runat="server" Text='<%# Bind("METRIC_MONTH_TXT") %>'>
    
                            </asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:Label ID="txtCommentMonth" runat="server" Text='<%# Bind("METRIC_MONTH_TXT") %>'>
    
                            </asp:Label>
                        </EditItemTemplate>
                    </asp:TemplateField>
    
                    <%-- COMMENT TAB --%>
                    <asp:TemplateField HeaderText="Tab">
                        <ItemTemplate>
                            <asp:Label ID="lblCommentTab" Visible="true" runat="server" Text='<%# Bind("TAB_NAME") %>'>
    
                            </asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:Label ID="txtCommentTab" runat="server" Text='<%# Bind("TAB_NAME") %>'>
    
                            </asp:Label>
                        </EditItemTemplate>
                    </asp:TemplateField>
    
                    <%-- COMMENT SECTION --%>
                    <asp:TemplateField HeaderText="Section">
                        <ItemTemplate>
                            <asp:Label ID="lblCommentSection" Visible="true" runat="server" Text='<%# Bind("SECTION_NAME") %>'>
    
                            </asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:Label ID="txtCommentSection" runat="server" Text='<%# Bind("SECTION_NAME") %>'>
    
                            </asp:Label>
                        </EditItemTemplate>
                    </asp:TemplateField>
    
                    <%-- COMMENT ORDER --%>
                    <asp:TemplateField HeaderText="Order" SortExpression="Order">
                        <ItemTemplate>
                            <asp:Label ID="lblCommentOrder" runat="server" Text='<%# Bind("COMMENT_ORDER") %>'>
    
                            </asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="txtCommentOrder"  Width="20px" runat="server" Text='<%# Bind("COMMENT_ORDER") %>'></asp:TextBox>
                            <asp:RequiredFieldValidator ID="rfvUpdateCommentOrder" ValidationGroup="UPDATE" runat="server" 
                            ErrorMessage="Comment order is required." ForeColor="Brown"  Display="Dynamic" ControlToValidate="txtCommentOrder"></asp:RequiredFieldValidator>
                        </EditItemTemplate>
                    </asp:TemplateField>
                    
                    <%-- COMMENT TEXT --%>
                    <asp:TemplateField HeaderStyle-HorizontalAlign="Center" HeaderText="Comments">
                        <ItemTemplate>
                            <asp:Label ID="lblComment" runat="server" Text='<%# Bind("COMMENT_TEXT") %>'>
    
                            </asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="txtEditComment"  TextMode="MultiLine" Width="400px" Height="30px" MaxLength="300" runat="server" Text='<%# Bind("COMMENT_TEXT") %>'></asp:TextBox>
                            <asp:RequiredFieldValidator ID="rfvUpdateCommentText" ValidationGroup="UPDATE" runat="server" 
                            ErrorMessage="Please enter a comment" ForeColor="brown" ControlToValidate="txtEditComment"></asp:RequiredFieldValidator>
                        </EditItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField>
                        <ItemTemplate>
                            <asp:LinkButton ID ="lnkEdit" runat="server" CommandName="Edit" Text ="Edit"></asp:LinkButton>
                            <asp:LinkButton OnClientClick="return confirm('Are you sure you want to delete this comment?');" ID ="lnkDelete" runat="server" CommandName="Delete" Text ="Delete"></asp:LinkButton>
                        </ItemTemplate>
                        <EditItemTemplate>   
                           <asp:Button ID="btn_Update" runat="server" Text="Update" CommandName="Update"/>   
                           <asp:Button ID="btn_Cancel" runat="server" Text="Cancel" CommandName="Cancel"/>   
                        </EditItemTemplate>
                    </asp:TemplateField>
                   
                </Columns>
                <EditRowStyle BackColor="#999999" />
                <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <HeaderStyle  HorizontalAlign="Left" BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                <SortedAscendingCellStyle BackColor="#E9E7E2" />
                <SortedAscendingHeaderStyle BackColor="#506C8C" />
                <SortedDescendingCellStyle BackColor="#FFFDF8" />
                <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
            </asp:GridView>

    aspx.cs

    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;
    using System.Configuration;
    using ChngMgmt_Comments_and_Legends.Library;
    using System.Data.Common;
    using System.Data.OracleClient;
    using log4net;
    
    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)
                {
                    RefreshGrid();
                }
            }
            
    
            public void 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);
                GridView1.DataSource = GridComments;
                GridView1.DataBind();
    
    
            }
            //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;
                RefreshGrid();
            }
    
            // 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","<script>alert('Your comment was successfully added.');</script>");
            }
    
            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)
            //{
    
            //}
    
    
        }
    }

    Monday, August 28, 2017 9:34 PM

Answers

  • User-335504541 posted

    Hi gssingh04,

    Please try to us the following code:

    In aspx:

                        <%-- COMMENT ORDER --%>
                        <asp:TemplateField HeaderText="Order" SortExpression="COMMENT_ORDER">
                            <ItemTemplate>
                                <asp:Label ID="lblCommentOrder" runat="server" Text='<%# Bind("COMMENT_ORDER") %>'>
    
                                </asp:Label>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:TextBox ID="txtCommentOrder" Width="20px" runat="server" Text='<%# Bind("COMMENT_ORDER") %>'></asp:TextBox>
                                <asp:RequiredFieldValidator ID="rfvUpdateCommentOrder" ValidationGroup="UPDATE" runat="server"
                                    ErrorMessage="Comment order is required." ForeColor="Brown" Display="Dynamic" ControlToValidate="txtCommentOrder"></asp:RequiredFieldValidator>
                            </EditItemTemplate>
                        </asp:TemplateField>               

    In behind code:

    I have modify your RefreshGrid() to return dt.

            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 dt; }
            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;
                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;
            }


    And the result is :

    You could refer to the link below for more information:

    https://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.sorting(v=vs.110).aspx

    Best Regards,

    Billy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 29, 2017 7:54 AM
  • User-335504541 posted

    Hi gssingh04,

    Sorry, please modify

    return dt;

    to

    return GridComments;

    Best Regards,

    Billy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 30, 2017 1:14 AM

All replies

  • User-335504541 posted

    Hi gssingh04,

    Please try to us the following code:

    In aspx:

                        <%-- COMMENT ORDER --%>
                        <asp:TemplateField HeaderText="Order" SortExpression="COMMENT_ORDER">
                            <ItemTemplate>
                                <asp:Label ID="lblCommentOrder" runat="server" Text='<%# Bind("COMMENT_ORDER") %>'>
    
                                </asp:Label>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:TextBox ID="txtCommentOrder" Width="20px" runat="server" Text='<%# Bind("COMMENT_ORDER") %>'></asp:TextBox>
                                <asp:RequiredFieldValidator ID="rfvUpdateCommentOrder" ValidationGroup="UPDATE" runat="server"
                                    ErrorMessage="Comment order is required." ForeColor="Brown" Display="Dynamic" ControlToValidate="txtCommentOrder"></asp:RequiredFieldValidator>
                            </EditItemTemplate>
                        </asp:TemplateField>               

    In behind code:

    I have modify your RefreshGrid() to return dt.

            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 dt; }
            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;
                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;
            }


    And the result is :

    You could refer to the link below for more information:

    https://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.sorting(v=vs.110).aspx

    Best Regards,

    Billy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 29, 2017 7:54 AM
  • User-2055741253 posted

    Hi Billy,

    I appreciate your response to this post. I tried the code behind, but for some reason I am getting an error: "The name 'dt' does not exist in current context"

    DataTable GridComments = Utility.GetData(command);
                return dt;

    Do you know what could be causing this?

    Tuesday, August 29, 2017 1:23 PM
  • User-335504541 posted

    Hi gssingh04,

    Sorry, please modify

    return dt;

    to

    return GridComments;

    Best Regards,

    Billy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 30, 2017 1:14 AM
  • User-2055741253 posted

    That took the error out. But when I sort, the data does not change when paging. The data stays the same for each page.

    Wednesday, August 30, 2017 3:26 AM
  • User-335504541 posted

    Hi gssingh04,

    The data stays the same for each page.

    The reason is that the  RefreshGrid() is called in PageIndexChanging event.

    Each time the RefeshGrid() is called, the data in gridview will be reset.

    Please try to use the following code:

            protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
            {            
                DataTable dt = RefreshGrid();            
                //Sort the data.
                var sort= e.SortExpression + " " + GetSortDirection(e.SortExpression);
                dt.DefaultView.Sort = sort;
                ViewState["Sort"] = sort;
                GridView1.DataSource = dt;
                GridView1.DataBind();
    
            }
            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();
            }

    And the result is:

    Best Regards,

    Billy

    Wednesday, August 30, 2017 8:43 AM