VB.NET

VB.NET RSS

Syntax error in UPDATE statement.

Please support our VB.NET advertiser: Intel Parallel Studio Home

•

Join Date: Sep 2008

Posts: 11

Reputation: Birdie010 is an unknown quantity at this point

Solved Threads: 0

Birdie010

Offline

Newbie Poster

Syntax error in UPDATE statement.

Sep 27th, 2008

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!

 Help with Code Tags 
 vbnet Syntax (Toggle Plain Text) 
    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
    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

Last edited by cscgal; Sep 27th, 2008 at 8:07 pm. Reason: Added code tags

•

Join Date: Feb 2008

Posts: 509

Reputation: selvaganapathy is an unknown quantity at this point

Solved Threads: 88

selvaganapathy selvaganapathy is offline

Offline

Posting Pro

Re: Syntax error in UPDATE statement.

Sep 27th, 2008

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])

KSG

•

Join Date: Sep 2008

Posts: 11

Reputation: Birdie010 is an unknown quantity at this point

Solved Threads: 0

Birdie010

Offline

Newbie Poster

Re: Syntax error in UPDATE statement.

Sep 27th, 2008

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

•

Join Date: Sep 2008

Posts: 11

Reputation: Birdie010 is an unknown quantity at this point

Solved Threads: 0

Birdie010

Offline

Newbie Poster

Re: Syntax error in UPDATE statement.

Sep 27th, 2008

Correction to my last reply!!!!!

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

"Data type mismatch in criteria expression."

CODE

 Help with Code Tags 
 vbnet Syntax (Toggle Plain Text) 
 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
 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

Last edited by cscgal; Sep 28th, 2008 at 1:31 am. Reason: Added code tags

•

Join Date: Feb 2008

Posts: 509

Reputation: selvaganapathy is an unknown quantity at this point

Solved Threads: 88

selvaganapathy selvaganapathy is offline

Offline

Posting Pro

Re: Syntax error in UPDATE statement.

Sep 28th, 2008

Hi,

I guess the error may be at line 19

 Help with Code Tags 
 VB Syntax (Toggle Plain Text) 
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)
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

 Help with Code Tags 
 VB Syntax (Toggle Plain Text) 
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)
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

Last edited by selvaganapathy; Sep 28th, 2008 at 11:57 am.

KSG