Filter GridView records using Start and End date in ASP.Net

sagarnayak
 
on Apr 30, 2014 11:11 PM
15257 Views

hello sir i have a tablewhere datas are inserted and there is a colum foor inserting date on which data are inserted , so thatcolumn has datatype datetime and in design i have teo textbox one takes value for from date and the other for to date. on submit button click i want to display the datas between two dates. so i converted textbox value to datetime and it is showing data but the problem is suppose i inserted data on 29/04/2014 .now i want to view data inserted between 28/04/2014 and 29/04/2014 it doe not show any data but when i check between 29/--/-- and 30/--/-- i t display the data

Download FREE API for Word, Excel and PDF in ASP.Net: Download
Azim
 
on May 02, 2014 11:11 PM
on May 05, 2014 11:06 PM

HTML

<cc1:ToolkitScriptManager runat="server">
</cc1:ToolkitScriptManager>
<asp:TextBox ID="txtFrom" runat="server" CssClass="Date"></asp:TextBox>
<asp:ImageButton ID="btnFrom" runat="server" ImageUrl="calender.png" AlternateText="Caleder" />
<cc1:CalendarExtender runat="server" PopupButtonID="btnFrom" TargetControlID="txtFrom"
    Format="dd/MM/yyyy">
</cc1:CalendarExtender>
<asp:TextBox ID="txtTo" runat="server" CssClass="Date"></asp:TextBox>
<asp:ImageButton ID="btnTo" runat="server" ImageUrl="calender.png" AlternateText="Caleder" />
<cc1:CalendarExtender runat="server" PopupButtonID="btnTo" TargetControlID="txtTo"
    Format="dd/MM/yyyy">
</cc1:CalendarExtender>
<asp:Button ID="btnSave" runat="server" Text="Search" OnClick="btnSearch_Click" />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true">
</asp:GridView>

Namespace

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Globalization

Code

C#

protected void btnSearch_Click(object sender, EventArgs e)
{
    string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT OrderID, OrderDate, ShipName, ShipCity FROM Orders WHERE OrderDate BETWEEN @From AND @To", con))
        {
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                cmd.Parameters.AddWithValue("@From", Convert.ToDateTime(this.txtFrom.Text, new CultureInfo("en-GB")));
                cmd.Parameters.AddWithValue("@To", Convert.ToDateTime(this.txtTo.Text, new CultureInfo("en-GB")));
                DataSet ds = new DataSet();
                da.Fill(ds);
                GridView1.DataSource = ds;
                GridView1.DataBind();
            }
        }
    }
}

VB.Net

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("ConString").ConnectionString

    Using con As SqlConnection = New SqlConnection(constr)

        Using cmd As SqlCommand = New SqlCommand("SELECT OrderID, OrderDate, ShipName, ShipCity FROM Orders WHERE OrderDate BETWEEN @From AND @To", con)

            Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                cmd.Parameters.AddWithValue("@From", Convert.ToDateTime(Me.txtFrom.Text, New CultureInfo("en-GB")))
                cmd.Parameters.AddWithValue("@To", Convert.ToDateTime(Me.txtTo.Text, New CultureInfo("en-GB")))
                Dim ds As DataSet = New DataSet()
                da.Fill(ds)
                GridView1.DataSource = ds
                GridView1.DataBind()
            End Using
        End Using
    End Using
End Sub

Screenshot