none
When i run the application it shows "Update syntax error" RRS feed

  • Question

  • when i click on button it shows me error "update syntax error"

    Option Explicit On Option Strict On Imports System.Data Imports System.Data.OleDb Public Class frmChangePassword Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Admin\documents\visual studio 2010\Projects\frmUsertype\frmUsertype\usertype.accdb" Dim con As OleDbConnection Dim cmd As OleDbCommand Dim query As String Dim rdr As OleDbDataReader Private Sub reset() cmbUsertype.SelectedIndex = -1 txtUsername.Text = "" txtOld_Pass.Text = "" txtNew_Pass.Text = "" txtConfirm_Pass.Text = "" cmbUsertype.Focus() End Sub Private Sub frmChangePassword_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load reset() End Sub Private Sub btnChange_Pass_Click(sender As System.Object, e As System.EventArgs) Handles btnChange_Pass.Click Try Dim RowsAffected As Integer = 0 If Len(Trim(cmbUsertype.Text)) = 0 Then MessageBox.Show("Please select user type", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) cmbUsertype.Focus() Exit Sub End If If Len(Trim(txtUsername.Text)) = 0 Then MessageBox.Show("Please enter user name", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtUsername.Focus() Exit Sub End If If Len(Trim(txtOld_Pass.Text)) = 0 Then MessageBox.Show("Please enter old password", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtOld_Pass.Focus() Exit Sub End If If Len(Trim(txtNew_Pass.Text)) = 0 Then MessageBox.Show("Please enter new password", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtNew_Pass.Focus() Exit Sub End If If Len(Trim(txtConfirm_Pass.Text)) = 0 Then MessageBox.Show("Please confirm new password", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtConfirm_Pass.Focus() Exit Sub End If If txtNew_Pass.TextLength < 5 Then MessageBox.Show("The New Password Should be of Atleast 5 Characters", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtNew_Pass.Text = "" txtConfirm_Pass.Text = "" txtNew_Pass.Focus() Exit Sub ElseIf txtNew_Pass.Text <> txtConfirm_Pass.Text Then MessageBox.Show("Password do not match", "Input error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtNew_Pass.Text = "" txtOld_Pass.Text = "" txtConfirm_Pass.Text = "" txtOld_Pass.Focus() Exit Sub ElseIf txtOld_Pass.Text = txtNew_Pass.Text Then MessageBox.Show("Password is same..Re-enter new password", "Input error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtNew_Pass.Text = "" txtConfirm_Pass.Text = "" txtNew_Pass.Focus() Exit Sub End If con = New OleDbConnection(connString) con.Open() query = "UPDATE UserType SET Password = @newpass WHERE Username = @uname, [Password] = @oldpass, [Usertype]=@cmbusertype" 'query = "update UserType set Password = '" & txtNew_Pass.Text & "' where [Username]='" & txtUsername.Text & "' [Password] = '" & txtOld_Pass.Text & "' and [Usertype]='" & cmbUsertype.Text & "'" cmd = New OleDbCommand(query, con) cmd.Connection = con cmd.Parameters.AddWithValue("@newpass", txtNew_Pass.Text) cmd.Parameters.AddWithValue("@uname", txtUsername.Text) cmd.Parameters.AddWithValue("@oldpass", txtOld_Pass.Text) cmd.Parameters.AddWithValue("@cmbusertype", cmbUsertype.Text) RowsAffected = cmd.ExecuteNonQuery() If RowsAffected > 0 Then MessageBox.Show("Successfully changed", "Password", MessageBoxButtons.OK, MessageBoxIcon.Information) Me.Hide() frmLogin.Show() Else MessageBox.Show("Invalid user name or password", "input error", MessageBoxButtons.OK, MessageBoxIcon.Error) txtUsername.Text = "" txtNew_Pass.Text = "" txtOld_Pass.Text = "" txtConfirm_Pass.Text = "" txtUsername.Focus() End If con.Close() Catch ex As Exception MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub End Class


    Thursday, February 1, 2018 1:59 PM

Answers

  • The where condition looks incorrect, for each condition use AND e.g.

    query = "UPDATE UserType SET [Password] = @newpass WHERE Username = @uname AND [Password] = @oldpass AND [Usertype]=@cmbusertype"


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, February 1, 2018 2:04 PM
    Moderator

All replies

  • The where condition looks incorrect, for each condition use AND e.g.

    query = "UPDATE UserType SET [Password] = @newpass WHERE Username = @uname AND [Password] = @oldpass AND [Usertype]=@cmbusertype"


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, February 1, 2018 2:04 PM
    Moderator
  • Do not use the keyword Password as a column name as it is a SQL reserved word in Jet/ACE OLEDB:

    http://allenbrowne.com/AppIssueBadWord.html#P


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Thursday, February 1, 2018 3:27 PM
  • It is not causing this error but probably the next one. 

    Change this one too

      cmd.Parameters.AddWithValue("@cmbusertype", cmbUsertype.SelectedItem)


    Success Cor

    Thursday, February 1, 2018 3:45 PM
  • Hi Deeps_Rockstar,

    I agree with Karen"s post, in addition to using AND for each condition, you can also use OR.

    Update Table set Column1=@Column1 WHERE condition1 AND condition2 ... OR condition_n

    Here is the detailed info about update statement, please refer to:

    https://docs.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 2, 2018 3:54 AM
    Moderator