Post
Ask Question
Gagan Marwah

Gagan Marwah

  • NA
  • 11
  • 5.7k

GridView Sorting

Aug 29 2017 6:55 PM

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

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.Data.Common;  
  5. using System.Data.OracleClient;  
  6. using System.Data.SqlClient;  
  7. using System.Linq;  
  8. using System.Web;  
  9.   
  10.   
  11. namespace ChngMgmt_Comments_and_Legends.Library  
  12. {  
  13.     public class Utility  
  14.     {  
  15.   
  16.         static DbConnection CreateDbConnection(string providerName, string connectionString)  
  17.         {  
  18.             // Assume failure.  
  19.             DbConnection connection = null;  
  20.   
  21.             // Create the DbProviderFactory and DbConnection.  
  22.             if (connectionString != null)  
  23.             {  
  24.                 try  
  25.                 {  
  26.                     DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);  
  27.   
  28.                     connection = factory.CreateConnection();  
  29.                     connection.ConnectionString = connectionString;  
  30.                 }  
  31.                 catch (Exception ex)  
  32.                 {  
  33.                     // Set the connection to null if it was created.  
  34.                     if (connection != null)  
  35.                     {  
  36.                         connection = null;  
  37.                     }  
  38.                     Console.WriteLine(ex.Message);  
  39.                 }  
  40.             }  
  41.             // Return the connection.  
  42.             return connection;  
  43.         }  
  44.   
  45.         internal static void Exec(OracleCommand comm)  
  46.         {  
  47.             string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;  
  48.             string prov = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;  
  49.   
  50.   
  51.             using (OracleConnection connection = new OracleConnection(constr))  
  52.             {  
  53.                 using (comm)  
  54.                 {  
  55.                     connection.Open();  
  56.                     comm.Connection = connection;  
  57.                     comm.ExecuteNonQuery();  
  58.                     connection.Close();  
  59.                 }  
  60.   
  61.   
  62.             }  
  63.         }  
  64.   
  65.   
  66.         public static DataTable GetData(OracleCommand comm)  
  67.         {  
  68.             string constr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;  
  69.             string prov = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;  
  70.   
  71.   
  72.             using (OracleConnection connection = new OracleConnection(constr))  
  73.             {  
  74.                 using (comm)  
  75.                 {  
  76.                     using (OracleDataAdapter da = new OracleDataAdapter())  
  77.                     {  
  78.                         comm.Connection = connection;  
  79.                         da.SelectCommand = comm;  
  80.   
  81.                         using (DataSet ds = new DataSet())  
  82.                         {  
  83.                             DataTable dt = new DataTable();  
  84.                             da.Fill(dt);  
  85.                             return dt;  
  86.                         }  
  87.                     }  
  88.                 }  
  89.                       
  90.                   
  91.             }  
  92.         }  
  93.   
  94.     }  
  95. }  
