Syntax error in update statement

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

Answer 1

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/

Answer 2

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)

Answer 3

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...

Answer 4

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...

Answer 5

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