Display record count in ASP.Net GridView based on TextBox value using C# and VB.Net

Check this test query. Now please take its reference and correct your code. You need to call this in your code to populate the GridView.

SQL

DECLARE @Test AS TABLE(Sno INT,Status VARCHAR(10),FromDate DATETIME,ToDate DATETIME)
INSERT INTO @Test VALUES(1,'Pending','02/20/2017','02/26/2017')
INSERT INTO @Test VALUES(2,'Hold','01/10/2017','01/25/2017')
INSERT INTO @Test VALUES(3,'Completed','02/21/2017','02/26/2017')
INSERT INTO @Test VALUES(4,'Completed','02/22/2017','02/28/2017')
INSERT INTO @Test VALUES(5,'Hold','01/15/2017','01/30/2017')
INSERT INTO @Test VALUES(6,'Pending','01/20/2017','02/22/2017')

DECLARE @DateTime DATETIME
SET @DateTime = '02/25/2017'

SELECT 
(SELECT COUNT(*) FROM @Test WHERE @DateTime BETWEEN FromDate AND ToDate AND Status = 'Pending') 'Pending'
,(SELECT COUNT(*) FROM @Test WHERE @DateTime BETWEEN FromDate AND ToDate AND Status = 'Hold') 'Hold'
,(SELECT COUNT(*) FROM @Test WHERE @DateTime BETWEEN FromDate AND ToDate AND Status = 'Completed') 'Completed'

Output

Hi kavithav,

Check this example. Now please take its reference and correct your code.

HTML

<asp:TextBox runat="server" ID="txtSearch" />
<asp:Button Text="Search" runat="server" OnClick="Search" />
<br />
<asp:GridView runat="server" ID="gvDetails" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Sno" HeaderText="Sno" />
        <asp:BoundField DataField="Status" HeaderText="Status" />
        <asp:BoundField DataField="FromDate" HeaderText="From Date" DataFormatString="{0:MM/dd/yyyy}" />
        <asp:BoundField DataField="ToDate" HeaderText="To Date" DataFormatString="{0:MM/dd/yyyy}" />
    </Columns>
</asp:GridView>
<br />
<asp:GridView runat="server" ID="gvStatus" AutoGenerateColumns="false" Caption="<b>Status</b>">
    <Columns>
        <asp:BoundField DataField="Pending" HeaderText="Pending" />
        <asp:BoundField DataField="Hold" HeaderText="Hold" />
        <asp:BoundField DataField="Completed" HeaderText="Completed" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data;

VB.Net

Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        gvDetails.DataSource = GetData();
        gvDetails.DataBind();
    }
}

protected void Search(object sender, EventArgs e)
{
    DateTime datatime = Convert.ToDateTime(txtSearch.Text.Trim());
    DataRow[] dataRows = GetData().Select(string.Format("#{0}# >= FromDate AND #{0}# <= ToDate", datatime));
    int pending = 0, hold = 0, completed = 0;
    DataTable dtStatus = new DataTable();
    dtStatus.Columns.AddRange(new DataColumn[] { new DataColumn("Pending"), new DataColumn("Hold"), new DataColumn("Completed") });
    for (int i = 0; i < dataRows.Length; i++)
    {
        if (dataRows[i].ItemArray[1].ToString().ToUpper() == "PENDING")
        {
            pending++;
        }
        if (dataRows[i].ItemArray[1].ToString().ToUpper() == "HOLD")
        {
            hold++;
        }
        if (dataRows[i].ItemArray[1].ToString().ToUpper() == "COMPLETED")
        {
            completed++;
        }
    }
    if (pending > 0 || hold > 0 || completed > 0)
    {
        dtStatus.Rows.Add(pending, hold, completed);
        gvStatus.DataSource = dtStatus;
        gvStatus.DataBind();
    }
    else
    {
        gvStatus.DataSource = null;
        gvStatus.DataBind();
    }
}
// Get DataTable Form DataBase
private DataTable GetData()
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[] { 
        new DataColumn("Sno",typeof(int)), new DataColumn("Status"), 
        new DataColumn("FromDate", typeof(DateTime)), new DataColumn("ToDate", typeof(DateTime)) });
    dt.Rows.Add(1, "Pending", "02/20/2017", "02/26/2017");
    dt.Rows.Add(2, "Hold", "01/10/2017", "01/25/2017");
    dt.Rows.Add(3, "Completed", "02/21/2017", "02/26/2017");
    dt.Rows.Add(4, "Completed", "02/22/2017", "02/28/2017");
    dt.Rows.Add(5, "Hold", "01/15/2017", "01/30/2017");
    dt.Rows.Add(6, "Pending", "01/20/2017", "02/22/2017");
    return dt;
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        gvDetails.DataSource = GetData()
        gvDetails.DataBind()
    End If
End Sub

Protected Sub Search(ByVal sender As Object, ByVal e As EventArgs)
    Dim datatime As DateTime = Convert.ToDateTime(txtSearch.Text.Trim())
    Dim dataRows As DataRow() = GetData().[Select](String.Format("#{0}# >= FromDate AND #{0}# <= ToDate", datatime))
    Dim pending As Integer = 0, hold As Integer = 0, completed As Integer = 0
    Dim dtStatus As DataTable = New DataTable()
    dtStatus.Columns.AddRange(New DataColumn() {New DataColumn("Pending"), New DataColumn("Hold"), New DataColumn("Completed")})
    For i As Integer = 0 To dataRows.Length - 1
        If dataRows(i).ItemArray(1).ToString().ToUpper() = "PENDING" Then
            pending += 1
        End If

        If dataRows(i).ItemArray(1).ToString().ToUpper() = "HOLD" Then
            hold += 1
        End If

        If dataRows(i).ItemArray(1).ToString().ToUpper() = "COMPLETED" Then
            completed += 1
        End If
    Next

    If pending > 0 OrElse hold > 0 OrElse completed > 0 Then
        dtStatus.Rows.Add(pending, hold, completed)
        gvStatus.DataSource = dtStatus
        gvStatus.DataBind()
    Else
        gvStatus.DataSource = Nothing
        gvStatus.DataBind()
    End If
End Sub
' Get DataTable Form DataBase
Private Function GetData() As DataTable
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn() {New DataColumn("Sno", GetType(Integer)), New DataColumn("Status"), New DataColumn("FromDate", GetType(DateTime)), New DataColumn("ToDate", GetType(DateTime))})
    dt.Rows.Add(1, "Pending", "02/20/2017", "02/26/2017")
    dt.Rows.Add(2, "Hold", "01/10/2017", "01/25/2017")
    dt.Rows.Add(3, "Completed", "02/21/2017", "02/26/2017")
    dt.Rows.Add(4, "Completed", "02/22/2017", "02/28/2017")
    dt.Rows.Add(5, "Hold", "01/15/2017", "01/30/2017")
    dt.Rows.Add(6, "Pending", "01/20/2017", "02/22/2017")
    Return dt
End Function

Sreenshot