Hi loghbil2009,
I have created an example. Refer below sample and modify as per your database.
Database
For this example I have used of Northwind database that you can download using the link given below.
Download Northwind Database
HTML
Date Of Birth:
<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
<asp:Button Text="Search" runat="server" OnClick="OnSearch" />
<hr />
<asp:GridView ID="gvEmployees" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="EmployeeID" HeaderText="Employee Id" />
<asp:BoundField DataField="FirstName" HeaderText="Name" />
<asp:BoundField DataField="BirthDate" HeaderText="DOB" DataFormatString="{0:dd/MM/yyyy}" />
</Columns>
</asp:GridView>
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.SearchEmployees();
}
}
protected void OnSearch(object sender, EventArgs e)
{
this.SearchEmployees();
}
private void SearchEmployees()
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string sql = "SELECT EmployeeID, FirstName, BirthDate FROM Employees WHERE CAST(BirthDate AS DATE) = @DOB OR @DOB IS NULL";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (!string.IsNullOrEmpty(txtSearch.Text.Trim()))
{
cmd.Parameters.AddWithValue("@DOB", txtSearch.Text.Trim());
}
else
{
cmd.Parameters.AddWithValue("@DOB", DBNull.Value);
}
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
gvEmployees.DataSource = dt;
gvEmployees.DataBind();
}
}
}
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Me.SearchEmployees()
End If
End Sub
Protected Sub OnSearch(sender As Object, e As EventArgs)
Me.SearchEmployees()
End Sub
Private Sub SearchEmployees()
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim sql As String = "SELECT EmployeeID, FirstName, BirthDate FROM Employees WHERE CAST(BirthDate AS DATE) = @DOB OR @DOB IS NULL"
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(sql, con)
If Not String.IsNullOrEmpty(txtSearch.Text.Trim()) Then
cmd.Parameters.AddWithValue("@DOB", txtSearch.Text.Trim())
Else
cmd.Parameters.AddWithValue("@DOB", DBNull.Value)
End If
Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
Using dt As DataTable = New DataTable()
sda.Fill(dt)
gvEmployees.DataSource = dt
gvEmployees.DataBind()
End Using
End Using
End Using
End Using
End Sub
Screenshot
