3

Note: Don't care with Connection becuse the connection work. Field in database is DateTime

DateTime dtc = Convert.ToDateTime(dateTimePicker1.Value.Date);

cmd = new SqlCommand("UPDATE LAB_TESTING set Lab_Nam='" + lab_id + "',Rslt_lb='" + 
                     textBox1.Text + "',Tst_Dat='" + dtc + "' Where Lab_ID='" + 
                     bindex + "'", con);
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("You Update recored successfully", "delete", MessageBoxButtons.OK, MessageBoxIcon.Information);
dataGridView1.DataSource = null;
con.Close();
readdatagrid1();

after Run program , I got Conversion failed when converting date and/or time from character string.

NASSER
  • 5,900
  • 7
  • 38
  • 57
wittyse
  • 29
  • 4

3 Answers3

1

Don't use string concatenation to build your queries (SQL injection alert, also read this!). Use parameterized queries. As for your problem: format the date as yyyy-MM-dd (ISO 8601) and it'll work.

If you'd have used parameterized queries correctly you could've just passed in the DateTime as-is and the driver would've ensured the value would get passed correctly to SQL regardless the "format" / locale setting / whatever since the value would be passed as a DateTime instead of a string. Something like this:

cmd = new SqlCommand(@"UPDATE LAB_TESTING set Lab_Nam = @labnam,
                            Rslt_lb = @result, Tst_Dat = @tstdat
                            Where Lab_ID = @id", con);

cmd.Parameters.AddWithValue("@labnam", lab_id );
cmd.Parameters.AddWithValue("@result", textBox1.Text);
cmd.Parameters.AddWithValue("@tstdat", dateTimePicker1.Value.Date);
cmd.Parameters.AddWithValue("@id", bindex);

con.Open();
cmd.ExecuteNonQuery();

Other than that I also recommend to wrap the con and cmd in a using() { ... } statement so that you can be sure this stuff gets disposed properly and that you give your variables and fieldnames decent names and use a consistent naming scheme.

Now repeat after me:

  • I will never, ever, run queries again that have been string-concatenated together anymore!

  • From now on I will use parameterized queries

  • If I need to run string-concatenated queries ever again I will make sure all values are escaped properly

Repeat the above aloud, at least 50 times.

RobIII
  • 8,488
  • 2
  • 43
  • 93
  • reject the "AddWithValue" as it got red under line. – wittyse Sep 04 '15 at 07:39
  • `got red under line`... again: tell us the exact error. "Red line" could be anything. Your errors window (or hovering your mouse over the underlined code) will show the actual error. Also please note that I wrote the above code as an **example**. You may have to change it to your needs or... I dunno... maybe refer to [the documentation](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue(v=vs.110).aspx) for a change? – RobIII Sep 04 '15 at 07:40
  • the error is "doesn't definition for AddWithValue " – wittyse Sep 04 '15 at 08:05
  • might I didn't explain good ,because i am just start with C# and I am facing the datetime problem. – wittyse Sep 04 '15 at 08:35
0

Try this 

DateTime dtc = Convert.ToDateTime(dateTimePicker1.Value.ToString("dd/MM/yyyy"));

or else

you can also do this

DateTime dtc = Convert.ToDateTime(dateTimePicker1.Text)

If you are getting this error with your SQL code than have a look here

Community
  • 1
  • 1
Mohit S
  • 13,723
  • 6
  • 34
  • 69
  • I used both codes you proposed above but didn't work, also got this message "Conversion failed when converting date and/or time from character string." – wittyse Sep 04 '15 at 06:47
  • Letz try this `DateTime.ParseExact(dateTimePicker1.Text)` – Mohit S Sep 04 '15 at 06:51
  • BTW you are getting this error on `DateTime dtc = Convert.ToDateTime(...)` line or somewhere else – Mohit S Sep 04 '15 at 06:54
  • Don't use a locale specific format; most of the world uses dd-MM-yyyy. [ISO 8601](http://stackoverflow.com/a/32391926/215042) will always work. – RobIII Sep 04 '15 at 07:01
  • also doesnt work this DateTime.ParseExact(dateTimePicker1.Text) also i have this code DateTime dtc = Convert.ToDateTime(...) orginally in my code and doesnt work. – wittyse Sep 04 '15 at 07:02
  • i have changed format of datetimepicker in custom format dd-MM-yyyy before posted my problem here. – wittyse Sep 04 '15 at 07:04
  • Read the link. Don't use dd-MM-yyyy or mm/dd/yyyy... Use `yyyy-mm-dd` (e.g. ISO 8601) if you REALLY have to. But better use [parameterized query](http://stackoverflow.com/a/32391926/215042). – RobIII Sep 04 '15 at 07:08
  • `also dosnt work` ... well that clarifies a lot. At least give us the exact error. I think you need to write `MM` (months) instead of `mm` (minutes); I wrote that wrong. Also: changing the format of your datetime picker doesn't change a thing if you're trying to write it's `value` to your database. The format is for *display* purposed only; the `value` is still a DateTime value. You need to learn the difference between representation and actual value. Again: refer to [this answer](http://stackoverflow.com/a/32391926/215042) to stay away from this hell alltogether... – RobIII Sep 04 '15 at 07:39
  • Why convert a date to a string and then back to a date? That makes no sense. Just pass `dateTimePicker1.Value.Date` to the query using a parameter. Don't convert to string _at all_! – Chris Dunaway Sep 04 '15 at 16:21
0

This will work for you. 

    var date = (DateTime)dateTimePicker1.SelectedDate.Value;
Hedego
  • 276
  • 2
  • 12