Kenzakie20 Kenzakie20 - 4 months ago 41
Vb.net Question

Input the date of DateTimePicker in ms access using a query VB.NET

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error (missing operator) in query expression '7/27/2016 6:20:48 PM'.

I got this message every time I click the save button on my program.

here's the code:

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


the error was thrown to cmd.ExecuteNonQuery()

Answer

So what happens when you do this?

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