Vinz' Blog

"Code, Beer and Music" ~ my way of being a programmer!
posts - 124, comments - 367, trackbacks - 0

My Links

News

Archives

Image Galleries

Filter GridView Data based from Date Ranges

This example show's how to filter the 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 was displayed in the TextBoxes 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!

Print | posted on Thursday, May 07, 2009 5:11 PM |

Feedback

No comments posted yet.
Post A Comment
Title:
Name:
Email:
Website:
Comment:
Verification:
 
 

Powered by: