Conversion failed when converting date and/or time from character string. in c#

Question

1.00/5 (1 vote)

See more:

C#

private void btn_BooksList_Click(object sender, EventArgs e)
       {
           FRM_ReservationList frm = new FRM_ReservationList();


           con.Open();
           s = " select R.P_id, Patients.P_Name,Total_Reservation Date_Reservation   ,  R.Notes from ResrvationData R  
           s = s + " where  Date_Reservation = '" + dateTimePicker_Time.Text + "' ";
           s = s + " order by Date_Reservation desc , Total_Reservation ";
           sCommand = new SqlCommand(s, con);
           sdAdapter = new SqlDataAdapter();
           sdAdapter.SelectCommand = sCommand;
           dt = new DataTable();
           sdAdapter.Fill(dt);
           //BindingSource BSource = new BindingSource();
           //BSource.DataSource = dt;
           frm.dataGridView_ReservationList.DataSource = dt;
           con.Close();
           frm.ShowDialog();

       }

What I have tried:

How i can find solution for this problem

Posted 15-Feb-16 2:24am

Member 12244977

Add a Solution

Comments

Kornfeld Eliyahu Peter 15-Feb-16 8:44am

Bear in your mind that not every string can be converted to a date/time value (what is the date/time equivalent of your name?)...
So take the debugger, hit the line and check the value that can not be converted...

Wombaticus 15-Feb-16 8:44am

1. Validate dateTimePicker_Time.Text
2. Use a parametized query

Richard Deeming 15-Feb-16 10:27am

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-02-15T02:44:00

First off, stop doing that.
Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
In this case, that code isn't vulnerable to SQL injection, but it is likely to fail because SQL doesn't know what locale the user is in, and tries to "guess" what the actual date format is when it does the convert.
If you pass the DateTimePicker.Value property directly as a parameter, then it needs no conversion in any direction, and SQL just gets the correct value immediately.

C#

s = " SELECT R.P_id, Patients.P_Name,Total_Reservation Date_Reservation   ,  R.Notes FROM ResrvationData R ";
s = s + " WHERE Date_Reservation = @DR ";
s = s + " ORDER BY Date_Reservation desc , Total_Reservation ";
sCommand = new SqlCommand(s, con);
sCommand.Parameters.AddWithValue("@DR", dateTimePicker_Time.Value);