GridView:

  1. <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" CellPadding="4"  ForeColor="#333333" GridLines="None" PageSize="20"   
  2.             OnPageIndexChanging="GridView1_PageIndexChanging" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowDeleting="GridView1_RowDeleting"  OnSorting="GridView1_Sorting">  
  3.             <AlternatingRowStyle BackColor="White" ForeColor="#284775" />  
  4.             <Columns>  
  5.   
  6.   
  7.                 <%-- COMMENT ID --%>  
  8.                 <asp:TemplateField>  
  9.                     <ItemTemplate>  
  10.                         <asp:Label  Visible="false" ID="lblID" runat="server" Text='<%# Bind("BRD_COMMENT_ID") %>'>  
  11.   
  12.                         </asp:Label>  
  13.                     </ItemTemplate>  
  14.                     <EditItemTemplate>  
  15.                         <asp:Label ID="txtID" Visible="false" runat="server" Text='<%# Bind("BRD_COMMENT_ID") %>'>  
  16.   
  17.                         </asp:Label>  
  18.                     </EditItemTemplate>  
  19.                 </asp:TemplateField>  
  20.   
  21.                 <%-- COMMENT YEAR --%>  
  22.                 <asp:TemplateField HeaderText="Year">  
  23.                     <ItemTemplate>  
  24.                         <asp:Label ID="lblCommentYear" Visible="true" runat="server" Text='<%# Bind("METRIC_YEAR") %>'>  
  25.   
  26.                         </asp:Label>  
  27.                     </ItemTemplate>  
  28.                     <EditItemTemplate>  
  29.                         <asp:Label ID="txtCommentYear" runat="server" Text='<%# Bind("METRIC_YEAR") %>'>  
  30.   
  31.                         </asp:Label>  
  32.                     </EditItemTemplate>  
  33.                 </asp:TemplateField>  
  34.   
  35.                 <%-- COMMENT MONTH --%>  
  36.                 <asp:TemplateField HeaderText="Month">  
  37.                     <ItemTemplate>  
  38.                         <asp:Label ID="lblCommentMonth" Visible="true" runat="server" Text='<%# Bind("METRIC_MONTH_TXT") %>'>  
  39.   
  40.                         </asp:Label>  
  41.                     </ItemTemplate>  
  42.                     <EditItemTemplate>  
  43.                         <asp:Label ID="txtCommentMonth" runat="server" Text='<%# Bind("METRIC_MONTH_TXT") %>'>  
  44.   
  45.                         </asp:Label>  
  46.                     </EditItemTemplate>  
  47.                 </asp:TemplateField>  
  48.   
  49.                 <%-- COMMENT TAB --%>  
  50.                 <asp:TemplateField HeaderText="Tab">  
  51.                     <ItemTemplate>  
  52.                         <asp:Label ID="lblCommentTab" Visible="true" runat="server" Text='<%# Bind("TAB_NAME") %>'>  
  53.   
  54.                         </asp:Label>  
  55.                     </ItemTemplate>  
  56.                     <EditItemTemplate>  
  57.                         <asp:Label ID="txtCommentTab" runat="server" Text='<%# Bind("TAB_NAME") %>'>  
  58.   
  59.                         </asp:Label>  
  60.                     </EditItemTemplate>  
  61.                 </asp:TemplateField>  
  62.   
  63.                 <%-- COMMENT SECTION --%>  
  64.                 <asp:TemplateField HeaderText="Section">  
  65.                     <ItemTemplate>  
  66.                         <asp:Label ID="lblCommentSection" Visible="true" runat="server" Text='<%# Bind("SECTION_NAME") %>'>  
  67.   
  68.                         </asp:Label>  
  69.                     </ItemTemplate>  
  70.                     <EditItemTemplate>  
  71.                         <asp:Label ID="txtCommentSection" runat="server" Text='<%# Bind("SECTION_NAME") %>'>  
  72.   
  73.                         </asp:Label>  
  74.                     </EditItemTemplate>  
  75.                 </asp:TemplateField>  
  76.   
  77.                 <%-- COMMENT ORDER --%>  
  78.                     <asp:TemplateField HeaderText="Order" SortExpression="COMMENT_ORDER">  
  79.                         <ItemTemplate>  
  80.                             <asp:Label ID="lblCommentOrder" runat="server" Text='<%# Bind("COMMENT_ORDER") %>'>  
  81.   
  82.                             </asp:Label>  
  83.                         </ItemTemplate>  
  84.                         <EditItemTemplate>  
  85.                             <asp:TextBox ID="txtCommentOrder" Width="20px" runat="server" Text='<%# Bind("COMMENT_ORDER") %>'></asp:TextBox>  
  86.                             <asp:RequiredFieldValidator ID="rfvUpdateCommentOrder" ValidationGroup="UPDATE" runat="server"  
  87.                                 ErrorMessage="Comment order is required." ForeColor="Brown" Display="Dynamic" ControlToValidate="txtCommentOrder"></asp:RequiredFieldValidator>  
  88.                         </EditItemTemplate>  
  89.                     </asp:TemplateField>                        
  90.                 <%-- COMMENT TEXT --%>  
  91.                 <asp:TemplateField HeaderStyle-HorizontalAlign="Center" HeaderText="Comments">  
  92.                     <ItemTemplate>  
  93.                         <asp:Label ID="lblComment" runat="server" Text='<%# Bind("COMMENT_TEXT") %>'>  
  94.   
  95.                         </asp:Label>  
  96.                     </ItemTemplate>  
  97.                     <EditItemTemplate>  
  98.                         <asp:TextBox ID="txtEditComment"  TextMode="MultiLine" Width="400px" Height="30px" MaxLength="300" runat="server" Text='<%# Bind("COMMENT_TEXT") %>'></asp:TextBox>  
  99.                         <asp:RequiredFieldValidator ID="rfvUpdateCommentText" ValidationGroup="UPDATE" runat="server"   
  100.                         ErrorMessage="Please enter a comment" ForeColor="brown" ControlToValidate="txtEditComment"></asp:RequiredFieldValidator>  
  101.                     </EditItemTemplate>  
  102.                 </asp:TemplateField>  
  103.                 <asp:TemplateField>  
  104.                     <ItemTemplate>  
  105.                         <asp:LinkButton ID ="lnkEdit" runat="server" CommandName="Edit" Text ="Edit"></asp:LinkButton>  
  106.                         <asp:LinkButton OnClientClick="return confirm('Are you sure you want to delete this comment?');" ID ="lnkDelete" runat="server" CommandName="Delete" Text ="Delete"></asp:LinkButton>  
  107.                     </ItemTemplate>  
  108.                     <EditItemTemplate>     
  109.                        <asp:Button ID="btn_Update" runat="server" Text="Update" CommandName="Update"/>     
  110.                        <asp:Button ID="btn_Cancel" runat="server" Text="Cancel" CommandName="Cancel"/>     
  111.                     </EditItemTemplate>  
  112.                 </asp:TemplateField>  
  113.                  
  114.             </Columns>  
  115.             <EditRowStyle BackColor="#999999" />  
  116.             <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />  
  117.             <HeaderStyle  HorizontalAlign="Left" BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />  
  118.             <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />  
  119.             <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />  
  120.             <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />  
  121.             <SortedAscendingCellStyle BackColor="#E9E7E2" />  
  122.             <SortedAscendingHeaderStyle BackColor="#506C8C" />  
  123.             <SortedDescendingCellStyle BackColor="#FFFDF8" />  
  124.             <SortedDescendingHeaderStyle BackColor="#6F8DAE" />  
  125.         </asp:GridView>  
 Code Behind:
 I get an error on return dt > error: The name 'dt' does not exist in current context.
  1. protected void Page_Load(object sender, EventArgs e)  
  2.         {  
  3.             if (!IsPostBack)  
  4.             {  
  5.                 GridView1.DataSource = RefreshGrid();  
  6.                 GridView1.DataBind();  
  7.             }  
  8.         }  
  9.   
  10.         public DataTable RefreshGrid()  
  11.         {  
  12.             //Gets all the data in grid view for the following columns  
  13.             //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";  
  14.   
  15.             //If Year drop down is not null the data gets filtered by year  
  16.             if (DropDownYear.SelectedValue != "")  
  17.             {  
  18.                 queryStr += " AND METRIC_YEAR = :METRIC_YEAR";  
  19.   
  20.             }  
  21.             //If Month drop down in not null the data gets filtered by month.  
  22.             if (DropDownMonth.SelectedValue != "")  
  23.             {  
  24.                 queryStr += " AND METRIC_MONTH = :METRIC_MONTH";  
  25.   
  26.             }  
  27.             //if section drop down is not null the data gets filtered by section name  
  28.             if (DropDownSectionName.SelectedValue != "")  
  29.             {  
  30.                 queryStr += " AND SECTION_NAME = :SECTION_NAME";  
  31.   
  32.             }  
  33.             //if TAB drop down is not null the data gets filtered by TAB Name  
  34.             if (DropDownTabName.SelectedValue != "")  
  35.             {  
  36.                 queryStr += " AND TAB_NAME = :TAB_NAME";  
  37.   
  38.             }  
  39.   
  40.             //Added parameters for Year,Month,Section,Tab drop downs  
  41.             OracleCommand command = new OracleCommand(queryStr);  
  42.             if (DropDownYear.SelectedValue != "")  
  43.             {  
  44.                 command.Parameters.AddWithValue("METRIC_YEAR", DropDownYear.SelectedValue);  
  45.   
  46.             }  
  47.             if (DropDownMonth.SelectedValue != "")  
  48.             {  
  49.                 command.Parameters.AddWithValue("METRIC_MONTH", DropDownMonth.SelectedValue);               
  50.   
  51.             }  
  52.             if (DropDownSectionName.SelectedValue != "")  
  53.             {  
  54.                 command.Parameters.AddWithValue("SECTION_NAME", DropDownSectionName.SelectedValue);  
  55.                   
  56.             }  
  57.             if (DropDownTabName.SelectedValue != "")  
  58.             {  
  59.                 command.Parameters.AddWithValue("TAB_NAME", DropDownTabName.SelectedValue);  
  60.             }  
  61.   
  62.             DataTable GridComments = Utility.GetData(command);  
  63.   
  64. // I GET A ERROR HERE: The name 'dt' does not exist in current context.   
  65.             return dt;  
  66.              
  67.         }  


 On Sorting event:
  1. protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)  
  2.         {  
  3.             DataTable dt = RefreshGrid();              
  4.             //Sort the data.  
  5.             dt.DefaultView.Sort = e.SortExpression + " " + GetSortDirection(e.SortExpression);  
  6.             GridView1.DataSource = dt;  
  7.             GridView1.DataBind();  
  8.   
  9.         }  
  10.         private string GetSortDirection(string column)  
  11.         {  
  12.   
  13.             // By default, set the sort direction to ascending.  
  14.             string sortDirection = "ASC";  
  15.   
  16.             // Retrieve the last column that was sorted.  
  17.             string sortExpression = ViewState["SortExpression"] as string;  
  18.   
  19.             if (sortExpression != null)  
  20.             {  
  21.                 // Check if the same column is being sorted.  
  22.                 // Otherwise, the default value can be returned.  
  23.                 if (sortExpression == column)  
  24.                 {  
  25.                     string lastDirection = ViewState["SortDirection"] as string;  
  26.                     if ((lastDirection != null) && (lastDirection == "ASC"))  
  27.                     {  
  28.                         sortDirection = "DESC";  
  29.                     }  
  30.                 }  
  31.             }  
  32.   
  33.             // Save new values in ViewState.  
  34.             ViewState["SortDirection"] = sortDirection;  
  35.             ViewState["SortExpression"] = column;  
  36.   
  37.             return sortDirection;  
  38.         }  
 

Answers (2)