How solve this problem syntax error in UPDATE statement

Question

This problem at syntax error for update statement then I don't know how to solve this problem

Private Sub editStaff()

   Try
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If

        If IDTextBox.Text <> "" And FirstTextBox.Text <> "" And SecondTextBox.Text <> "" And UsernameTextBox.Text <> "" And PasswordTextBox.Text <> "" Then
            strSQL = "update Staff set First_Name = '" & FirstTextBox.Text & "', " &
                "Second_Name = '" & SecondTextBox.Text & "', " & "Username = '" & UsernameTextBox.Text & "', " &
                "Password = '" & PasswordTextBox.Text & "'" & " where ID = " & CInt(IDTextBox.Text) & ""

            Dim cmd As OleDbCommand = New OleDbCommand(strSQL, con)
            Try
                cmd.ExecuteNonQuery()
                cmd.Dispose()
                con.Close()
                MessageBox.Show("Update Successful")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End If
    Catch ex As Exception
        MessageBox.Show(ex.ToString)
    End Try
End Sub

What you need to do is use SQL parameters instead of concatenating the values into the command string. Additionally, it is possible that one of the column names is a reserved keyword in the database that you're using - to cope with that you can escape the column names, but without knowing the database I can't say what the escape character you would need is. — Andrew Morton
– Andrew Morton, Commented Oct 13, 2019 at 16:07
I posted an answer but I just noticed that you have never accepted a single answer or left a comment why the answers don't work for you. It is customary to accept an answer if it solved your problem by clicking the check mark (tick mark) to the left of the answer. You can also Upvote an answer with the up pointing triangle on top of the number to the left of the answer if it was especially helpful or informative. — Mary
– Mary, Commented Oct 13, 2019 at 22:16

Mary · Accepted Answer · 2019-10-16 01:33:40Z

For some reason your validation If did not include the ID text box. I added validation for this text box. The OrElse is a short circuit. As soon as it finds a True it stops checking the conditions and proceeds to the next line.

This code

        If con.State = ConnectionState.Closed Then
            con.Open()
        End If

is completely unnecessary if you keep your database objects local. Keeping them local allows you to ensure they are closed and disposed with Using...End Using blocks.

Don't open the connection until you need it which is directly before the .Execute... line. Use parameters to avoid Sql Injection. Also your Update statement is much easier to write without all the single quotes and double quotes and ampersands.

Caution In Access the order that the parameters appear in the Sql statement must match the order that they are added to the .Parameters collection.

Finally, you should NEVER store passwords as plain text. I will leave it to you to research salting and hashing and correct the code.

Private Sub editStaff()
    Dim i As Integer
    If Integer.TryParse(IDTextBox.Text, i) Then
        MessageBox.Show("ID text box must be a number")
        Return
    End If

    If IDTextBox.Text = "" OrElse FirstTextBox.Text = "" OrElse SecondTextBox.Text = "" OrElse UsernameTextBox.Text = "" OrElse PasswordTextBox.Text = "" Then
        MessageBox.Show("Please fill in all text boxes")
        Return
    End If
    Try
        Using con As New OleDbConnection("Your connection string")
            Dim strSQL = "Update Staff set First_Name = @FirstName, Second_Name = @SecondName, [Username] = @UserName, [Password] = @Password Where [ID] = @ID"
            Using cmd As New OleDbCommand(strSQL, con)
                With cmd.Parameters
                    .Add("@FirstName", OleDbType.VarChar).Value = FirstTextBox.Text
                    .Add("@SecondName", OleDbType.VarChar).Value = SecondTextBox.Text
                    .Add("@UserName", OleDbType.VarChar).Value = UsernameBox.Text
                    .Add("@Password", OleDbType.VarChar).Value = PasswordTextBox.Text
                    .Add("@ID", OleDbType.Integer).Value = CInt(IDTextBox.Text)
                End With
                con.Open()
                cmd.ExecuteNonQuery()
            End Using
        End Using
        MessageBox.Show("Update Successful")
    Catch ex As Exception
        MessageBox.Show(ex.ToString)
    End Try
End Sub

Collectives™ on Stack Overflow

How solve this problem syntax error in UPDATE statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related