locked
Data to pick from DB as per Month and Year selected RRS feed

  • Question

  • User810354248 posted

    In my asp.net +VB+SQL DB web. I have a table named .

    In a web page i want to display data as per date selection. But i dint want the date but Month and Year part to be selected from date and show data in grid view.

    i tried this code

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:baijuep %>" SelectCommand="SELECT * FROM [Tpt_2Mile] WHERE ([Date_] = @Date_)">
            <SelectParameters>
                <asp:ControlParameter ControlID="dttxt" Name="Date_" PropertyName="Text" Type="DateTime" />
            </SelectParameters>
        </asp:SqlDataSource>

    But above code shows the selected date, Month, Year 

    I will select date and i want the data of complete Moth and Year of that date.

    Monday, June 11, 2018 8:39 PM

All replies

  • User283571144 posted

    Hi Baiju EP,

    I will select date and i want the data of complete Moth and Year of that date.

    According to your description, I couldn't understand clearly about your requirement.

    Do you mean you want to select the database if the textbox value is just year like 2018 or month like 2018/6?

    Could you please tell me which the date format you have used for the Date_ column? 

    Do you have a colum named month or year?

    If you could post more details information, it will be more easily for us to understand your requirement and find a solution.

    Best Regards,

    Brando

    Tuesday, June 12, 2018 6:35 AM
  • User810354248 posted

    in my datepicker thw date will show like this 06/13/2018

    But i want to fetch data from Database and show in gridview  as per Month and year only.

    Wednesday, June 13, 2018 5:38 AM
  • User283571144 posted

    Hi Baiju EP,

    According to your description, I suggest you could consider using jquery ui datapicker to achieve only selecting the month and year in the textbox.

    Then you could get the start date and end date according to the selected data.

    At last you could modify the sqldatasource select command and get the data.

    More dtails, you could refer to below test demo codes:

    Notice: You should install the jquery ui from Nuget Package

    Install-Package jQuery.UI.Combined -Version 1.12.1	
    

    ASPX:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DataPickEr.aspx.cs" Inherits="TestWebForm.DataPickEr" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>    <script src="Scripts/jquery-1.12.4.js"></script>
     
        <script src="Scripts/jquery-ui-1.12.1.min.js"></script>
        <style>
            .ui-datepicker-calendar {
        display: none;
        }
        </style>
       <script>
           $(function () {
               $('.date-picker').datepicker(
                              {
                                  dateFormat: "yy/mm",
                                  changeMonth: true,
                                  changeYear: true,
                                  showButtonPanel: true,
                                  onClose: function (dateText, inst) {
                                      function isDonePressed() {
                                          return ($('#ui-datepicker-div').html().indexOf('ui-datepicker-close ui-state-default ui-priority-primary ui-corner-all ui-state-hover') > -1);
                                      }
                                      if (isDonePressed()) {
                                          var month = $("#ui-datepicker-div .ui-datepicker-month :selected").val();
                                          var year = $("#ui-datepicker-div .ui-datepicker-year :selected").val();
                                          $(this).datepicker('setDate', new Date(year, month, 1)).trigger('change');
                                          $('.date-picker').focusout()//Added to remove focus from datepicker input box on selecting date
                                      }
                                  },
                                  beforeShow: function (input, inst) {
                                      inst.dpDiv.addClass('month_year_datepicker')
                                      if ((datestr = $(this).val()).length > 0) {
                                          year = datestr.substring(datestr.length - 4, datestr.length);
                                          month = datestr.substring(0, 2);
                                          $(this).datepicker('option', 'defaultDate', new Date(year, month - 1, 1));
                                          $(this).datepicker('setDate', new Date(year, month - 1, 1));
                                          $(".ui-datepicker-calendar").hide();
                                      }
                                  }
                              })
           });
       </script>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
           <label for="startDate">Date :</label>
             <asp:TextBox ID="startDate" runat="server" class="date-picker"></asp:TextBox>
            <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
                    <br />
            <br />
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="Id" DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="Id" HeaderText="Id" ReadOnly="True" SortExpression="Id" />
                    <asp:BoundField DataField="MainCode" HeaderText="MainCode" SortExpression="MainCode" />
                    <asp:BoundField DataField="TestDate" HeaderText="TestDate" SortExpression="TestDate" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:aspnet-TestMVC-20170430045951ConnectionString %>" SelectCommand="SELECT * FROM [TableA]"></asp:SqlDataSource>
        </div>
        </form>
    </body>
    </html>
    

    Code-behind:

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    
    namespace TestWebForm
    {
        public partial class DataPickEr : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
    
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                string d1 = startDate.Text + "/01";
                DateTime startTime = DateTime.Parse(startDate.Text);
                DateTime endTime = GetFirstDayOfNextMonth(startTime);
                SqlDataSource1.SelectParameters.Clear();
                SqlDataSource1.SelectCommand = "SELECT * FROM [TableA] where TestDate > @Start and TestDate < @End";
                SqlDataSource1.SelectParameters.Add("Start", DbType.DateTime, startTime.ToString());
                SqlDataSource1.SelectParameters.Add("End", DbType.DateTime, endTime.ToString());
                SqlDataSource1.DataBind();
                
            }
            protected DateTime GetFirstDayOfNextMonth(DateTime startTime)
            {
                int monthNumber, yearNumber;
                if (startTime.Month == 12)
                {
                    monthNumber = 1;
                    yearNumber = startTime.Year + 1;
                }
                else
                {
                    monthNumber = startTime.Month + 1;
                    yearNumber = startTime.Year;
                }
                DateTime lastDate = new DateTime(yearNumber, monthNumber, 1);
                return lastDate;
            }
        }
    }

    Result:

    Best Regards,

    Brando

    Friday, June 15, 2018 2:33 AM