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

VAer 761 Reputation points
2021-01-18T01:36:25.693+00:00

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.

57300-date-format.jpg

string sqlDelete = "DELETE FROM TableAppointment WHERE Username = '" + Environment.UserName + "' AND AppoitmentDate BETWEEN '" + dateTimePickerBegin.CustomFormat("yyyy-MM-dd") + "' AND '" + dateTimePickerEnd.CustomFormat("yyyy-MM-dd") + "'";  
Windows Forms
Windows Forms
A set of .NET Framework managed libraries for developing graphical user interfaces.
1,858 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,415 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Daniel Zhang-MSFT 9,621 Reputation points
    2021-01-18T06:57:12.537+00:00

    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.

    0 comments No comments