Display record in GridView based on DropDown, From and To Date using C# and VB.Net in ASP.Net

Hi George616,

For filling DataTable using SqlDataAdapter opening and closing connection is not required.

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

Database

CREATE TABLE Users
(
    ID INT IDENTITY PRIMARY KEY,
    Email VARCHAR(50),
    Name VARCHAR(50),
    Role CHAR(1),
    Image VARBINARY(MAX),
    CreateDate DATETIME
)
 
INSERT INTO Users VALUES ('aaa@yahoo.com','Charles','A',NULL,'7/8/2020')
INSERT INTO Users VALUES ('xxx@gmail.com','Sam','A',NULL,'7/16/2020')
INSERT INTO Users VALUES ('eee@gmail.com','Tim','U',NULL,'7/16/2020')
INSERT INTO Users VALUES ('shina@hotmail.com','shina','A',NULL,'7/31/2020')
INSERT INTO Users VALUES ('gabby@gmail.com','Gab','A',NULL,'8/1/2020')
INSERT INTO Users VALUES ('rite@yahoo.com','Rita','A',NULL,'8/4/2020')
INSERT INTO Users VALUES ('Emmy@dixon.com','Emmy','U',NULL,'8/7/2020')


SELECT * FROM Users

HTML

<asp:DropDownList runat="server" ID="ddlTables">
    <asp:ListItem Text="Select" />
    <asp:ListItem Text="Cards" Value="Cards" />
    <asp:ListItem Text="Certificates" Value="Certificates" />
</asp:DropDownList>
From :
<asp:TextBox ID="TextBox1" runat="server" />
To :
<asp:TextBox ID="Dat" runat="server" />
<asp:Button Text="Filter" runat="server" OnClick="Button2_Click" />
<hr />
<asp:GridView runat="server" ID="GridView1"></asp:GridView>

Namespaces

C#

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

VB.Net

Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Button2_Click(object sender, EventArgs e)
{
    if (ddlTables.SelectedIndex > 0)
    {
        string query = "";
        if (ddlTables.SelectedValue.ToLower() == "cards")
        {
            query = "SELECT TOP 5 email,Role,Name,image FROM Users";
            if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))
            {
                query += " WHERE CreateDate BETWEEN @From AND @To";
            }
        }

        if (ddlTables.SelectedValue.ToLower() == "certificates")
        {
            query = "SELECT TOP 5 fullname,fone,chasis,type_v, mal FROM Report";
            if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))
            {
                query += " WHERE dat BETWEEN @From AND @To";
            }
        }

        if (ddlTables.SelectedValue.ToLower() == "reciepts")
        {
            query = "";
        }
        DataTable dt = new DataTable();
        using (SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True"))
        {
            using (SqlCommand cmd = new SqlCommand(query))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))
                    {
                        cmd.Parameters.AddWithValue("@From", TextBox1.Text);
                        cmd.Parameters.AddWithValue("@To", Dat.Text);
                    }
                    sda.SelectCommand = cmd;
                    sda.Fill(dt);
                }
            }
        }
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

VB.Net

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As EventArgs)
    If ddlTables.SelectedIndex > 0 Then
        Dim query As String = ""
        If ddlTables.SelectedValue.ToLower() = "cards" Then
            query = "SELECT TOP 5 email,Role,Name,image FROM Users"
            If Not String.IsNullOrEmpty(TextBox1.Text) AndAlso Not String.IsNullOrEmpty(Dat.Text) Then
                query += " WHERE CreateDate BETWEEN @From AND @To"
            End If
        End If

        If ddlTables.SelectedValue.ToLower() = "certificates" Then
            query = "SELECT TOP 5 fullname,fone,chasis,type_v, mal FROM Report"
            If Not String.IsNullOrEmpty(TextBox1.Text) AndAlso Not String.IsNullOrEmpty(Dat.Text) Then
                query += " WHERE dat BETWEEN @From AND @To"
            End If
        End If

        If ddlTables.SelectedValue.ToLower() = "reciepts" Then
            query = ""
        End If

        Dim dt As DataTable = New DataTable()
        Using con As SqlConnection = New SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True")
            Using cmd As SqlCommand = New SqlCommand(query)
                Using sda As SqlDataAdapter = New SqlDataAdapter()
                    cmd.Connection = con
                    If Not String.IsNullOrEmpty(TextBox1.Text) AndAlso Not String.IsNullOrEmpty(Dat.Text) Then
                        cmd.Parameters.AddWithValue("@From", TextBox1.Text)
                        cmd.Parameters.AddWithValue("@To", Dat.Text)
                    End If
                    sda.SelectCommand = cmd
                    sda.Fill(dt)
                End Using
            End Using
        End Using

        GridView1.DataSource = dt
        GridView1.DataBind()
    End If
End Sub

Screenshot