locked
Refresh/Auto update dropdownlist RRS feed

  • Question

  • User-2055741253 posted

    Hello all,

    I have a multiple drop down boxes that filter the result for GridView, and the drop down boxes are data binded to data source. I would like to Refresh/Auto update dropdownlist whenever new data is available, so I do not have to clear the dropdown list and repopulate it, which I have to do by refreshing the entire page. I know something like this can be done using AJAX but I cannot seem to direct myself into right direction. Can some please help with this? Any help would be much appreciated.

    aspx page

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ChngMetricsLegends.aspx.cs" Inherits="ChngMgmt_Comments_and_Legends.ChngMetricsLegends" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
     <head runat="server">
         <title></title>
         <style type="text/css">
             .auto-style1 {
                 width: 45%;
             }
             .auto-style2 {
                 width: 206px;
             }
             .auto-style4 {
                 width: 206px;
                 height: 82px;
             }
             .auto-style5 {
                 height: 82px;
             }
             .auto-style6 {
                 height: 35px;
             }
             </style>
    
        <link href="StyleSheet1.css" rel="stylesheet" />
         <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
         <script type="text/javascript" src="MaxLength.min.js"></script>
         <script type="text/javascript">
    
        function textCounter(field, countfield, maxlimit) 
         {
         if (field.value.length > maxlimit)
         field.value = field.value.substring(0, maxlimit);
         else
         countfield.value = maxlimit - field.value.length;
         }
    
        </script>
    
        <script type="text/javascript">
             $(function () {
    
                //Specifying the Character Count control explicitly
                 $("[id*=txtEditLegend]").MaxLength(
                 {
                     MaxLength: 300,
                     CharacterCountControl: $('#counter')
                 });
    
            });
         </script>
    
    </head>
     <body>
         <form id="form1" runat="server">
         <div class="auto-style6">
             <ul id="nav">
              <li><a href="Comments.aspx">Comments</a></li>
              <li><a href="ChngMetricsLegends.aspx">Legends</a></li>
             </ul>
         </div>
             <br />
             <h2 style="background-color:#2680A5; color:white; text-align:center; padding-bottom:10px;">Legends</h2>
             <div>
             <fieldset id="fieldset-auto-width" style="border-left-color: #2680A5; border-bottom-color: #2680A5; border-top-style: solid; border-top-color: #2680A5; border-right-style: solid; border-left-style: solid; border-right-color: #2680A5; border-bottom-style: solid">
             <legend style="color:brown;">Fill the information below to add new legend:</legend>
             <table class="auto-style1">
                 <tr>
                     <td class="auto-style4">Legend:</td>
                     <td class="auto-style5">
                         <asp:TextBox ID="txtLegend" runat="server" Placeholder="Please enter legend here"  TextMode="MultiLine" MaxLength="300"  Width="400px" Height="50px" Wrap="true" 
                             onkeyup="textCounter(txtLegend, this.form.uxCommentsNoCharCount, 300);" onkeydown="textCounter(txtLegend, this.form.uxCommentsNoCharCount, 300);" />
                         
                         <input readonly="readonly" type="text" id="uxCommentsNoCharCount" name="uxCommentsNoCharCount"
                                size="4" maxlength="4" value="" style="border: solid 0px white; background: white;" />
                         <br />
                         <asp:RequiredFieldValidator ID="RequiredFieldValidatorLegendText" ValidationGroup="INSERT" runat="server" 
                             ErrorMessage="Please enter text" ForeColor="Red" ControlToValidate="txtLegend"></asp:RequiredFieldValidator>
                         
                         <br />
                     </td>
                 </tr>
                 <tr>
                     <td class="auto-style2">Year:</td>
                     <td>
                         <asp:DropDownList ID="drpDownYear" runat="server" Width="180px">
                             <asp:ListItem Text= "Select a year" Value="-1"/>
                             <asp:ListItem>2016</asp:ListItem>
                             <asp:ListItem>2017</asp:ListItem>
                             <asp:ListItem>2018</asp:ListItem>
                             <asp:ListItem>2019</asp:ListItem>
                             <asp:ListItem>2020</asp:ListItem>
                             <asp:ListItem>2021</asp:ListItem>
                             <asp:ListItem>2022</asp:ListItem>
                             <asp:ListItem>2023</asp:ListItem>
                             <asp:ListItem>2024</asp:ListItem>
                             <asp:ListItem>2025</asp:ListItem>
                         </asp:DropDownList>
                         <br />
                         <asp:RequiredFieldValidator ID="RequiredFieldValidatorYear" ValidationGroup="INSERT" runat="server" 
                             ErrorMessage="Year is required" ForeColor="Red" ControlToValidate="drpDownYear" InitialValue="-1"></asp:RequiredFieldValidator>
                         <br />
                     </td>
                 </tr>
                 <tr>
                     <td class="auto-style2">Month:</td>
                     <td>
                         <asp:DropDownList ID="drpDownMonth" runat="server" Width="180px">
                             <asp:ListItem Text= "Select a month" Value="-1"/>
                             <asp:ListItem>January</asp:ListItem>
                             <asp:ListItem>February</asp:ListItem>
                             <asp:ListItem>March</asp:ListItem>
                             <asp:ListItem>April</asp:ListItem>
                             <asp:ListItem>May</asp:ListItem>
                             <asp:ListItem>June</asp:ListItem>
                             <asp:ListItem>July</asp:ListItem>
                             <asp:ListItem>August</asp:ListItem>
                             <asp:ListItem>September</asp:ListItem>
                             <asp:ListItem>October</asp:ListItem>
                             <asp:ListItem>November</asp:ListItem>
                             <asp:ListItem>December</asp:ListItem>
                         </asp:DropDownList>
                         <br />
                         <asp:RequiredFieldValidator ID="RequiredFieldValidatorMonth" ValidationGroup="INSERT" runat="server" 
                             ErrorMessage="Month is required" ForeColor="Red" ControlToValidate="drpDownMonth" InitialValue="-1"></asp:RequiredFieldValidator>
                         <br />
                     </td>
                 </tr>
                 <tr>
                     <td class="auto-style2">Tab:</td>
                     <td>
                         <asp:DropDownList ID="drpDownTabName" runat="server" Width="180px" AutoPostBack="True" OnSelectedIndexChanged="drpDownTabName_SelectedIndexChanged">
                             <asp:ListItem Text= "Select tab name" Value="-1"/>
                             <asp:ListItem>Watch List</asp:ListItem>
                             <asp:ListItem>General Stats</asp:ListItem>
                             <asp:ListItem>Planning</asp:ListItem>
                             <asp:ListItem>CAB</asp:ListItem>
                         </asp:DropDownList>
                         <br />
                         <asp:RequiredFieldValidator ID="RequiredFieldValidatorTabName" ValidationGroup="INSERT" runat="server" 
                             ErrorMessage="Tab name is required" ForeColor="Red" ControlToValidate="drpDownTabName" InitialValue="-1"></asp:RequiredFieldValidator>
                         <br />
                     </td>
                 </tr>
                 <tr>
                     <td class="auto-style2">Section:</td>
                     <td>
                         <asp:DropDownList ID="drpDownSectionName" runat="server" Width="180px" AutoPostBack="True">
                             <asp:ListItem Text= "Select section name" Value="-1"/>
                             <asp:ListItem>Main</asp:ListItem>
                         </asp:DropDownList>
                         <br />
                         <asp:RequiredFieldValidator ID="RequiredFieldValidatorSectionName" ValidationGroup="INSERT" runat="server" 
                             ErrorMessage="Section name is required" ForeColor="Red" ControlToValidate="drpDownSectionName" InitialValue="-1"></asp:RequiredFieldValidator>
                         <br />
                     </td>
                 </tr>
                 <tr>
                     <td class="auto-style2">Order:</td>
                     <td>
                         <asp:TextBox ID="txtLgndOrder" runat="server" Placeholder="Enter legend here"  TextMode="Number"></asp:TextBox>
                         <br />
                         <asp:RequiredFieldValidator ID="RequiredFieldValidatorLegendOrder" ValidationGroup="INSERT" runat="server" 
                             ErrorMessage="Legend order is required" ForeColor="Red" ControlToValidate="txtLgndOrder" InitialValue="-1"></asp:RequiredFieldValidator>
                         <br />
                     </td>
                 </tr>
                 <tr>
                     <td class="auto-style2">
                         <asp:Button ID="btnAdd"  ValidationGroup="INSERT" runat="server" OnClick="btnAdd_Click" Text="Add Legend" />
                     </td>
                     <td>
                         &nbsp;</td>
                 </tr>
             </table>
             </fieldset>
             </div>
                         <strong>
                         <asp:Label ID="lblLegendSaved" runat="server" ForeColor="SlateGray"></asp:Label>
                         </strong>
                     <br />
             <br />
                         <asp:Label ID="lblLegendDisplay" runat="server" ForeColor="#4BD21E"></asp:Label>
                         <br />
             <br />
             <div>
             <fieldset id="fieldset-auto-width" style="border-left-color: #2680A5; border-bottom-color: #2680A5; border-top-style: solid; border-top-color: #2680A5; border-right-style: solid; border-left-style: solid; border-right-color: #2680A5; border-bottom-style: solid">
             <legend style="color:brown;">Select values in drop down to filter data:</legend>
             <br />
             Year:
             <asp:DropDownList ID="DropDownYear" runat="server" AutoPostBack="True" DataSourceID="CommentsYearMonth" DataTextField="METRIC_YEAR"  DataValueField="METRIC_YEAR" AppendDataBoundItems="true" OnSelectedIndexChanged="DropDownYear_SelectedIndexChanged">
                 <asp:ListItem Text="Select Year" Value="" /> 
             </asp:DropDownList>
             &nbsp;&nbsp;&nbsp; Month:
             <asp:DropDownList ID="DropDownMonth" runat="server" AutoPostBack="True" DataSourceID="CommentsMonth" DataTextField="METRIC_MONTH_TXT" DataValueField="METRIC_MONTH" AppendDataBoundItems="true" OnSelectedIndexChanged="DropDownMonth_SelectedIndexChanged">
                 <asp:ListItem Text="Select Month" Value="" />
             </asp:DropDownList>
                 &nbsp;&nbsp;&nbsp; Tab:
             <asp:DropDownList ID="DropDownTabName" runat="server" AutoPostBack="True" DataSourceID="CommentsTabName" DataTextField="TAB_NAME" DataValueField="TAB_NAME" AppendDataBoundItems="true" OnSelectedIndexChanged="DropDownTabName_SelectedIndexChanged">
                 <asp:ListItem Text="Select Tab Name" Value="" />
             </asp:DropDownList>
                 &nbsp;&nbsp;&nbsp; Section:
             <asp:DropDownList ID="DropDownSectionName" runat="server" AutoPostBack="True" DataSourceID="CommentsSectionName" DataTextField="SECTION_NAME" DataValueField="SECTION_NAME" AppendDataBoundItems="true" OnSelectedIndexChanged="DropDownSectionName_SelectedIndexChanged">
                 <asp:ListItem Text="Select Section Name" Value="" />
             </asp:DropDownList>
                 <br />
                 <br />
                 <asp:Button ID="btnViewAll" runat="server" OnClick="btnViewAll_Click" Text="View All" />
                 <br />
             </fieldset>
             </div>
             <asp:SqlDataSource ID="CommentsTabName" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT DISTINCT TAB_NAME FROM CHNGMETRICS.VW_BRD_LEGEND"></asp:SqlDataSource>
             <asp:SqlDataSource ID="CommentsSectionName" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT DISTINCT SECTION_NAME FROM CHNGMETRICS.VW_BRD_LEGEND"></asp:SqlDataSource>
             &nbsp;<asp:SqlDataSource ID="CommentsMonth" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT DISTINCT METRIC_MONTH_TXT, METRIC_MONTH FROM CHNGMETRICS.VW_BRD_LEGEND ORDER BY METRIC_MONTH"></asp:SqlDataSource>
             <asp:SqlDataSource ID="CommentsYearMonth" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT DISTINCT METRIC_YEAR FROM CHNGMETRICS.VW_BRD_LEGEND ORDER BY METRIC_YEAR"></asp:SqlDataSource>
             <br />
             <br />
             <br />
             <%--GRID VIEW BEGINS HERE--%>
             <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>
                     
                     <%-- LEGEND ID --%>
                     <asp:TemplateField>
                         <ItemTemplate>
                             <asp:Label  Visible="false" ID="lblID" runat="server" Text='<%# Bind("BRD_LEGEND_ID") %>'>
    
                            </asp:Label>
                         </ItemTemplate>
                         <EditItemTemplate>
                             <asp:Label ID="txtID" Visible="false" runat="server" Text='<%# Bind("BRD_LEGEND_ID") %>'>
    
                            </asp:Label>
                         </EditItemTemplate>
                     </asp:TemplateField>
    
                    <%-- LEGEND YEAR --%>
                     <asp:TemplateField HeaderText="Year" SortExpression="METRIC_YEAR">
                         <ItemTemplate>
                             <asp:Label ID="lblLegendYear" Visible="true" runat="server" Text='<%# Bind("METRIC_YEAR") %>'>
    
                            </asp:Label>
                         </ItemTemplate>
                         <EditItemTemplate>
                             <asp:Label ID="txtLegendYear" runat="server" Text='<%# Bind("METRIC_YEAR") %>'>
    
                            </asp:Label>
                         </EditItemTemplate>
                     </asp:TemplateField>
    
                    <%-- LEGEND MONTH --%>
                     <asp:TemplateField HeaderText="Month">
                         <ItemTemplate>
                             <asp:Label ID="lblLegendMonth" Visible="true" runat="server" Text='<%# Bind("METRIC_MONTH_TXT") %>'>
    
                            </asp:Label>
                         </ItemTemplate>
                         <EditItemTemplate>
                             <asp:Label ID="txtLegendMonth" runat="server" Text='<%# Bind("METRIC_MONTH_TXT") %>'>
    
                            </asp:Label>
                         </EditItemTemplate>
                     </asp:TemplateField>
    
                    <%-- LEGEND TAB --%>
                     <asp:TemplateField HeaderText="Tab">
                         <ItemTemplate>
                             <asp:Label ID="lblLegendTab" Visible="true" runat="server" Text='<%# Bind("TAB_NAME") %>'>
    
                            </asp:Label>
                         </ItemTemplate>
                         <EditItemTemplate>
                             <asp:Label ID="txtLegendTab" runat="server" Text='<%# Bind("TAB_NAME") %>'>
    
                            </asp:Label>
                         </EditItemTemplate>
                     </asp:TemplateField>
    
                    <%-- LEGEND SECTION --%>
                     <asp:TemplateField HeaderText="Section">
                         <ItemTemplate>
                             <asp:Label ID="lblLegendSection" Visible="true" runat="server" Text='<%# Bind("SECTION_NAME") %>'>
    
                            </asp:Label>
                         </ItemTemplate>
                         <EditItemTemplate>
                             <asp:Label ID="txtLegendSection" runat="server" Text='<%# Bind("SECTION_NAME") %>'>
    
                            </asp:Label>
                         </EditItemTemplate>
                     </asp:TemplateField>
    
                    <%-- LEGEND ORDER --%>
                     <asp:TemplateField HeaderText="Order" SortExpression="LEGEND_ORDER">
                         <ItemTemplate>
                             <asp:Label ID="lblLegendOrder" runat="server" Text='<%# Bind("LEGEND_ORDER") %>'>
    
                            </asp:Label>
                         </ItemTemplate>
                         <EditItemTemplate>
                             <asp:TextBox ID="txtLegendOrder" Width="20px" runat="server" Text='<%# Bind("LEGEND_ORDER") %>'></asp:TextBox>
                             <asp:RequiredFieldValidator ID="rfvUpdateLegendOrder" ValidationGroup="UPDATE" runat="server" 
                             ErrorMessage="Legend order is required." ForeColor="Brown"  Display="Dynamic" ControlToValidate="txtLegendOrder"></asp:RequiredFieldValidator>
                         </EditItemTemplate>
                     </asp:TemplateField>
                     
                     <%-- LEGEND TEXT --%>
                     <asp:TemplateField HeaderStyle-HorizontalAlign="Center" HeaderText="Legends">
                         <ItemTemplate>
                             <asp:Label ID="lblLegend" runat="server" Text='<%# Bind("LEGEND_TEXT") %>'>
    
                            </asp:Label>
                         </ItemTemplate>
                         <EditItemTemplate>
                             <asp:TextBox ID="txtEditLegend" TextMode="MultiLine" Width="400px" Height="30px" runat="server" Text='<%# Bind("LEGEND_TEXT") %>'></asp:TextBox>
                             <asp:RequiredFieldValidator ID="rfvUpdateLegendText" ValidationGroup="UPDATE" runat="server" 
                             ErrorMessage="Please enter legend" ForeColor="brown" ControlToValidate="txtEditLegend"></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 legend?');" 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 Wrap="true" 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>
             <br />
             <br />
             </form>
     </body>
     </html>
    

    Code Behind:

    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 ChngMetricsLegends : 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()
             {
                 String queryStr = "SELECT BRD_LEGEND_ID, METRIC_YEAR, METRIC_MONTH_TXT, TAB_NAME, SECTION_NAME, LEGEND_ORDER, LEGEND_TEXT FROM CHNGMETRICS.VW_BRD_LEGEND WHERE 1=1";
                 if (DropDownYear.SelectedValue != "")
                 {
                     queryStr += " AND METRIC_YEAR = :METRIC_YEAR";
    
                }
                 if (DropDownMonth.SelectedValue != "")
                 {
                     queryStr += " AND METRIC_MONTH = :METRIC_MONTH";
    
                }
                 if (DropDownSectionName.SelectedValue != "")
                 {
                     queryStr += " AND SECTION_NAME = :SECTION_NAME";
    
                }
    
                if (DropDownTabName.SelectedValue != "")
                 {
                     queryStr += " AND TAB_NAME = :TAB_NAME";
    
                }
    
                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;
             }
    
            protected void DropDownYear_SelectedIndexChanged(object sender, EventArgs e)
             {
                 GridView1.DataSource = RefreshGrid();
                 GridView1.DataBind();
             }
    
            protected void DropDownMonth_SelectedIndexChanged(object sender, EventArgs e)
             {
                 GridView1.DataSource = RefreshGrid();
                 GridView1.DataBind();
             }
             protected void DropDownSectionName_SelectedIndexChanged(object sender, EventArgs e)
             {
                 GridView1.DataSource = RefreshGrid();
                 GridView1.DataBind();
             }
    
            protected void DropDownTabName_SelectedIndexChanged(object sender, EventArgs e)
             {
                 GridView1.DataSource = RefreshGrid();
                 GridView1.DataBind();
             }
             protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
             {
                 GridView1.EditIndex = e.NewEditIndex;
                 //GridView1.DataSource = RefreshGrid();
                 //GridView1.DataBind();
                 DataTable dt = RefreshGrid();
                 if (ViewState["Sort"] != null)
                 {
                     dt.DefaultView.Sort = (string)ViewState["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();
             }
    
            protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
             {
                 if (Page.IsValid)
                 {
                     logger.Debug("Debugging update statement");
                     logger.Error("Catching error(s) while updating data");
                     Label id = GridView1.Rows[e.RowIndex].FindControl("txtID") as Label;
                     TextBox legend = GridView1.Rows[e.RowIndex].FindControl("txtEditLegend") as TextBox;
                     TextBox order = GridView1.Rows[e.RowIndex].FindControl("txtLegendOrder") as TextBox;
                     //var updateQuery = ("UPDATE CHNGMETRICS.VW_BRD_LEGEND SET LEGEND_ORDER ='" + order.Text + "', LEGEND_TEXT = '" + legend.Text + "' WHERE BRD_LEGEND_ID = '" + Convert.ToInt32(id.Text) + "' ");
                     var updateQuery = ("UPDATE CHNGMETRICS.VW_BRD_LEGEND SET LEGEND_ORDER = :LEGENDORDER , LEGEND_TEXT = :LEGENDTEXT WHERE BRD_LEGEND_ID = :BRDLEGENDID ");
                     OracleCommand command = new OracleCommand(updateQuery);
    
                    //Add PARAMETERS FOR UPDATE STATEMENT
                     command.Parameters.Add(new OracleParameter("LEGENDORDER", order.Text));
                     command.Parameters.Add(new OracleParameter("LEGENDTEXT", legend.Text));
                     command.Parameters.Add(new OracleParameter("BRDLEGENDID", Convert.ToInt32(id.Text)));
                     GridView1.EditIndex = -1;
                     Utility.Exec(command);
                     //GridView1.DataSource = RefreshGrid();
                     //GridView1.DataBind();
                     DataTable dt = RefreshGrid();
                     if (ViewState["Sort"] != null)
                     {
                         dt.DefaultView.Sort = (string)ViewState["Sort"];
                     }
                     GridView1.DataSource = dt;
                     GridView1.DataBind();
                     //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;
                 //GridView1.DataSource = RefreshGrid();
                 //GridView1.DataBind();
                 DataTable dt = RefreshGrid();
                 if (ViewState["Sort"] != null)
                 {
                     dt.DefaultView.Sort = (string)ViewState["Sort"];
                 }
                 GridView1.DataSource = dt;
                 GridView1.DataBind();
             }
    
            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_LEGEND WHERE BRD_LEGEND_ID = '" + Convert.ToInt32(id.Text) + "' ");
                 var deleteQuery = ("DELETE FROM CHNGMETRICS.VW_BRD_LEGEND WHERE BRD_LEGEND_ID = :BRDLEGENDID ");
                 OracleCommand command = new OracleCommand(deleteQuery);
                 //Add PARAMETERS FOR DELETE STATEMENT
                 command.Parameters.Add(new OracleParameter("BRDLEGENDID", Convert.ToInt32(id.Text)));
                 Utility.Exec(command);
                 //GridView1.DataSource = RefreshGrid();
                 //GridView1.DataBind();
                 DataTable dt = RefreshGrid();
                 if (ViewState["Sort"] != null)
                 {
                     dt.DefaultView.Sort = (string)ViewState["Sort"];
                 }
                 GridView1.DataSource = dt;
                 GridView1.DataBind();
                 //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_LEGEND VALUES ('" + "', '" + "', '" + "', '" + "', '" + drpDownYear.Text + "', '" + month + "', '" + drpDownMonth.Text + "', '" + drpDownTabName.Text + "', '" + drpDownSectionName.Text + "', '" + drpDownLegendOrder.Text + "', '" + txtLegend.Text + "')");
    
                var insertQuery = ("INSERT INTO CHNGMETRICS.VW_BRD_LEGEND (METRIC_YEAR, METRIC_MONTH, METRIC_MONTH_TXT, TAB_NAME, SECTION_NAME, LEGEND_ORDER, LEGEND_TEXT) VALUES  (:YEAR, :MONTH_NUM, :MONTH_TEXT, :TAB_NAME, :SECTION_NAME, :LEGEND_ORDER, :LEGEND_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("LEGEND_ORDER", txtLgndOrder.Text));
                 command.Parameters.Add(new OracleParameter("LEGEND_TEXT", txtLegend.Text));
    
                Utility.Exec(command);
                 GridView1.DataSource = RefreshGrid();
                 GridView1.DataBind();
    
                //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;
    
    
                 txtLegend.Text = String.Empty;
                 txtLgndOrder.Text = String.Empty;
                 drpDownYear.Text = "-1";
                 drpDownMonth.Text = "-1";
                 drpDownTabName.Text = "-1";
                 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)
             {
                 if (drpDownTabName.SelectedValue == "Watch List" || drpDownTabName.SelectedValue == "General Stats" || drpDownTabName.SelectedValue == "Planning" || drpDownTabName.SelectedValue == "CAB")
                 {
                     drpDownSectionName.Text = "Main";
                 }
             }
    
            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.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;
             }
    
            protected void btnViewAll_Click(object sender, EventArgs e)
             {
                 DropDownYear.ClearSelection();
                 DropDownMonth.ClearSelection();
                 DropDownTabName.ClearSelection();
                 DropDownSectionName.ClearSelection();
                 GridView1.DataSource = RefreshGrid();
                 GridView1.DataBind();
             }
         }
     }
    

    Tuesday, February 6, 2018 5:01 PM

All replies