Search Record Between Two dates in asp.net using sql server

 

Design Form Code:-

 Start Date:    <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>

End Date    <asp:TextBox ID="TextBox5" runat="server" style="height: 22px"></asp:TextBox>

<asp:Button ID="Button3" runat="server" onclick="Button3_Click" Text="Search" />

   <asp:CustomValidator ID="CustomValidator3" runat="server" 

        ControlToValidate="TextBox4" ErrorMessage="dd/MM/yyyy Format" 

        onservervalidate="CustomValidator3_ServerValidate"></asp:CustomValidator>

    <asp:CustomValidator ID="CustomValidator4" runat="server" 

        ControlToValidate="TextBox5" ErrorMessage="dd/MM/yyyy Format" 

        onservervalidate="CustomValidator4_ServerValidate"></asp:CustomValidator>

    <asp:GridView ID="GridView2" runat="server">

    </asp:GridView>

    <br />

    <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>

Code: For Search Button and Validation:-

protected void CustomValidator3_ServerValidate(object source, ServerValidateEventArgs args)

{

DateTime d;

args.IsValid = DateTime.TryParseExact(args.Value, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out d);

validdate = DateTime.TryParseExact(args.Value, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out d);

 }

 protected void CustomValidator4_ServerValidate(object source, ServerValidateEventArgs args)

        {

            DateTime d;

args.IsValid = DateTime.TryParseExact(args.Value, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out d);

validdate = DateTime.TryParseExact(args.Value, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out d);

        }

protected void Button3_Click(object sender, EventArgs e)

{

if (validdate == true)

{

// Label2.Text = "Valid Date";

string constring = ConfigurationManager.ConnectionStrings["dbcss"].ConnectionString;

                SqlConnection con = new SqlConnection(constring);

//SqlCommand cmd = new SqlCommand("select Name, Convert(varchar,DOJ,105)as DOJ from StdTbl where DOJ between Convert(date,@DOJ,103) and Convert(date,@DOJ1,103)", con);

                ////  cmd.CommandType = CommandType.StoredProcedure;

SqlCommand cmd = new SqlCommand("select Name, convert(varchar, cast(Convert(varchar(10),DOJ,101) as DateTime) ,106) as DOJ from StdTbl where DOJ between convert(date, @DOJ, 105) AND convert(date, @DOJ1, 105)",con);

                cmd.Parameters.AddWithValue("@DOJ", TextBox4.Text);

                cmd.Parameters.AddWithValue("@DOJ1", TextBox5.Text);

 

                try

                {

                    con.Open();

                    SqlDataAdapter da = new SqlDataAdapter(cmd);

                    DataTable dt = new DataTable();

                    da.Fill(dt);

                    GridView2.DataSource = dt;

                    GridView2.DataBind();

                    con.Close();

                     if (dt.Rows.Count > 0)

                    {

                        Label2.Text = "Record Search Successfully";

                     }

                    else

                    {

                        Label2.Text = "Record not Found.....";

                    } }

                catch (Exception ex)

                {

                    Label2.Text = ex.Message;

                }

Connection String:-

 <connectionStrings>

    <add name="dbcs" connectionString="Data Source=CSLODHI-PC\SQLEXPRESS;Initial Catalog=WorkingDate;Integrated Security=True" providerName="System.Data.SqlClient" />

    <add name="dbcss" connectionString="Data Source=CSLODHI-PC\SQLEXPRESS;Initial Catalog=DateOnly;Integrated Security=True" providerName="System.Data.SqlClient" />

  </connectionStrings>

Table Create: