Bèlguesmi Sémah Bèlguesmi Sémah - 3 months ago 24
MySQL Question

Insert date value into sql database in C#

I have a problem when trying to insert the value of "datetimepicker" into column of type "date"

cmd = new SqlCommand("insert into PIECE_D_IDENDITE VALUES('" + textBox4.Text + "','" + dateTimePicker2.Value + "','" + textBox7.Text + "','" + comboBox1.SelectedValue + "');insert into PERSONNE (ID_CARTE,PRENOM_PERSONNE,NOM_PERSONNE,PROFESSION_PERSONNE,TEL_PERSONNE,ADRESSE_PERSONNE,DATE_NAIS_PERSONNE)values('" + textBox4.Text + "','" + textBox1.Text + "','" + textBox2.Text + "','" + textBox8.Text + "','" + textBox10.Text + "','" + textBox9.Text + "','" + dateTimePicker1.Value + "')", cn);
cn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("success!");


So the problem is when I choose the day's number greatest than 12 (for exemple 13/10/2016) this error appear => "Conversion failed when converting date and/or time from character string."

Answer

You must always use parameterized SQL, and do no conversion to string especially for Date\DateTimes.

SqlCommand cmd = new SqlCommand(@"insert into PIECE_D_IDENDITE VALUES 
     (@v1, @v2, @v3, @v4);
insert into PERSONNE 
(ID_CARTE,PRENOM_PERSONNE,NOM_PERSONNE,
 PROFESSION_PERSONNE,TEL_PERSONNE,
 ADRESSE_PERSONNE,DATE_NAIS_PERSONNE)
values
(@ID_CARTE,@PRENOM_PERSONNE,@NOM_PERSONNE,
 @PROFESSION_PERSONNE,@TEL_PERSONNE,
 @ADRESSE_PERSONNE,@DATE_NAIS_PERSONNE);", cn);


cmd.Parameters.AddWithValue("@v1", textBox4.Text);
cmd.Parameters.AddWithValue("@v2", dateTimePicker2.Value);
cmd.Parameters.AddWithValue("@v3", textBox7.Text);
cmd.Parameters.AddWithValue("@v4", comboBox1.SelectedValue);

cmd.Parameters.AddWithValue("@ID_CARTE", textBox4.Text);
cmd.Parameters.AddWithValue("@PRENOM_PERSONNE", textBox1.Text);
cmd.Parameters.AddWithValue("@NOM_PERSONNE", textBox2.Text);
cmd.Parameters.AddWithValue("@PROFESSION_PERSONNE", textBox8.Text);
cmd.Parameters.AddWithValue("@TEL_PERSONNE", textBox10.Text);
cmd.Parameters.AddWithValue("@ADRESSE_PERSONNE", textBox9.Text);
cmd.Parameters.AddWithValue("@DATE_NAIS_PERSONNE", dateTimePicker1.Value);

cn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("success!");

In first insert you are not specifying the field names. That would work but is depends on the order of the fields and dangerous IMHO (and I assumed all the fields other than dates were of type string, if not, convert them to their corresponding types).