This example shows how to filter data in GridView based from two given dates. For example you have two Calendar Control which is allows you to select the Start Date and End Dates accordingly. These selected dates will then be displayed in TextBoxes and later on you wish to Grab the data from the database based from the dates (StartDate and EndDate) that is displayed in the TextBox and bind the result to a GridView control.
Here are the code blocks below:
private string GetConnectionString(){
//call the Connection string that was set up from the web.config file
return System.Configuration.ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString;
}
private void BindGrid(string startDate, string endDate){
DateTime dStartDate;
DateTime dEndDate;
DataTable dt = new DataTable();
//Check if the string has the valid date format
if (datetime.TryParse(startDate, out dStartDate) && datetime.TryParse(endDate, out dEndDate))
{
SqlConnection connection = new SqlConnection(GetConnectionString());
try
{
connection.Open();
string sqlStatement = "SELECT <ColumnName> FROM <TableName> WHERE DateColumnName > @DateFrom AND DateColumnName < @DateTo";
SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
sqlCmd.Parameters.AddWithValue("@DateFrom", dStartDate);
sqlCmd.Parameters.AddWithValue("@DateTo", dEndDate);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlDa.Fill(dt);
if (dt.Rows.Count > 0)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
else
{
//Show no Records found
}
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Fetch Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
BindGrid(TextDateFrom.Text, TextDateTo.Text);
}
That's it! I hope someone find this post useful!