CODEWITHSUNDEEP
c#sql-serverado.net
Ayvin
Ayvin

Reputation: 1

C# datetimepicker to date in SQL Server

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.

Upvotes: 0

Views: 1991

Answers (1)

Satwik Nadkarny
Satwik Nadkarny

Reputation: 5135

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!!!

Upvotes: 1

Related Questions