Sorting data within date range

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);

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