C#: how to write date(dateTimePicker) as criteria?

Question

Backend database: Access
Access date format: yyyy-MM-dd

I am trying to match dateTimePicker format same as Access date format, but it does not recognize the way I wrote.

Thanks.

string sqlDelete = "DELETE FROM TableAppointment WHERE Username = '" + Environment.UserName + "' AND AppoitmentDate BETWEEN '" + dateTimePickerBegin.CustomFormat("yyyy-MM-dd") + "' AND '" + dateTimePickerEnd.CustomFormat("yyyy-MM-dd") + "'";  

Answer 1

Hi VAer-4038,
The DateTimePicker.CustomFormat is a property which is used to set the custom date/time format string.
You can use it as below:

 // Set the Format type and the CustomFormat string.  
 dateTimePicker1.Format = DateTimePickerFormat.Custom;  
 dateTimePicker1.CustomFormat = "yyyy-MM-dd";  

More details you can refe to this document.
For question about access date format:
First, you can force a format on your date value to create a string expression for the date.

DateTime dt = dateTimePicker1.Value;  
 string sqlDelete = "DELETE FROM Table WHERE [Date] = #" + dt.ToString("yyyy-MM-dd") + "#";  

To avoid SQL injection attacks, I suggest you use parameterized queries.
You can refer to the following code

using (OdbcConnection Cn = new OdbcConnection(connectionString)) //Access database  
{  
    Cn.Open();  
    OdbcCommand cmd = Cn.CreateCommand();  
    cmd.CommandText = "SELECT * FROM Table WHERE Id = ?";  
    cmd.Parameters.AddWithValue("@Id", dateTimePicker1.Value.Date);  
    cmd.ExecuteNonQuery();  
}  

Best Regards,
Daniel Zhang

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.