Insert date value into sql database in C#

Question

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."

First thing to do: stop putting values into SQL like that. You're opening yourself up to SQL injection attacks. Start using parameterized SQL right now. As a pleasant side effect, that will fix the conversion problems you have, because you won't be relying on the date format on your local machine... — Jon Skeet
– Jon Skeet, Commented Aug 22, 2016 at 10:58
@JonSkeet can you give me an example of using parameterized SQl please in this case please .. I'm beginner in C# — Semah
– Semah, Commented Aug 22, 2016 at 11:09
There are tons of examples of parameterized SQL in C#. A search for "parameterized sql c# mysql" gave me over 400,000 results. Please do some research, and ask a specific question after trying it if you really have to. — Jon Skeet
– Jon Skeet, Commented Aug 22, 2016 at 11:10

Cetin Basoz · Accepted Answer · 2016-08-22 11:20:11Z

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).

amalbala · Accepted Answer · 2016-08-22 11:01:05Z

-1

This problem is related to the local date configuration of your forms/server and the value returned.

If the local date format is MM/DD/YYY and you do not transform before call the procedure, the Day 13 will be stored into the MM format of the date into the database.

I have no clue about your MySQL Configuration or Winform configuration, but I am pretty sure that this problems is a different date format on client/server.

answered Aug 22, 2016 at 11:01

amalbala

1181 silver badge12 bronze badges

Collectives™ on Stack Overflow

Insert date value into sql database in C#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related