[RESOLVED] Syntax error in UPDATE statement

[shmo888]

Hi there, having a bit of a problem with one of my SQL statements.

I'm trying to get the table to update the password field.
Basically the user logs in on one form and then the form visibility is set to false.
The variables for that form are set to Public shared, so i can use then on this form. In this case they are frmlogin.staffid and frmlogin.level

I've tried copying and pasting the SQL into access and it works fine, so not too sure whats going wrong.

The exact error i get is in the title.

The other fields in the table are not relevant (Forename and Surname) so i haven't included them in the query.

'If the new password textboxes don't contain the same password then clear them and get them to do it again.
            If txtnewpass.Text = txtnewpass1.Text Then

                Dim dbCommand As OleDbCommand = New OleDbCommand

                'create and open the connection object
                dbconnection = New OleDbConnection(strconnection)
                dbconnection.Open()

                Using dbCmd As New OleDbCommand
                    dbCommand.Connection = dbconnection

                    'Set the SQL up for updating the staff table
                    dbCommand.CommandText = "UPDATE staff SET Password = @newpassword WHERE staff_ID = @Staff_ID AND [Level] = @level  AND Password = @oldpass"

                    dbCommand.CommandType = CommandType.Text
                    'Add in the new / updated values

                    With dbCommand.Parameters

                        .AddWithValue("@newpassword", txtnewpass.Text)
                        .AddWithValue("@level", frmLogin.level)
                        .AddWithValue("@staff_ID", frmLogin.StaffID)
                        .AddWithValue("@oldpass", txtoldpass.Text)
                    End With

                    '   and finally execute the SQL to update the record in customers table
                    Try
                        dbCommand.ExecuteNonQuery()

                    Catch ex As Exception

                        MsgBox(ex.Message, MsgBoxStyle.Critical)
                    End Try

                End Using

                dbconnection.Close()
                'Give user feedback 
                lblaction.Text = "Password successfully changed"

            Else
                MessageBox.Show("Passwords do not match")
                cleartextboxes(grpchangepass)


            End If

Can anyone spot anything wrong with it?
Thanks in advance.

[si_the_geek]

The problem is that Password is a reserved word, and as such should not be used as a field/table name (it confuses the query parser within the database system).

If you change the field name (in the database and your code), it should work correctly.

For more information, see the article What names should I NOT use for tables/fields/views/stored procedures/...? from our Database Development FAQs/Tutorials (at the top of this forum)

[GaryMazzone]

Password is a reserverd word.

Place Password field name in square brackets ( [Password] ) or better yet don't use that as a fieldname maybe use Pword or Passwrd

[shmo888]

Cheers guys, i remembered to put the square brackets round 'Level' but not password.
Knew it would be something stupid like that.
Thanks again.

[shmo888]

Sorry to bother you all again, but it's not updating it into the database?
I get no error when it runs, but it doesn't actually update it.

[GaryMazzone]

Add this

Try
     Dim ResutlCount As Integer = dbCommand.ExecuteNonQuery()
     MessageBox.Show("Rows Effected: " & ResultCount.ToString()
Catch ex As Exception

     MsgBox(ex.Message, MsgBoxStyle.Critical)
End Try

What is the result in the messagebox.

[shmo888]

Add this

Try
     Dim ResutlCount As Integer = dbCommand.ExecuteNonQuery()
     MessageBox.Show("Rows Effected: " & ResultCount.ToString()
Catch ex As Exception

     MsgBox(ex.Message, MsgBoxStyle.Critical)
End Try

What is the result in the messagebox.

Rows Effected: 0

[GaryMazzone]

So that means that nothing was updated in the database.

[shmo888]

I've basically used the same code for my other tables and they seem to work. All i did was change the SQL bit and it doesn't work now?

[si_the_geek]

Nothing is being updated because nothing matches your WHERE conditions.

[GaryMazzone]

First this is access so named parameters mean nothing... Access is postional only so the order of your parameters in the SQL is

1. NewPassword
2. StaffId
3. Level
4. OldPassword

the Parameter.Add statements need to be in that same order.

[shmo888]

How come its not finding any matches?

I have run a "Select * WHERE" query using those parameters in access and it finds the match.
I have tried taking out the parameter and putting values into the code to see if it can find it that way, still not finding any matches.

The values in the variables are definitely correct as i've put a break in the code and checked it. I see no reason why this shouldn't work, and it's really annoying me now.

[GaryMazzone]

Do you understand post #11?

[shmo888]

Do you understand post #11?

Yeah, sorry i fixed that before, but still get 0 rows updated.