none
Syntax error in update statement RRS feed

  • Question

  • friends

    whenever click on update button vb.net shows me the message -"Syntax error in update statement" , i'm using ms access as database, i tried a lot but can't getting whats wrong with the statement

    somebody please help me this is my query-

    Try

                openconn()


                s = "update generaldetails set registration_date='" & Format(dtprdate.Value) & "', firts_name='" & Trim(txtfname.Text) & "',middle_name='" & Trim(txtmname.Text) & "',last_name='" & Trim(txtlname.Text) & "',gender='" & gender & "',date_of_birth='" & Format(dtpdob.Value) & "',religion='" & Trim(cmbreligion.Text) & "',cast='" & Trim(txtcast.Text) & "',marital_status='" & Trim(cmbmaritalstatus.Text) & "',age=" & Integer.Parse(txtage.Text) & "  where profile_id=" & Integer.Parse(txtprofileid.Text) & ""
                MessageBox.Show(s)
                pcmd = New OleDbCommand(s, cn)

                If pcmd.ExecuteNonQuery() = 0 Then
                    MsgBox("Record not found")
                Else
                    MsgBox("Record Updated Successfully!!!")

                End If
            Catch ex As Exception
                MessageBox.Show(ex.Message)

            End Try


    • Edited by Abhijeet01 Monday, November 28, 2011 3:03 PM
    Monday, November 28, 2011 2:58 PM

Answers

  • As Dan mentioned you should be using Command Parameters. In addition, the keyword "cast" is reserved in Jet SQL. Sometimes enclosing it within brackets will resolve the issue, but it's generally a good idea to choose another name for the column.

    List of Microsoft Jet 4.0 reserved words

     


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, November 28, 2011 4:11 PM

All replies

  • It might help if you included the actual query (as displayed by your MsgBox).  Rather than build the query string with literals, consider using a parameterized query passing values in native forrmat like the untested example below.  This practice provides better security, performance and cleaner code.  Change the parameter data types and maximum length as needed.

     

            Dim s As String = "UPDATE generaldetails" _
                & " SET registration_date = ?" _
                & " ,firts_name = ?" _
                & " ,middle_name = ?" _
                & " ,last_name = ?" _
                & " ,gender = ?" _
                & " ,date_of_birth = ?" _
                & " ,religion = ?" _
                & " ,cast = ?" _
                & " ,marital_status = ?" _
                & " ,age = ?" _
                & " WHERE profile_id = ?"
    
            Dim command As New OleDbCommand(s, connection)
            command.Parameters.Add("@registration_date", OleDbType.DBTimeStamp).Value = dtprdate.Value
            command.Parameters.Add("@firts_name", OleDbType.VarChar, 30).Value = Trim(txtfname.Text)
            command.Parameters.Add("@middle_name", OleDbType.VarChar, 30).Value = Trim(txtmname.Text)
            command.Parameters.Add("@last_name", OleDbType.VarChar, 30).Value = Trim(txtlname.Text)
            command.Parameters.Add("@gender", OleDbType.Char, 1).Value = gender
            command.Parameters.Add("@date_of_birth", OleDbType.Date).Value = dtpdob.Value
            command.Parameters.Add("@religion", OleDbType.VarChar, 30).Value = Trim(cmbreligion.Text)
            command.Parameters.Add("@cast", OleDbType.VarChar, 30).Value = Trim(txtcast.Text)
            command.Parameters.Add("@marital_status", OleDbType.VarChar, 10).Value = Trim(cmbmaritalstatus.Text)
            command.Parameters.Add("@age", OleDbType.Integer).Value = Integer.Parse(txtage.Text)
            command.Parameters.Add("@profile_id", OleDbType.Integer).Value = Integer.Parse(txtprofileid.Text)
            command.ExecuteNonQuery()
            connection.Close()
    

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Janos BerkeMVP Monday, November 28, 2011 4:02 PM
    Monday, November 28, 2011 3:53 PM
  • As Dan mentioned you should be using Command Parameters. In addition, the keyword "cast" is reserved in Jet SQL. Sometimes enclosing it within brackets will resolve the issue, but it's generally a good idea to choose another name for the column.

    List of Microsoft Jet 4.0 reserved words

     


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, November 28, 2011 4:11 PM
  • Thanx a lot for helping me Dan Sir... it will be very helpful to write parameterized query, i'll try it ...but as Sir Paul P Clement adviced i made changes and i have solved the error...the word  'cast' is reserved keyword...



    • Edited by Abhijeet01 Tuesday, November 29, 2011 10:09 AM
    Tuesday, November 29, 2011 9:33 AM
  • thanks a lot Paul sir i got the solution ...the word 'cast' was the problem...and by your advice i have solved it...thnx again sir...
    • Edited by Abhijeet01 Tuesday, November 29, 2011 10:07 AM
    Tuesday, November 29, 2011 9:38 AM
  • Hi Abhijeet01,

    I’m glad to hear that you have solved your issue, if you have any problems, please feel free to post in the forum.

    Best Regards,


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, November 30, 2011 2:17 AM
    Moderator