Sorting data within date range

Question

Hello Team,

In my window application, am using dataGridview table and dateTimepicker to sort data in date range even though it is working but if I tried sorting only 2023 year data it end up as well sorting 2022 year data.

public void SortBaptismalCandidate()
{
int i = 0;
dataGridView3.Rows.Clear();
cn.Open();
cm = new SqlCommand("Select * from tblBaptismal where SDate between'" + BaptismalDt1.Value.ToString("dd-MM-yyyy") + "'and'" + BaptismalDt2.Value.ToString("dd-MM-yyyy") + "%'", cn);
dr = cm.ExecuteReader();
while (dr.Read())
{
i++;
dataGridView3.Rows.Add(i, dr["id"].ToString(), dr["SDate"].ToString(), dr["bFName"].ToString(), dr["bLName"].ToString(), dr["bGender"].ToString(), dr["bDOB"].ToString(), dr["bPhoneNo"].ToString(), dr["bLocation"].ToString(), dr["bFatherName"].ToString(), dr["bMotherName"].ToString(), dr["bPhoneNumber"].ToString());
}
dr.Close();
cn.Close();
}

Naimish Makwana · Answer

The issue seems to be with the date format and the SQL query you’re using. In your SQL query, you’re converting the dates to the “dd-MM-yyyy” format and appending a “%” at the end. This “%” is used for pattern matching in SQL, and it might be causing unexpected results. Also, when you’re dealing with dates, it’s better to use parameterized queries to avoid issues with date formats and SQL injection attacks. Here’s how you can modify your code: public void SortBaptismalCandidate() { int i = 0; dataGridView3.Rows.Clear(); cn.Open(); cm = new SqlCommand("Select * from tblBaptismal where SDate between @StartDate and @EndDate", cn); cm.Parameters.AddWithValue("@StartDate", BaptismalDt1.Value.Date); cm.Parameters.AddWithValue("@EndDate", BaptismalDt2.Value.Date); dr = cm.ExecuteReader(); while (dr.Read()) { i++; dataGridView3.Rows.Add(i, dr["id"].ToString(), dr["SDate"].ToString(), dr["bFName"].ToString(), dr["bLName"].ToString(), dr["bGender"].ToString(), dr["bDOB"].ToString(), dr["bPhoneNo"].ToString(), dr["bLocation"].ToString(), dr["bFatherName"].ToString(), dr["bMotherName"].ToString(), dr["bPhoneNumber"].ToString()); } dr.Close(); cn.Close(); } In this code, I’ve removed the string conversion of the dates and added them as parameters to the SQL command. The Value.Date property ensures that only the date part is considered, not the time. Thanks

Jignesh Kumar · Answer

Hello, Your code should function properly, just one mistake you have made by adding a % sign in your query, just change below line cm = new SqlCommand("SELECT * FROM tblBaptismal WHERE SDate BETWEEN '" + BaptismalDt1.Value.ToString("yyyy-MM-dd") + "' AND '" + BaptismalDt2.Value.ToString("yyyy-MM-dd") + "'", cn);