Search and display records between two Dates in ASP.Net GridView using C# and VB.Net

nedash says:

cmd.Parameters.AddWithValue("@txtaz", DateTime.Now);

DateTime.Now will be current date not the null.

So, you need to pass null value instead of DateTime.Now.

Inside the procedure parameters need to check with IS NULL.

Check the below example.

Database

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

Download Northwind Database

Stored Procedure

CREATE PROCEDURE GetOrdersDateWiseWithPaging
      @PageIndex INT = 1
      ,@PageSize INT = 10
	  ,@From DATETIME = NULL
      ,@To DATETIME = NULL
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
          ORDER BY [CustomerID] ASC
      )AS RowNumber,*
      INTO #Results
      FROM [Orders]
     
      SELECT @RecordCount = COUNT(*)
      FROM #Results
	  WHERE (OrderDate BETWEEN @From AND @To) OR (@From IS NULL OR @To IS NULL)
           
      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
     
      DROP TABLE #Results
END

HTML

C#

From:
<asp:TextBox runat="server" ID="txtFrom" />
<br />
To:
<asp:TextBox runat="server" ID="txtTo" /> <br />
<asp:Button Text="Search" runat="server" OnClick="OnSearch" />
<asp:GridView ID="gvOrders" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="OrderID" HeaderText="Id" />
        <asp:BoundField DataField="OrderDate" HeaderText="Date" />
        <asp:BoundField DataField="Freight" HeaderText="Freight" />
    </Columns>
</asp:GridView>
<br />
<asp:Repeater ID="rptPager" runat="server">
    <ItemTemplate>
        <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
            CssClass='<%# Convert.ToBoolean(Eval("Enabled")) ? "page_enabled" : "page_disabled" %>'
            OnClick="Page_Changed" OnClientClick='<%# !Convert.ToBoolean(Eval("Enabled")) ? "return false;" : "" %>'></asp:LinkButton>
    </ItemTemplate>
</asp:Repeater>

VB.Net

From:
<asp:TextBox runat="server" ID="txtFrom" />
<br />
To:
<asp:TextBox runat="server" ID="txtTo" />
<br />
<asp:Button Text="Search" runat="server" OnClick="OnSearch" />
<asp:GridView ID="gvOrders" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="OrderID" HeaderText="Id" />
        <asp:BoundField DataField="OrderDate" HeaderText="Date" />
        <asp:BoundField DataField="Freight" HeaderText="Freight" />
    </Columns>
</asp:GridView>
<br />
<asp:Repeater ID="rptPager" runat="server">
    <ItemTemplate>
        <asp:LinkButton ID="lnkPage" runat="server" Text='<%#Eval("Text") %>' CommandArgument='<%# Eval("Value") %>'
            CssClass='<%# If(Convert.ToBoolean(Eval("Enabled")), "page_enabled", "page_disabled")%>'
            OnClick="Page_Changed" OnClientClick='<%# If(Not Convert.ToBoolean(Eval("Enabled")), "return false;", "") %>'></asp:LinkButton>
    </ItemTemplate>
</asp:Repeater>

Namespaces

C#

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

VB.Net

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

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.GetCustomersPageWise(1);
    }
}

private void GetCustomersPageWise(int pageIndex)
{
    string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("GetOrdersDateWiseWithPaging", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
            cmd.Parameters.AddWithValue("@PageSize", int.Parse("10"));

            if (!string.IsNullOrEmpty(txtFrom.Text.Trim()))
            {
                cmd.Parameters.AddWithValue("@From", Convert.ToDateTime(txtFrom.Text.Trim()));
            }
            else
            {
                cmd.Parameters.AddWithValue("@From", DBNull.Value);
            }

            if (!string.IsNullOrEmpty(txtTo.Text.Trim()))
            {
                cmd.Parameters.AddWithValue("@To", Convert.ToDateTime(txtTo.Text.Trim()));
            }
            else
            {
                cmd.Parameters.AddWithValue("@To", DBNull.Value);
            }

            cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
            con.Open();
            IDataReader idr = cmd.ExecuteReader();
            gvOrders.DataSource = idr;
            gvOrders.DataBind();
            idr.Close();
            con.Close();
            int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
            this.PopulatePager(recordCount, pageIndex);
        }
    }
}

protected void Page_Changed(object sender, EventArgs e)
{
    int pageIndex = int.Parse((sender as LinkButton).CommandArgument);
    this.GetCustomersPageWise(pageIndex);
}

protected void OnSearch(object sender, EventArgs e)
{
    this.GetCustomersPageWise(1);
}

private void PopulatePager(int recordCount, int currentPage)
{
    double dblPageCount = (double)((decimal)recordCount / (decimal)10);
    int pageCount = (int)Math.Ceiling(dblPageCount);
    List<ListItem> pages = new List<ListItem>();
    if (pageCount > 0)
    {
        pages.Add(new ListItem("<<", "1", currentPage > 1));
        if (currentPage != 1)
        {
            pages.Add(new ListItem("Previous", (currentPage - 1).ToString()));
        }
        if (pageCount < 4)
        {
            for (int i = 1; i <= pageCount; i++)
            {
                pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
            }
        }
        else if (currentPage < 4)
        {
            for (int i = 1; i <= 4; i++)
            {
                pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
            }
            pages.Add(new ListItem("...", (currentPage).ToString(), false));
        }
        else if (currentPage > pageCount - 4)
        {
            pages.Add(new ListItem("...", (currentPage).ToString(), false));
            for (int i = currentPage - 1; i <= pageCount; i++)
            {
                pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
            }
        }
        else
        {
            pages.Add(new ListItem("...", (currentPage).ToString(), false));
            for (int i = currentPage - 2; i <= currentPage + 2; i++)
            {
                pages.Add(new ListItem(i.ToString(), i.ToString(), i != currentPage));
            }
            pages.Add(new ListItem("...", (currentPage).ToString(), false));
        }
        if (currentPage != pageCount)
        {
            pages.Add(new ListItem("Next", (currentPage + 1).ToString()));
        }
        pages.Add(new ListItem(">>", pageCount.ToString(), currentPage < pageCount));
    }
    rptPager.DataSource = pages;
    rptPager.DataBind();
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.GetCustomersPageWise(1)
    End If
End Sub

Private Sub GetCustomersPageWise(ByVal pageIndex As Integer)
    Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constring)
        Using cmd As SqlCommand = New SqlCommand("GetOrdersDateWiseWithPaging", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
            cmd.Parameters.AddWithValue("@PageSize", Integer.Parse("10"))
            If Not String.IsNullOrEmpty(txtFrom.Text.Trim()) Then
                cmd.Parameters.AddWithValue("@From", Convert.ToDateTime(txtFrom.Text.Trim()))
            Else
                cmd.Parameters.AddWithValue("@From", DBNull.Value)
            End If

            If Not String.IsNullOrEmpty(txtTo.Text.Trim()) Then
                cmd.Parameters.AddWithValue("@To", Convert.ToDateTime(txtTo.Text.Trim()))
            Else
                cmd.Parameters.AddWithValue("@To", DBNull.Value)
            End If

            cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4)
            cmd.Parameters("@RecordCount").Direction = ParameterDirection.Output
            con.Open()
            Dim idr As IDataReader = cmd.ExecuteReader()
            gvOrders.DataSource = idr
            gvOrders.DataBind()
            idr.Close()
            con.Close()
            Dim recordCount As Integer = Convert.ToInt32(cmd.Parameters("@RecordCount").Value)
            Me.PopulatePager(recordCount, pageIndex)
        End Using
    End Using
End Sub

Protected Sub Page_Changed(ByVal sender As Object, ByVal e As EventArgs)
    Dim pageIndex As Integer = Integer.Parse((TryCast(sender, LinkButton)).CommandArgument)
    Me.GetCustomersPageWise(pageIndex)
End Sub

Protected Sub OnSearch(ByVal sender As Object, ByVal e As EventArgs)
    Me.GetCustomersPageWise(1)
End Sub

Private Sub PopulatePager(ByVal recordCount As Integer, ByVal currentPage As Integer)
    Dim dblPageCount As Double = CDbl((CDec(recordCount) / CDec(10)))
    Dim pageCount As Integer = CInt(Math.Ceiling(dblPageCount))
    Dim pages As List(Of ListItem) = New List(Of ListItem)()

    If pageCount > 0 Then
        pages.Add(New ListItem("<<", "1", currentPage > 1))

        If currentPage <> 1 Then
            pages.Add(New ListItem("Previous", (currentPage - 1).ToString()))
        End If

        If pageCount < 4 Then

            For i As Integer = 1 To pageCount
                pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
            Next
        ElseIf currentPage < 4 Then

            For i As Integer = 1 To 4
                pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
            Next

            pages.Add(New ListItem("...", (currentPage).ToString(), False))
        ElseIf currentPage > pageCount - 4 Then
            pages.Add(New ListItem("...", (currentPage).ToString(), False))

            For i As Integer = currentPage - 1 To pageCount
                pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
            Next
        Else
            pages.Add(New ListItem("...", (currentPage).ToString(), False))

            For i As Integer = currentPage - 2 To currentPage + 2
                pages.Add(New ListItem(i.ToString(), i.ToString(), i <> currentPage))
            Next

            pages.Add(New ListItem("...", (currentPage).ToString(), False))
        End If

        If currentPage <> pageCount Then
            pages.Add(New ListItem("Next", (currentPage + 1).ToString()))
        End If

        pages.Add(New ListItem(">>", pageCount.ToString(), currentPage < pageCount))
    End If

    rptPager.DataSource = pages
    rptPager.DataBind()
End Sub

Screenshot