Search code examples
sqldatabasevb.netoledb

Getting error on inserting date into sql database?

I'm inserting record into database but is getting error on the paremeter "Date". Any thoughts would be good.

myConnection.Open()
Dim sqlQry As String = "INSERT INTO MasterLabNumber (LabNumber, Location, Date)" + "VALUES(@LabNumber, @Location, @Date)"
Dim str As String
str = "SELECT * FROM MasterLabNumber WHERE (LabNumber = " & TextBox1.Text & ")"
Dim d As System.DateTime = DateTime.Now.ToString("yyyy/MM/dd")
Dim cmd As OleDbCommand = New OleDbCommand(sqlQry, myConnection)
cmd.Parameters.AddWithValue("@LabNumber", TextBox1.Text)
cmd.Parameters.AddWithValue("@Location", ComboBox1.Text)
cmd.Parameters.AddWithValue("@Date", d)

cmd.ExecuteNonQuery() <-- gets error here relating to adding the parameter date.
Solution

  • Yeah, that's cause Date is a reserve word which you are using in your insert statement. You need to escape it like below using []

    INSERT INTO MasterLabNumber (LabNumber, Location, [Date])

    Your insert statement should become

    Dim sqlQry As String = "INSERT INTO MasterLabNumber (LabNumber, Location, [Date]) VALUES(@LabNumber, @Location, @Date)"