How to use a DateTimePicker and format DateTime to create this SQL SELECT query for Access?

Question

When I run this code, I get table columns without data.

Date is designed as Short text in Ms Access database.

connection.Open();
OleDbCommand cmd = new OleDbCommand();
try
{
    String date = dateTimePicker1.Value.ToString();

    cmd.Connection = connection;
    String query = "SELECT * from Events where DOB ='"+date+"'";

    cmd.CommandText = query;
    Console.WriteLine("" + query);
  
    OleDbDataAdapter oa = new OleDbDataAdapter(cmd);
    DataTable dt = new DataTable();
    oa.Fill(dt);
    dataGridView1.DataSource = dt;
}

How to solve that?

Does this answer your question? [C# Data Connections Best Practice?](https://stackoverflow.com/questions/17552829/c-sharp-data-connections-best-practice) — Charlieface, Jan 24 '21 at 15:03
[What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — , Jan 24 '21 at 15:10
Can you check in your debugger what is value String date =dateTimePicker1.Value.ToString(); — Serge, Jan 24 '21 at 15:10
Does this answer your question? [Turning a SqlCommand with parameters into a DataTable](https://stackoverflow.com/questions/13870843/turning-a-sqlcommand-with-parameters-into-a-datatable) — , Jan 24 '21 at 15:12
I usually use typed DataSet, but I see the query is never executed in the provided code, so should it be? Or the Fill method do that? — , Jan 24 '21 at 15:13
The Fill method does that. If I hard code a DOB value like String query = "SELECT * from Events where DOB =' #01/24/2020"; it displays on the table but using dateTimePicker, I get an empty table — arrey tabe ebob, Jan 24 '21 at 15:25
@arreytabeebob Ok, thanks. So use [SQL parameters](https://docs.microsoft.com/dotnet/api/system.data.sqlclient.sqlparameter) as [explained](https://docs.microsoft.com/dotnet/framework/data/adonet/configuring-parameters-and-parameter-data-types) in the link above about [SQL injection](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection), with named parameters else positional parameter `(?)` if the ADO.NET provider, the ODBC driver or the DB don't support. You also need to respect DB date/time format (I don't know for Access right now) if not custom string. — , Jan 24 '21 at 15:50

score 0 · Answer 1 · answered Jan 24 '21 at 16:50

0

Force a format on the date value:

DateTime date = dateTimePicker1.Value;

String query = "SELECT * from Events where DOB = #" + date.ToString("yyyy'/'MM'/'dd") + "#";

answered Jan 24 '21 at 16:50

Gustav

53,498
7
29
55

[What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) – Jan 24 '21 at 18:07
@OlivierRogier: Nothing to "inject" here; a DatePicker can only return a date value. – Gustav Jan 24 '21 at 18:19
@.Gustav Even if you are sure that the `ToString` formatting parameter as well as the query cannot be badly written to be SQL infected, and that no other coder can modify this line of code, or that the type in the database will never be changed, this is a very bad habit of not getting into the good habit of using SQL parameters, I think, because it quickly becomes a bad habit, and it will be hard to undo that. – Jan 24 '21 at 18:41
@OlivierRogier: Of course, parameter usage is optimum, but if you can't write a correct hard-coded format string, you are facing major challenges. – Gustav Jan 24 '21 at 18:46
1

Thanks @Gustav that was very helpful. I'm able to have data on all columns. – arrey tabe ebob Jan 25 '21 at 00:18

How to use a DateTimePicker and format DateTime to create this SQL SELECT query for Access?

1 Answers1