Search (Filter) ASP.Net GridView using Start and End Date in C# and VB.Net

Last Reply 4 months ago By pandeyism

Posted 4 months ago

I have table ,col of entrydate which type is datetime,now i want search record by date ,i have tried but where entrdate like this 2019-04-30 15:01:14.000,then this data does not get display in gridview ,if entrydate like this 2019-04-28 00:00:00.000 then data get display here is my html code 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="AllproducBale.aspx.cs" Inherits="Saleorder.AllproducBale" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
             <table class="auto-style1">
            <tr>
                <td class="auto-style2">&nbsp;</td>
                <td>
                    <h1>All Produce Bale</h1>
                </td>
                <td>&nbsp;</td>
            </tr>
            <tr>
                <td class="auto-style2">&nbsp;</td>
                <td>
                    <h4>DateSearch : From&nbsp;
                        <asp:TextBox ID="txtstart" runat="server"></asp:TextBox>
                        &nbsp;To:
                        <asp:TextBox ID="txtend" runat="server"></asp:TextBox>
                        <asp:Button ID="Button1" runat="server" Text="Search" OnClick="Button1_Click" />
                        <asp:LinkButton ID="exportExcel" runat="server" OnClick="exportExcel_Click">ExportExcel</asp:LinkButton>
                        <asp:LinkButton ID="PDF" runat="server" OnClick="PDF_Click">PDF</asp:LinkButton>
                    </h4>
                </td>
                <td>&nbsp;</td>
            </tr>
            <tr>
                <td class="auto-style2">&nbsp;</td>
                <td>
                    <div style ="height:500px; width:800px; overflow:auto;">


                    <asp:GridView ID="GVProducebale" runat="server" BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Horizontal" AutoGenerateColumns = "true" Font-Names = "Arial" ShowHeader = "true" 
                        Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B" class="grid_scroll" PageSize="5" Width="788px">
                    </asp:GridView>
                        </div>
                </td>
                <td>&nbsp;</td>
            </tr>
        </table>
        </div>
    </form>
</body>
</html>

c# code 

        protected void Button1_Click(object sender, EventArgs e)
        { 
            con = new SqlConnection("Data Source=DESKTOP-5PJ76B9;Integrated Security=SSPI;Initial Catalog=SilverProduction;MultipleActiveResultSets=True;");
            con.Open();
            {
                SqlCommand cmd = new SqlCommand("Searchbaledatewise");
                cmd.Parameters.Add(new SqlParameter("@StartDate", txtstart.Text));
                cmd.Parameters.Add(new SqlParameter("@EndDate", txtend.Text));                       
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                da.Fill(dt);
                GVProducebale.DataSource = dt;
                GVProducebale.DataBind();
                con.Close();
            }
        }

 

Create PROCEDURE [dbo].[Searchbaledatewise]
	-- Add the parameters for the stored procedure here
	@StartDate DateTime,
@Enddate   DateTime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
		SELECT     Probale.Prdno AS BalNo, SalesOrder.OrderNo, ItemMasterFile.Descriptionitem,Probale.prdqty AS QTY,ItemMasterFile.BaleSize,  SalesOrder.Status , salesorder.Order_Ref_No as Ref_ID,Worker.Namworker  as worker,Probale.EntryDate
FROM         Probale INNER JOIN
                      SalesOrder ON SalesOrder.OrderNo = Probale.OrderNo INNER JOIN
                      ItemMasterFile ON ItemMasterFile.CodeItem = Probale.Codeitem
                      inner Join Worker on Worker.IDwokrer=Probale.IDwokrer
                   where Probale.EntryDate between @StartDate and @Enddate
END

Please guide

Posted 4 months ago Modified on 4 months ago

Hi akhter,

Refer below sample.

Database

For this sample I have used of NorthWind database that you can download using the link given below.

Download Northwind Database

SQL

CREATE PROCEDURE SearchCustomerDetailsByDate
	@StartDate DATETIME,
	@EndDate DATETIME
AS
BEGIN
	SELECT TOP 5 OrderID
		  ,CONVERT(VARCHAR,OrderDate,105) OrderDate
		  ,ShipName
		  ,ShipCity
		  ,ShipCountry
	FROM Orders WHERE OrderDate BETWEEN @StartDate AND @EndDate
END

Namespaces

C#

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

VB.Net

Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT TOP 5 OrderID ,OrderDate ,ShipName ,ShipCity ,ShipCountry FROM Orders ", con))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    this.gvOrders.DataSource = dt;
                    this.gvOrders.DataBind();
                }
            }
        }
    }
}

protected void Search(object sender, EventArgs e)
{
    DataTable dt = GetDate(txtFromDate.Text, txtEndDate.Text);
    this.gvOrders.DataSource = dt;
    this.gvOrders.DataBind();
}

private DataTable GetDate(string fromdate, string enddate)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SearchCustomerDetailsByDate", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@StartDate", fromdate);
            cmd.Parameters.AddWithValue("@EndDate", enddate);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(constr)
            Using cmd As SqlCommand = New SqlCommand("SELECT TOP 5 OrderID ,OrderDate ,ShipName ,ShipCity ,ShipCountry FROM Orders ", con)
                Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable()
                    da.Fill(dt)
                    Me.gvOrders.DataSource = dt
                    Me.gvOrders.DataBind()
                End Using
            End Using
        End Using
    End If
End Sub

Protected Sub Search(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = GetDate(txtFromDate.Text, txtEndDate.Text)
    Me.gvOrders.DataSource = dt
    Me.gvOrders.DataBind()
End Sub

Private Function GetDate(ByVal fromdate As String, ByVal enddate As String) As DataTable
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SearchCustomerDetailsByDate", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@StartDate", fromdate)
            cmd.Parameters.AddWithValue("@EndDate", enddate)
            Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                da.Fill(dt)
                Return dt
            End Using
        End Using
    End Using
End Function

Screenshot