error insert date mysql vb.net

Question

I have the column named breakdown date in my database. it's type is "Date" when i'm inserting into my database a breakdown i have a problem with the date column

Private Sub BT_SAVE_Click(sender As Object, e As EventArgs) Handles BT_SAVE.Click

    Dim SQLStatement As String = "INSERT INTO Pannes VALUES(03082016,'" & CB_AV.SelectedItem.ToString() & "','" & TXT_PAN.Text & "','" & TXT_DET.Text & "','" & DateTimePicker1.Value.ToString("yyyy-mm-dd") & "')"
    SavePann(SQLStatement)
End Sub

i get the error Incorrect date value: '2016-47-16' for column 'DATE' at row 1 Can anyone help?

FloatingKiwi · Accepted Answer · 2016-08-16 10:52:44Z

1

Your month is incorrect. '2016-47-16'. I suspect it's a minute value.

Use .ToString("yyyy-MM-dd") instead.

answered Aug 16, 2016 at 10:52

FloatingKiwi

4,5161 gold badge20 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

S.ElBahloul Over a year ago

Thank you mr @FloatingKiwi it works now. i have another question :when i generate a pdf containing all breakdowns the date i get is like this 12/08/2016 00:00:00 : i want to get ride of hours and minutes and seconds and show only short date here is my code for column's date ` dataRow(DGV_SIT.Columns(4).HeaderText.ToString()) = DGV_SIT.Rows(i).Cells(4).Value`

FloatingKiwi Over a year ago

I'm not familiar with PDF generation. I'd suggest posting as a new question with your code.

Alex B. · Accepted Answer · 2016-08-16 11:35:11Z

Please use SQL parameters instead of concat a big Insert string. This will avoid such errors like you describe.

Also you don´t have to convert a Date parameter into a String when MySql is translating it back to a Date value anyways. Just use the DateTimePicker.Value.

 Dim SQLStatement As String = "INSERT INTO Pannes VALUES(@p1,@p2,@p3,@p4,@p5)"

(I suggest to replace the parameter names with meaningful ones).

And in SavePann make use of Using since it automatically disposes your connection and command objects.

Using conn as New MySqlConnection
   conn.ConnectionString = "YourSqlConnectionString"
   conn.Open()
   Using cmd as New MySqlCommand 
       cmd.Connection = conn
       cmd.CommandText = SQLStatement
       cmd.Prepare()

       cmd.Parameters.AddWithValue("@p1", 03082016)
       cmd.Parameters.AddWithValue("@p2", CB_AV.SelectedItem.ToString())
       cmd.Parameters.AddWithValue("@p3", TXT_PAN.Text)
       cmd.Parameters.AddWithValue("@p4", TXT_DET.Text)
       cmd.Parameters.AddWithValue("@p5", DateTimePicker1.Value) 'No ToString needed'

       cmd.ExecuteNonQuery()
  End Using
End Using

Collectives™ on Stack Overflow

error insert date mysql vb.net

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related