mysql - 如何使用 vb.net 使用日期时间选择器在 sql 中插入日期

Question

当我启动表单时，我不断收到错误消息“无法将 mysql 日期/时间值转换为 system.datetime”

这是我的代码

Private Sub Add_Button1(sender As Object, e As EventArgs) Handles Add_Button.Click
    MySqlConn = New MySqlConnection
    MySqlConn.ConnectionString = "Server = Localhost; database = apartmentdb; user id = root; Password = "
    Dim Reader As MySqlDataReader

    Try
        MySqlConn.Open()
        Dim Query As String
        Query = "Insert into Apartmentdb.Bookings(Apartment_ID, Client_ID, Booking_start_Date, Booking_end_Date, Total_Days, Price_per_Week, Total_Price) Values ('" & TextBox5.Text & "', '" & TextBox4.Text & "', '" & DateTimePicker4.Value & "' , '" & DateTimePicker3.Value & "' ,'" & TextBox3.Text & "', '" & TextBox2.Text & "', '" & TextBox1.Text & "')"
        Command = New MySqlCommand(Query, MySqlConn)
        Reader = Command.ExecuteReader

        MessageBox.Show("Data Saved")
        MySqlConn.Close()

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        MySqlConn.Dispose()

    End Try

    Call GridView_load()

End Sub

Answer 1

不是 VB 人员，但这应该是一个开始。

Private Sub Add_Button1(sender As Object, e As EventArgs) Handles Add_Button.Click
  Using MySqlConn as New MySqlConnection("Server = Localhost; database = apartmentdb; user id = root; Password = ")
    Dim Query As String
    Query = "Insert into Apartmentdb.Bookings(Apartment_ID, Client_ID, Booking_start_Date, Booking_end_Date, Total_Days, Price_per_Week, Total_Price) 
Values (@Apartment_ID,@Client_ID,@Start_Date,@End_Date,@Total_Days,@Price_Per_week, @Total_Price)"
    MySqlConn.Open()
    Using MyCommand = New MySqlCommand(Query, MySqlConn)
      MyCommand.Parameters.AddWithValue("@ApartmentID",TextBox5.Text)
      MyCommand.Parameters.AddWithValue("@Client_ID", TextBox4.Text)
      // and so on.
      MyCommand.ExecuteNonQuery
      MessageBox.Show("Data Saved")
    End Using
  End Using
  Call GridView_load()
End Sub

注意捕获异常是没有意义的，除非这个函数根本不重要。您应该只捕获您可以处理的异常。使用后就不需要再尝试关闭东西了。范围内的所有内容并且它与连接池配合得很好。参数化查询不仅可以阻止注入(inject)攻击，而且非常适合本地化数据，例如数字和日期格式。

几乎可以肯定，当您将 Picker.Value 编码转换为字符串时，您的问题是您的 sql 服务器不知道如何处理的字符串。即使已经过去了。您可能会得到旧的 8/6/2015 是 6 月 8 日或 8 月 6 日问题。剥离时间是一个略有不同的问题，具体取决于您的数据库，但如果您想确保其为 0(午夜)，则 SomePicker.Value.Date 将执行此操作。