vb.net syntax error in UPDATE statement. Code attached

Question

Hi I am trying to UPDATE an access database with VB.net and am getting a Syntax Error. Everything else in my program works except this!

Error:

System.Data.OleDb.OleDbException was unhandled ErrorCode=-2147217900 HResult=-2147217900 Message=Syntax error in UPDATE statement. Source=Microsoft Office Access Database Engine StackTrace: at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at WindowsApplication3.Form2.AddAddButton_Click(Object sender, EventArgs e) in C:\Users\Andrew\Documents\Visual Studio 2013\Projects\WindowsApplication3\WindowsApplication3\Form2.vb:line 83 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData) at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context) at System.Windows.Forms.Application.Run(ApplicationContext context) at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel() at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine) at WindowsApplication3.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException:

Code:

Private Sub AddAddButton_Click(sender As Object, e As EventArgs) Handles AddAddButton.Click

    Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\*****************************;Jet OLEDB:Database Password=*********;")
    Dim insertsql As String
    Try
        insertsql = "UPDATE RepairOrders SET ROOther = @Other,  ROJobType = @Type, SET ROJobTime = @Time, SET RODelPicDate = @DelPic WHERE RONo = @JobNo"
        Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(insertsql, conn)

        cmd.Parameters.AddWithValue("@Other", AddOtherText.Text)
        cmd.Parameters.AddWithValue("@Type", AddTypeCombo.SelectedValue)
        If AddTimeCombo.Text = "AM" Then
            cmd.Parameters.AddWithValue("@Time", Convert.ToInt32("1"))
        Else
            cmd.Parameters.AddWithValue("@Time", Convert.ToInt32("2"))
        End If
        cmd.Parameters.AddWithValue("@Time", Convert.ToInt32(AddTimeCombo.SelectedValue))
        cmd.Parameters.AddWithValue("@DelPic", AddDatePick.Value.Date.ToString)
        cmd.Parameters.AddWithValue("@JobNo", Convert.ToInt32(AddJobText.Text))

        conn.Open()
        cmd.ExecuteNonQuery()
        MessageBox.Show("Booking Added!")
        conn.Close()

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

I have hidden some sensitive information in the code. Please help me identify this issue. Thankyou :)

we dont "update" questions with a totally new error and totally different error. it invalidates the answers posted. Accept the answer so this one gets closed and ask a new question. — Ňɏssa Pøngjǣrdenlarp

Szymon Szymon · Accepted Answer · 2014-07-31T23:51:14

You have multiple SET in your query. Change it to

insertsql = "UPDATE RepairOrders SET ROOther = @Other,  
ROJobType = @Type, ROJobTime = @Time, RODelPicDate = @DelPic WHERE RONo = @JobNo"

vb.net syntax error in UPDATE statement. Code attached

Error:

Code:

1 Answers