vb.net, mysql inserting dates

Question

hi im trying to insert dates from vb.net into my sql database and the results keep showing as 00-00-00 i use a date time picker to get the date which is formated to short

Public Sub NewAppointment()

    SQLCommand.Connection = SQLconnection
    SQLCommand.CommandText = "INSERT INTO " & _
appointment(TattooID,Date,Time,Length,Deposit,Cost) VALUES"& _
 ('" & _Tattoo.ID & "','"& _
  " & AppDate.Date & "','" & _
  " & AppTime & "','" & _
  " & AppLength.ToString & "','" & _
  " & AppDespoit & "','" & AppCost & "');"


  SQLCommand.CommandType = CommandType.TableDirect

        Try
            SQLconnection.Open()
            SQLCommand.ExecuteNonQuery()
            SQLconnection.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        SQLconnection.Dispose()

End Sub

score 1 · Accepted Answer · edited May 23 '17 at 12:12

As usual these problems (and other as well) are totally avoided with a parameterized query

This is an example, you need to fix the correct datatype (MySqlDbType) for each parameter to match the datatype of your datatable fields

SQLCommand.Connection = SQLconnection
SQLCommand.CommandText = "INSERT INTO " & _
    " appointment(TattooID,Date,Time,Length,Deposit,Cost) VALUES "& _
    " (@id, @appDate, @AppTime, @AppLength,@AppDespoit,@AppCost);"
SQLCommand.Parameters.Add("@id", MySqlDbType.VarChar).Value = _Tattoo.ID
SQLCommand.Parameters.Add("@AppDate", MySqlDbType.Date).Value = AppDate.Date 
SQLCommand.Parameters.Add("@AppTime", MySqlDbType.Date).Value = AppTime
SQLCommand.Parameters.Add("@AppLength", MySqlDbType.VarChar).Value = AppLength
SQLCommand.Parameters.Add("@AppDespoit", MySqlDbType.VarChar).Value = AppDespoit 
SQLCommand.Parameters.Add("@AppCost", MySqlDbType.VarChar).Value = AppCost

Now, the job to get a correct value for your date field is passed to the database engine that receives a parameter of type Date and knows how to extract the value from the parameter and store it in the relative field.

Notice how your query is now more readable and, as an added benefit, you avoid any possible Sql Injection

Worked, thanks it also corrected all the data already in my table :) — notme61, Mar 07 '15 at 17:11

vb.net, mysql inserting dates

1 Answers1