none
Gets an unexpected "Syntax error in UPDATE statement" RRS feed

  • General discussion

  • Hi,
    I got an Access database which I am trying to update with the following code:

    Try
                If Len(txtFrontPage.Text) = 0 Then Exit Sub
                strSql = "UPDATE BookSetUp SET FtpFolderPath = ?" & _
                ", HtmlFolderPath = ?" & _
                ", UserName = ?" & _
                ", PassWord = ?" & _
                ", ReportName = ?" & _
                ", FrontPageFontName = ?" & _
                ", FrontPageFontSize = ?" & _
                ", FrontPageFontColor = ?" & _
                ", ReportBackColor = ?" & _
                ", SecondPageFontName = ?" & _
                ", SecondPageFontSize = ?" & _
                ", SecondPageFontColor = ?" & _
                ", WHERE ChildNo = ?"
    
                Dim myCommand As OleDbCommand = New OleDb.OleDbCommand(strSql)
                myCommand.Connection = ConKids
                myCommand.Parameters.AddWithValue("FtpFolderPath", OleDbType.VarChar).Value = txtFtp.Text
                myCommand.Parameters.AddWithValue("HtmlFolderPath", OleDbType.VarChar).Value = txtHttp.Text
                myCommand.Parameters.AddWithValue("UserName", OleDbType.VarChar).Value = txtUserName.Text
                myCommand.Parameters.AddWithValue("PassWord", OleDbType.VarChar).Value = txtPassword.Text
                myCommand.Parameters.AddWithValue("ReportName", OleDbType.VarChar).Value = Trim(txtFrontPage.Text)
                myCommand.Parameters.AddWithValue("FrontPageFontName", OleDbType.VarChar).Value = txtFrontTextFont.Text
                myCommand.Parameters.AddWithValue("FrontPageFontSize", OleDbType.Integer).Value = numFrontText.Value
                myCommand.Parameters.AddWithValue("FrontPageFontColor", OleDbType.VarChar).Value = cboFrontTextColor.Text
                myCommand.Parameters.AddWithValue("ReportBackColor", OleDbType.VarChar).Value = cboBackColor.Text
                myCommand.Parameters.AddWithValue("SecondPageFontName", OleDbType.VarChar).Value = txtOtherTextFont.Text
                myCommand.Parameters.AddWithValue("SecondPageFontSize", OleDbType.Integer).Value = numOtherText.Value
                myCommand.Parameters.AddWithValue("SecondPageFontColor", OleDbType.VarChar).Value = cboOtherTextColor.Text
                myCommand.Parameters.AddWithValue("ChildNo", OleDbType.LongVarChar).Value = txtChild.Text
                myCommand.ExecuteNonQuery()
    
            Catch ex As System.Exception
                Debug.Print(Err.Number & vbCrLf & ex.Message & vbCrLf & ex.StackTrace & " - frmCompleteBookSetUp: UpdateFields")
            End Try

    but here I gets ths following error:

    A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
    5
    Syntax error in UPDATE statement.
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       ved System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       ved System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       at MyChildren.frmCompleteBookSetUp.UpdateFields() i C:\VB_Net Programmer\MyChildren\MyChildren_ADO\MasterKid_Net\frmCompleteBookSetUp.vb:linje 182 - frmCompleteBookSetUp: UpdateFields

    I have now used the most of two days trying to solve this problem, but I am getting nowhere.
    Would someone out there please take a look and point out my mistakes?

    Kind regards
    Jorgen


    levesen

    • Changed type levesen Monday, February 11, 2013 7:46 PM wrong forum
    Thursday, February 7, 2013 9:42 PM

All replies

  • Jorgen,

    This is no DataSet question, you use an ExecuteNonQuery with not any DataSet and this is a DataSet forum.

    Simply use the VB forum, there probably Paul, I or somebody else can help you fine with your problem

    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral

    That is an active forum so you get quicker answers there.


    Success
    Cor

    Saturday, February 9, 2013 1:12 PM
  • Moderators,

    Can this thread be moved to Off Topic, search engines will lead to this as DataSet and can give misunderstandings for those searching for answers around that.

    Thanks,



    Cor


    Monday, February 11, 2013 7:26 PM
  • Hi Jorgen, I'm not sure if this is your problem or not, but I've always used a different syntax for the Parameters.AddWithValue. Try changing all your parms to use the following syntax:

    myCommand.Parameters.AddWithValue("FtpFolderPath", txtFtp.Text)


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Wednesday, February 13, 2013 4:02 PM
    Wednesday, February 13, 2013 4:01 PM
  • You have a comma too much (left of WHERE statement).

    Miha Markic [MVP C#] http://blog.rthand.com

    Saturday, February 16, 2013 2:03 PM