GridView Sorting

Question

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?

I am getting an error in code behind  in public DataTable RefreshGrid() method

 

return dt; > The name 'dt' does not exist in current context. Please see the following code. I would really appreciate if someone can help me out. Thanks in advance   

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="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>
                <%-- 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>

 Code Behind:

 I get an error on return dt > error: The name 'dt' does not exist in current context.

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);

// I GET A ERROR HERE: The name 'dt' does not exist in current context.
            return dt;

        }

 On Sorting event:

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