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

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.