使用查询VB.NET以毫秒访问方式输入DateTimePicker的日期

Question

类型为"System"的未处理异常。数据OleDb。系统中出现OleDbException。Data.dll

附加信息：查询表达式"7/27/2016 6:20:48 PM"中存在语法错误（缺少运算符）。

每次单击程序上的保存按钮时，我都会收到此消息。

这是代码：

Public Sub savetoDB()
    Dim mydate As DateTime
    mydate = Me.dtpDateDel.Value
    con.Open()
    Dim sqlQry As String = "INSERT INTO [tbl_Monitoring] ([Truck Plate No], [Driver], [Helper], [Date of Delivery], [Product], [Payment], [Customer]) VALUES (@p1, @p2, @p3, " & mydate & ", @p5, @p6, @p7)"
    Using cmd As New OleDbCommand(sqlQry, con)
        cmd.Parameters.AddWithValue("@p1", cbxTruck.Text)
        cmd.Parameters.AddWithValue("@p2", cbxDriver.Text)
        cmd.Parameters.AddWithValue("@p3", cbxHelper.Text)
        cmd.Parameters.AddWithValue("@p5", cbxProduct.Text)
        cmd.Parameters.AddWithValue("@p6", txtPayment.Text)
        cmd.Parameters.AddWithValue("@p7", txtCustomer.Text)
        cmd.ExecuteNonQuery()
        con.Close()
        MsgBox("Save Successfully!")
    End Using
End Sub

错误被抛出到cmd。ExecuteNonQuery（）

Answer 1

那么，当您这样做时会发生什么呢？

Public Sub savetoDB()
Dim mydate As DateTime
    mydate = Me.dtpDateDel.Value
    con.Open()
    Dim sqlQry As String = "INSERT INTO [tbl_Monitoring] ([Truck Plate No], [Driver], [Helper], [Date of Delivery], [Product], [Payment], [Customer]) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7)"
    Using cmd As New OleDbCommand(sqlQry, con)
        cmd.Parameters.AddWithValue("@p1", cbxTruck.Text)
        cmd.Parameters.AddWithValue("@p2", cbxDriver.Text)
        cmd.Parameters.AddWithValue("@p3", cbxHelper.Text)
        cmd.Parameters.AddWithValue("@p4", mydate)
        // Alternatively you need to use something like this to format it correctly.
        //cmd.Parameters.AddWithValue("@p4", mydate.ToString("dd/mm/yyyy hh:mm"))
        cmd.Parameters.AddWithValue("@p5", cbxProduct.Text)
        cmd.Parameters.AddWithValue("@p6", txtPayment.Text)
        cmd.Parameters.AddWithValue("@p7", txtCustomer.Text)
        cmd.ExecuteNonQuery()
        con.Close()
        MsgBox("Save Successfully!")
    End Using
End Sub

Answer 2

您可以尝试以下代码：

Public Sub savetoDB()
Dim mydate As DateTime
mydate = Me.dtpDateDel.Value
con.Open()
Dim sqlQry As String = "INSERT INTO [tbl_Monitoring] ([Truck Plate No], [Driver], [Helper], [Date of Delivery], [Product], [Payment], [Customer]) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7)"
Using cmd As New OleDbCommand(sqlQry, con)
    cmd.Parameters.AddWithValue("@p1", cbxTruck.Text)
    cmd.Parameters.AddWithValue("@p2", cbxDriver.Text)
    cmd.Parameters.AddWithValue("@p3", cbxHelper.Text)
    cmd.Parameters.Add("@p4", SqlDbType.Date ,mydate) 
    cmd.Parameters.AddWithValue("@p5", cbxProduct.Text)
    cmd.Parameters.AddWithValue("@p6", txtPayment.Text)
    cmd.Parameters.AddWithValue("@p7", txtCustomer.Text)
    cmd.ExecuteNonQuery()
    con.Close()
    MsgBox("Save Successfully!")
End Using
End Sub