C# datetimepicker to date in SQL Server

Question

I get an exception whenever I try changing value of DateTimePicker in C# application if I don't change it the program runs successfully - any ideas?

Exception :

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Conversion failed when converting date and/or time from character string.

C# code :

private void button8_Click(object sender, EventArgs e)
{
    // SqlConnection cnx = new SqlConnection("Data Source=TDI11-PC\\SQLEXPRESS;Initial Catalog=mjhd;Integrated Security=True");
    String req = "insert into dommage_materiel values('" + textBox1.Text +"','" + richTextBox2.Text+ "','" + richTextBox1.Text +"','" + textBox3.Text + "','" +dateTimePicker1.Value+"','" + richTextBox3.Text + "','" + textBox2.Text + "','" + textBox4.Text.ToString()+"','" + textBox5.Text.ToString()+"')";

    SqlCommand cmd = new SqlCommand(req, cnx);

    cnx.Open();
    cmd.ExecuteNonQuery();
    cnx.Close();
}

I tried this methods nothing worked:

DateTime.Parse(dateTimePicker1.Value.ToString());

Convert.ToDateTime(dateTimePicker1.Value.ToString());

Note : SQL Server column datatype is Date.

SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
– marc_s, Commented Mar 8, 2016 at 21:37

Satwik Nadkarny · Accepted Answer · 2016-03-08 21:04:05Z

Why don't you try passing parameters? That way you can specify the parameter type as well. Just to give an example :

SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@dateParamter", SqlDbType.Date);
command.Parameters["@dateParamter"].Value = dateTimePicker1.Value;

You can read more about this here : How to: Execute a Parameterized Query

Also, dynamic SQL as above is prone to SQL injection. Parameterised SQL is at the very least a good beginning point in learning to prevent them.

For more information, you can read Jeff Atwood's article Give me parameterized SQL, or give me death

Just to borrow the bottomline from the aforementioned article :

Parameterised SQL offers the following pure performance benefits:

Fewer string concatenations

No need to worry about any kind of manual string escaping

A more generic query form is presented to db, so it's likely already hashed and stored as a pre-compiled execution plan

Smaller strings are sent across the wire

Non-parameterized SQL is the GoTo statement of database programming. Don't do it, and make sure your coworkers don't either.

Hope this helps!!!

Collectives™ on Stack Overflow

C# datetimepicker to date in SQL Server

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related