UPDATE Query not working?

[seano]

For some reason there is an error in my SQL Update statement i have done this a million times before so i dont know why it isn't working. I have a Class called clsDateManagement where all my Code to do with Database is stored. here is the code,

Change Password form:

Public Class ChangePassword
    Public DataManagement As New clsDataManagement("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=POSDatabase.accdb")
    Private Sub btnsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsave.Click
        Try
            Dim DSUser As New DataSet

            DSUser = DataManagement.SelectObjects("Employees", "Where Password= '" & txtpassword.Text & "'")

            If DSUser.Tables(0).Rows.Count = 1 Then
                If txtnewpasswordconfirm.Text = txtnewpassword.Text Then
                    'DataManagement.Update("Employees", DSUser.Tables(0).Rows(0).Item("EmployeeID"), "Password= '" & txtnewpassword.Text & "'", "EmployeeID")
                    DataManagement.Update("UPDATE Employees Set Password= '1234' Where EmployeeID = 1", 0, "", "")
                    Login.txtUsername.Select()
                    Login.txtUsername.Focus()
                    Me.Close()
                Else
                    MsgBox("New Passwords Dont Match!", MsgBoxStyle.Critical, "POS System")
                    txtnewpasswordconfirm.Clear()
                    txtnewpassword.Clear()
                    txtnewpassword.Select()
                    txtnewpassword.Focus()
                End If
            Else
            MsgBox("Login Incorrect!", MsgBoxStyle.Critical, "POS System")
                txtpassword.Clear()
                txtnewpasswordconfirm.Clear()
                txtnewpassword.Clear()
                txtpassword.Select()
                txtpassword.Focus()
            End If
        Catch ex As Exception
            MsgBox("Cant Connect to DataBase!", MsgBoxStyle.Critical, "POS System")
        End Try
    End Sub

in clsDataManagement the update function

Public Function Update(ByVal tableName As String, ByVal id As Integer, ByVal updateClause As String, ByVal IDFieldName As String) As String
        Try
            Dim myOleDbConnection As New OleDbConnection(connectionString)
            ' Get the result as a Table - one record per object
            myOleDbConnection.Open()
            Dim UpdateSQL As String = "UPDATE " & tableName & " SET " & updateClause & " WHERE (" & IDFieldName & " = " & id & ")"
            Dim myCommand As New OleDbCommand(tableName, myOleDbConnection)
            myCommand.ExecuteNonQuery()
            myOleDbConnection.Close()

        Catch ex As Exception
            Return ex.ToString
        End Try

        Return ""
    End Function

Please help, Thanks for your time.

 

[jmcilhinney]

First things first, you should be closing your connection in a Finally block, because as it is it won't get closed if an exception is thrown. Also, what's the point of your Update method returning exception information if the calling code simply ignores it?

As for the issue, I'm guessing the issue is the fact that Password is reserved word for your database. If in doubt, quote your identifiers to ensure that they don't get interpreted as reserved words. As you're using Access, you use brackets [] to wrap the identifier. Some databases use graves `` and some will use either.

 

[jmcilhinney]

By the way, it would have been nice if you'd explained that you'd changed the Update method so that you could pass in the whole SQL statement instead of letting us waste our time figuring it out for ourselves. Please provide all the relevant information in the first place in future. We're happy to volunteer our time to help but that doesn't mean we like wasting it because you neglected to mention a rather important detail.

 

[seano]

By the way, it would have been nice if you'd explained that you'd changed the Update method so that you could pass in the whole SQL statement instead of letting us waste our time figuring it out for ourselves. Please provide all the relevant information in the first place in future. We're happy to volunteer our time to help but that doesn't mean we like wasting it because you neglected to mention a rather important detail.

Sorry mate you cant really notice its commented out on here with the little ' but thanks for your time you solved my problem.

 

[jmcilhinney]

It's not this part that I'm talking about:

                    'DataManagement.Update("Employees", DSUser.Tables(0).Rows(0).Item("EmployeeID"), "Password= '" & txtnewpassword.Text & "'", "EmployeeID")
                    DataManagement.Update("UPDATE Employees Set Password= '1234' Where EmployeeID = 1", 0, "", "")

It's this part:

            Dim UpdateSQL As String = "UPDATE " & tableName & " SET " & updateClause & " WHERE (" & IDFieldName & " = " & id & ")"
            Dim myCommand As New OleDbCommand(tableName, myOleDbConnection)

I wrote up a whole post about how those arguments couldn't possibly produce a useful UPDATE statement before I actually realised that the value of 'UpdateSQL' wasn't being used. It's very important that you remember that no one here has any prior knowledge of your project and no idea what's in your head so you need to provide all the relevant info and don't just assume that we'll work it out. Even if we do, as I did, it's a waste of our time to have to work out something that you could just explain in the first place.