Syntax error in UPDATE statement - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Syntax error in UPDATE statement

chip050555chip050555 Posts: 32Member
I keep getting the same error message when I try to update passwords in a database. I have tried using datatables and datasets. Both are updated, but I get the Syntax error message. Anyone have any suggestions?

[code]
Option Strict On
Imports System.Data.OleDb

Public Class frmChangePassword
Inherits System.Windows.Forms.Form

Private myForm As New frmMainMenu()
Private DS As New DataSet()
'Private DT As New DataTable()
Private DbConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = \xxx-xxxDataQualitySystemESD DatabasesESD_MainTables.mdb")
Private tblData As New OleDbDataAdapter()
Private Cmd As New OleDbCommand()
Dim DbCmd As New OleDbCommandBuilder()
Private DbSQL As String


Private Sub frmChangePassword_KeyDown(ByVal sender As Object, ByVal e As KeyEventArgs)
If e.KeyCode = Keys.Return Then
SendKeys.Send("{TAB}")
End If
End Sub

Private Sub frmChangePassword_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Panel1.Enabled = True
Panel1.Visible = True
btnExit.Enabled = True
btnExit.Visible = True
btnCancel.Enabled = True
btnCancel.Visible = True
btnSubPW.Enabled = True
btnSubPW.Visible = True
txtUsername.Focus()
lblusername.Visible = True
lblusername.Enabled = True
imgusername.Visible = True
End Sub

Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
lblOldPW.Visible = False
lblNewPW.Visible = False
lblVPW.Visible = False
txtOldPW.Text = ""
txtOldPW.Visible = False
txtNewPW.Text = ""
txtNewPW.Visible = False
txtVPW.Text = ""
txtVPW.Visible = False
txtUsername.Focus()
Me.Hide()
End Sub

Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
lblOldPW.Visible = False
lblNewPW.Visible = False
lblVPW.Visible = False
txtOldPW.Text = ""
txtOldPW.Visible = False
txtNewPW.Text = ""
txtNewPW.Visible = False
txtVPW.Text = ""
txtVPW.Visible = False
imgSubPW.Visible = False
sbDate.Text = ""
txtUsername.Focus()
End Sub

Private Sub btnSubPW_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubPW.Click
Dim Results As New DialogResult()
Cmd.Connection = DbConn
DbConn.Open()

DbSQL = "SELECT * FROM tblESDUsers WHERE loginid = '" & txtUsername.Text & "'"

Dim tblData As New OleDbDataAdapter(DbSQL, DbConn)
DbCmd.DataAdapter = tblData
'Fill datatable with information
Try
tblData.Fill(DS, "users")
Catch x As Exception
MessageBox.Show(x.Message)
End Try

If DS.Tables(0).Rows.Count >= 2 Or DS.Tables(0).Rows.Count < 1 Then

MessageBox.Show("You entered an invalid login!", "LOGIN DENIED...", MessageBoxButtons.OK, MessageBoxIcon.Stop)
imgusername.Visible = True
txtUsername.Focus()
lblOldPW.Visible = False
lblNewPW.Visible = False
lblVPW.Visible = False
txtOldPW.Text = ""
txtOldPW.Visible = False
txtNewPW.Text = ""
txtNewPW.Visible = False
txtVPW.Text = ""
txtVPW.Visible = False
DbConn.Close()
Exit Sub
End If

'Search for User Name Entered
If DS.Tables(0).Rows(0)("password").Equals(Convert.ToString(txtOldPW.Text)) Then
txtprimaryid.Text = Convert.ToString(DS.Tables(0).Rows(0)("primaryid"))
txtfirstname.Text = Convert.ToString(DS.Tables(0).Rows(0)("firstname"))
txtlastname.Text = Convert.ToString(DS.Tables(0).Rows(0)("lastname"))
txtsupervisorid.Text = Convert.ToString(DS.Tables(0).Rows(0)("supervisorid"))
txtshift.Text = Convert.ToString(DS.Tables(0).Rows(0)("usershift"))
txtpasswordhint.Text = Convert.ToString(DS.Tables(0).Rows(0)("passwordhint"))
txtpasswordquestionid.Text = Convert.ToString(DS.Tables(0).Rows(0)("passwordquestionid"))
txtusertower.Text = Convert.ToString(DS.Tables(0).Rows(0)("usertower"))
Dim firstname As String = Convert.ToString(DS.Tables(0).Rows(0)("firstname"))
Dim lastname As String = Convert.ToString(DS.Tables(0).Rows(0)("lastname"))
sbDate.Text = firstname & " " & lastname & " " & GetNetRemoteTOD("\srv-mfg")

