Member Avatar for monching

Hello guys,

I have my problem in saving date to mysql.

Dim conn As MySqlConnection
Dim myCommand As New MySqlCommand
Dim commandB As MySqlCommandBuilder
Dim data As MySqlDataAdapter
Dim query As String = "SELECT * FROM events e;"
Dim myconnstring As String
Dim comm As String
Dim vers As String = TBox.Text & " " & "VS" & " " & tBox2.Text

Dim eventdate As String = Format(DateTimePicker1.Value, "yyyy-dd-MM HH:mm:ss")

myconnstring = "datasource=localhost;username=root;password=*******;database=*******;"

comm = "INSERT INTO `newscoring`.`events` (`gametype`, `date`, `vs`, `aa`, `aaa`, `aaaaa`) VALUES ('sample', '" & eventdate & "', '" & vers & "', '" & RBox.Text & "', '" & UBox.Text & "', '" & pbox.Text & "');"

            conn = New MySqlConnection(myconnstring)
            Try
                conn.Open()
                Try
                    data = New MySqlDataAdapter(query, conn)
                    commandB = New MySqlCommandBuilder(data)
                    Dim res As New MySqlParameter
                    res.Value = commandB
                    myCommand.CommandText = comm
                    myCommand.Connection = conn
                    myCommand.ExecuteNonQuery()
                Catch myerror As MySqlException
                    TTimer.Stop() : Sclock.Stop()
                    MessageBox.Show("Saving event error: " & myerror.Message)
                    conn.Dispose()

                End Try
            Catch myerror As MySqlException
                TTimer.Stop() : Sclock.Stop()
                MessageBox.Show("Saving event error: " & myerror.Message)
                conn.Dispose()
            End Try
            conn.Dispose()

Error message:
Error 1292: Incorrect datetime value: '1-1-2013' for column 'date' at row 1

In this line the value were get.
Dim eventdate As String = Format(DateTimePicker1.Value, "yyyy-dd-MM HH:mm:ss")

Edited by monching
  • 5 Contributors
  • 6 Replies
  • 3K Views
  • 6 Months Discussion Span
  • Latest Post Latest Post by shashikumar s g
Member Avatar for tinstaafl

Try using "YYYY-MM-DD" instead

Member Avatar for monching

That format is error also, infact it could not read the YYYY-DD value only the MM value.
Error: Incorrect datetime value: 'YYYY-DD-01' for column 'date' at row 1

I followed the default format of MySQL which is yyyy-dd-MM and this can get the value but error in saving to database
Error: Incorrect datetime value: '2013-01-01' for column 'date' at row 1

Edited by monching because: lacking
Member Avatar for G_Waddell

Hi
Are you sure the default format is yyyy-dd-MM HH:mm:ss? Try formating it as yyyy-MM-dd HH:mm:ss (Serial date)

Dim eventdate As String = Format(DateTimePicker1.Value, "yyyy-MM-dd HH:mm:ss")

Also you may want to double check that syntax, - I'm always mixing my months and minutes up hence I usually do this:

Dim eventdate As String 

eventdate = Year(DateTimePicker1.Value) & "-" & Format(Month(DateTimePicker1.Value),"0#") &"-" & Format(DatePart(DateInterval.Day, DateTimePicker1.Value ),"0#") & _
            " " &TimeValue(DateTimePicker1.Value)

I know it should be the same thing but just in case....

Edited by G_Waddell
Member Avatar for monching

@ G Waddell
You're right, it's my mistake. Because, i am basing to this output 2013-01-01.
Thanks!

Member Avatar for shashikumar s g

wht u mean by basing to this output 2013-01-01

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.