Syntax error in UPDATE statement.

[Birdie010]

I'm trying to determine why I receive this error message when attempting to update the database.."Syntax error in UPDATE statement", please assist!

Thanks!

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim SQLStmt As String
        Dim UpdateOK As Integer
        If txtFirstName.Text = "" And txtLastName.Text = "" Then
            MsgBox("All Account records must have at least a last or first name.  Companies' names should be entered in the LastName field, leaving the FirstName field empty...")
            txtLastName.Focus()
            Return
        End If
        Dim UpdateCnxn As New OleDbConnection( _
            "Provider=Microsoft.ACE.OLEDB.12.0;" _
            & "Data Source=DB.mdb")
        UpdateCnxn.Open()
        'escape any quotes to avoid problems with SQL...
        txtLoginId.Text.Replace("'", "''")
        txtPassword.Text.Replace("'", "''")
        txtFirstName.Text.Replace("'", "''")
        txtLastName.Text.Replace("'", "''")
        If lblId.Text = "New" Then
            SQLStmt = String.Format("insert into ACCOUNTS (LoginId, LoginPwd, Primary Role, FirstName, LastName) values ('{0}', '{1}', '{2}', '{3}', '{4}')", _
            txtLoginId.Text, txtPassword.Text, cbMenuType.Text, txtLastName.Text, txtFirstName.Text)
        Else
            SQLStmt = String.Format("update ACCOUNTS set LoginId='{0}', LoginPwd='{1}', Primary Role='{2}', FirstName='{3}', LastName='{4}' where Id={5}", _
            txtLoginId.Text, txtPassword.Text, cbMenuType.Text, txtFirstName.Text, txtLastName.Text, lblId.Text)
        End If
        Dim UpdateCommand As New OleDbCommand(SQLStmt, UpdateCnxn)
        Debug.Write("UpdateSQLStmt=" & SQLStmt & vbCrLf)
        UpdateOK = UpdateCommand.ExecuteNonQuery()
        UpdateCnxn.Close()
        ClearInputAreas()
        txtSearch.Text = ""
        lblAdvice.Text = "Enter some characters of the last name, or company name, to search for an Account.  Or, enter the Account # if you have it.  Click the New button to create a new account record."
        txtSearch.Focus()


    End Sub

[selvaganapathy]

Hi, Check the Field Names in the data base with the name you given in the program.

if field name contains space use Square Brackets (like [Field Name])

[Birdie010]

Thanks for replying!

I changed Primary Role, to PrimaryRole, to avoid the bracket issue with the database, but I continue to receive the same error!

Any other suggestions

[Birdie010]

Correction to my last reply!!!!!

I'm now receiving this error message, please assist:

"Data type mismatch in criteria expression."

CODE

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Dim SQLStmt As String
        Dim UpdateOK As Integer
        If txtFirstName.Text = "" And txtLastName.Text = "" Then
            MsgBox("All Account records must have at least a last or first name.  Companies' names should be entered in the LastName field, leaving the FirstName field empty...")
            txtLastName.Focus()
            Return
        End If
        Dim UpdateCnxn As New OleDbConnection( _
            "Provider=Microsoft.ACE.OLEDB.12.0;" _
            & "Data Source=DB.mdb")
        UpdateCnxn.Open()
        'escape any quotes to avoid problems with SQL...
        txtLoginId.Text.Replace("'", "''")
        txtPassword.Text.Replace("'", "''")
        txtFirstName.Text.Replace("'", "''")
        txtLastName.Text.Replace("'", "''")
        If lblId.Text = "New" Then
            SQLStmt = String.Format("insert into ACCOUNTS (LoginId, LoginPwd, PrimaryRole, FirstName, LastName,) Id values '{0}', '{1}', '{2}', '{3}', '{4}'),", _
            txtLoginId.Text, txtPassword.Text, cbMenuType.Text, txtFirstName.Text, txtLastName.Text)
        Else
            SQLStmt = String.Format("update ACCOUNTS set LoginId='{0}', LoginPwd='{1}', PrimaryRole='{2}', FirstName='{3}', LastName='{4}' where Id= '{5}'", _
            txtLoginId.Text, txtPassword.Text, cbMenuType.Text, txtFirstName.Text, txtLastName.Text, lblId.Text)
        End If
        Dim UpdateCommand As New OleDbCommand(SQLStmt, UpdateCnxn)
        Debug.Write("UpdateSQLStmt=" & SQLStmt & vbCrLf)
        UpdateOK = UpdateCommand.ExecuteNonQuery()
        UpdateCnxn.Close()
        ClearInputAreas()
        txtSearch.Text = ""
        lblAdvice.Text = "Enter some characters of the last name, or company name, to search for an Account.  Or, enter the Account # if you have it.  Click the New button to create a new account record."
        txtSearch.Focus()


    End Sub

[selvaganapathy]

Hi,

I guess the error may be at line 19

SQLStmt = String.Format("insert into ACCOUNTS (LoginId, LoginPwd, PrimaryRole, FirstName, LastName,) Id values '{0}', '{1}', '{2}', '{3}', '{4}'),", _
txtLoginId.Text, txtPassword.Text, cbMenuType.Text, txtFirstName.Text, txtLastName.Text)

I think this not in the format of
INSERT INTO TABLE (Field1, Field2, ...)
VALUES (Val1, Val2, ..)

May be its your typing mistake.

Corrected may be

SQLStmt = String.Format( "insert into ACCOUNTS (LoginId, LoginPwd, PrimaryRole, FirstName, LastName)  values ('{0}', '{1}', '{2}', '{3}', '{4}')", _
txtLoginId.Text, txtPassword.Text, cbMenuType.Text, txtFirstName.Text, txtLastName.Text)

Before execute the Query check the Query SQL.

Another possible Error May be:
You are considering all the fields are String and give them for single quote. It may be differ in the original table.

For example you have given id as Text. but it may be numeric type. So Check the Type of the Field in the Access Table