Else
MessageBox.Show("You entered an incorrect password!", "LOGIN DENIED...", MessageBoxButtons.OK, MessageBoxIcon.Stop)
txtOldPW.Text = ""
imgOldPW.Visible = False
txtOldPW.Focus()
btnSubPW.Enabled = False
imgOldPW.Visible = True
lblNewPW.Visible = False
lblVPW.Visible = False
txtNewPW.Text = ""
txtNewPW.Visible = False
txtVPW.Text = ""
txtVPW.Visible = False
DbConn.Close()
DS.Tables(0).Clear()
Exit Sub
End If

DataGrid1.DataSource = DS.Tables("users")
MessageBox.Show("Login OK")

If txtNewPW.Text = txtVPW.Text Then

DS.Tables(0).Rows(0)!password = " " & txtNewPW.Text & " "

DataGrid1.DataSource = DS.Tables("users")
MessageBox.Show("Data Set Changed")

Dim DS2 As DataSet = DS.GetChanges()

tblData.UpdateCommand = DbConn.CreateCommand
tblData.UpdateCommand.CommandText = "UPDATE tblesdusers SET password = ' " & txtNewPW.Text & " ' WHERE primaryid = ' " & txtprimaryid.Text & " '"

Try
DbCmd.DataAdapter.Update(DS2, "Users")
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

DataGrid1.DataSource = DS2.Tables("users")
MessageBox.Show("Dataset")

DS2.AcceptChanges()
Results = MessageBox.Show("Password successfully changed.", "PASSWORD CHANGED", MessageBoxButtons.OK)
If Results = DialogResult.OK Then
lblOldPW.Visible = False
lblNewPW.Visible = False
lblVPW.Visible = False
txtOldPW.Text = ""
txtOldPW.Visible = False
txtNewPW.Text = ""
txtNewPW.Visible = False
txtVPW.Text = ""
txtVPW.Visible = False
txtUsername.Text = ""
txtUsername.Focus()
DbConn.Close()
Exit Sub
End If
Else
Results = MessageBox.Show("Your new password does not match! Please re-enter your password.", "LOGIN DENIED!", MessageBoxButtons.OK)
If Results = DialogResult.OK Then
txtNewPW.Text = ""
txtVPW.Text = ""
End If
End If
DbConn.Close()
End Sub

Private Sub txtOldPW_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtOldPW.TextChanged
imgOldPW.Visible = False
lblNewPW.Visible = True
txtNewPW.Visible = True
imgNewPW.Visible = True
End Sub

Private Sub txtNewPW_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtNewPW.TextChanged
imgNewPW.Visible = False
lblVPW.Visible = True
txtVPW.Visible = True
imgVPW.Visible = True
End Sub

Private Sub txtVPW_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtVPW.TextChanged
imgVPW.Visible = False
btnSubPW.Visible = True
imgSubPW.Visible = True
End Sub

Private Sub txtUsername_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtUsername.TextChanged
lblOldPW.Enabled = True
lblOldPW.Visible = True
txtOldPW.Enabled = True
txtOldPW.Visible = True
imgOldPW.Visible = False
imgSubPW.Visible = False
End Sub
End Class
[code]

Comments

  • rlcrlc Posts: 448Member
    What error? Is 'password' a key word in the jet engine? Enclose your fields in square brackets and this might solve your issue.

    ~rlc
  • aamirwaziraamirwazir Posts: 108Member
    [code]
    "UPDATE tblesdusers SET [password] = ' " & txtNewPW.Text & " ' WHERE primaryid = " & txtprimaryid.Text
    [code]

    because may be u are using primaryid in ur database as a number not a text and password is a keyowrd


  • chip050555chip050555 Posts: 32Member
    : [code]
    : "UPDATE tblesdusers SET [password] = ' " & txtNewPW.Text & " ' WHERE primaryid = " & txtprimaryid.Text
    : [code]
    :
    : because may be u are using primaryid in ur database as a number not a text and password is a keyowrd
    :
    :
    : The brackets causes a data type mismatch in criteria expression.
  • rlcrlc Posts: 448Member
    Chip,

    I dont have any code running Jet stuff right now apparently it uses a different character to enclose DBOs. Try formating like

    .Password

    or

    !Password

    If you run the query in access, rather then through your code does it return?

    ~rlc
  • chip050555chip050555 Posts: 32Member
    : Chip,
    :
    : I dont have any code running Jet stuff right now apparently it uses a different character to enclose DBOs. Try formating like
    :
    : .Password
    :
    : or
    :
    : !Password
    :
    : If you run the query in access, rather then through your code does it return?
    :
    : ~rlc
    :

    Thanks for the help.

    If I specific the table name in the UPDATE statement I get a data type mismatch. The UPDATE statement does run in Access. If I specific the UPDATE values of password and primaryid I still get a syntax error.

    Chip
  • aamirwaziraamirwazir Posts: 108Member
    Chip,
    kindly post the Schema of ur table
    may be this can help us in solving ur problem
    i mean post the
    data types of ur Attributes of the Table in access



Sign In or Register to comment.