Error received saying “Conversion failed when converting date and/or time from character string.”

Question

When setting codes using the INSERT INTO sql command the following error occurs.

"Conversion failed when converting date and/or time from character string."

Values of date & time are read from a datetime picker in form.
How can I Fix this issue?

{
    String conString = @"Data Source=DESKTOP-2V4K24T\SQLEXPRESS;Initial Catalog=Student_Enrollment_System_DB;Integrated Security=True";
    string query = "INSERT INTO Students(Registration_Number,Student_Name,Date_of_Birth,Gender,Contact_Number,Course_enrolled_in) " +
        "VALUES('" + RegNo.Text + "','" + StuName.Text + "','" + this.DateOfBirth.Text + "','" + textBox1.Text + "','" + ContactNo.Text + "','" + CsesEnrolledIn.Items + "')";
    SqlConnection conn;
    conn = new SqlConnection(conString);
    SqlCommand runquery = new SqlCommand(query, conn);

    conn.Open();
    runquery.ExecuteNonQuery();
    MessageBox.Show("Student have been added!");
 }

Print your ```query``` and check what is it. https://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i — MichaelMao, Dec 26 '20 at 15:36
You will fix your problems just writing a parameterized query instead of building an sql command from pieces of constant strings and values taken from your inputs. — Steve, Dec 26 '20 at 15:38

score 1 · Answer 1 · answered Dec 29 '20 at 09:15

I have a few things to say;

It is not a good idea to write your connection string to your code. Usually it would be better to write it in your configuration file. See: Where Should I Store a database Connection String?
Looks like you try to insert string value to your datetime typed column. Don't do that. Use right data types for your data. Please read Bad Habits to Kick : Choosing the wrong data type
You are directly showing "Student have been added!" message no matter what. There is a chance that your insert command wouldn't inserted successfully. Since ExecuteNonQuery returns "the number of rows affected" of your query, it would be better to check it's result is bigger than 0 or not.
Do not create your sql command using string concatenation. This makes your code is open for SQL Injection attacks. You should always use prepared statements (aka parameterid queries). Jeff Atwood has a cool blog article called Give me parameterized SQL, or give me death. Please read it carefully. SQL Injection is a very important subject when you are working with sql queries in your code.
You open your connection but you never closed it. That's usually a bad idea. Instead of that, you can use using statement for your connection and command to automatically dispose them.

using(var conString = new SqlConnection(connectionString))
using(var query = cnn.CreateCommand())
{
     query.CommandText = @"Create your prepared statement command";
     // Add your parameters and their values with Parameters.Add method
     conString.Open();
     var affectedRows = query.ExecuteNonQuery();
     if(affectedRows > 0)
     {
        MessageBox.Show("Student have been added!");
     }
} --> Both connection and command will be disposed here

Error received saying “Conversion failed when converting date and/or time from character string.”

1 Answers1