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? — Charlieface
– Charlieface, Commented Jan 24, 2021 at 15:03
Can you check in your debugger what is value String date =dateTimePicker1.Value.ToString(); — Serge
– Serge, Commented Jan 24, 2021 at 15:10
Does this answer your question? Turning a SqlCommand with parameters into a DataTable — user12031933
– user12031933, Commented Jan 24, 2021 at 15:12

Gustav · Accepted Answer · 2021-01-24 16:50:41Z

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, 2021 at 16:50

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user12031933 Over a year ago

What are good ways to prevent SQL injection?

Gustav Over a year ago

@OlivierRogier: Nothing to "inject" here; a DatePicker can only return a date value.

user12031933 Over a year ago

@.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.

Gustav Over a year ago

@OlivierRogier: Of course, parameter usage is optimum, but if you can't write a correct hard-coded format string, you are facing major challenges.

arrey tabe ebob Over a year ago

Thanks @Gustav that was very helpful. I'm able to have data on all columns.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related