Solve error in update query

Question

I have code that throws an error - I need your help to solve it.

The error is

Syntax error in update statement

My code:

Try
    Dim conn As OleDbConnection = New OleDbConnection(My.Resources.ConnectionString)
    Dim cmd As OleDbCommand

    conn.Open()

    Dim Sql As String = "select * from Administretor"
    cmd = New OleDbCommand(Sql, conn)

    Dim userE, userR As String
    userE = txtOldPass.Text

    Dim reder As OleDbDataReader = cmd.ExecuteReader()

    While reder.Read()
        userR = reder.Item(0)
    End While

    If userE = userR Then
        If txtNewPass.Text = txtNewConfromPass.Text And txtNewConfromPass.Text <> "" And txtNewPass.Text <> "" Then
            Sql = "UPDATE Administretor SET PASSWORD='" & txtNewPass.Text & " where LogIn_id=" & txtOldPass.Text & ""

            Dim cmd0 As OleDbCommand = New OleDbCommand(Sql, conn)
            cmd0.ExecuteNonQuery()
        Else
            MsgBox("Make sure that you have entered new password in both text Box and they both are same...!")
        End If
    Else
        MsgBox("Enter the correct Username")
    End If

    MsgBox("Done 2")
Catch ex As OleDbException
    MsgBox(ex.Message)
End Try

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Jul 31, 2016 at 7:17

Jim Garrison · Accepted Answer · 2016-07-31 05:34:04Z

1

Two errors

"UPDATE Administretor SET PASSWORD='" & txtNewPass.Text & " where LogIn_id=" & txtOldPass.Text & ""
                                                           ^      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                                                           |                  |
                               Missing single quote here---+                  |
                                                                              |
    LogIn_Id will never equal the old password--------------------------------+

But apart from the simple syntax errors you have a huge SQL injection vulnerability from building the SQL out of pieces including user input.

answered Jul 31, 2016 at 5:34

Jim Garrison

87k20 gold badges162 silver badges197 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charles Bretana · Accepted Answer · 2016-07-31 03:15:30Z

0

In this part,
"UPDATE Administretor SET PASSWORD='" & txtNewPass.Text & " where ...

The PASSWORD will have a single quote before it, and no single quote after it.

Change it to:
"UPDATE Administretor SET PASSWORD='" & txtNewPass.Text & "' where ...
Notice the extra single quote here ----------------------------------------^

answered Jul 31, 2016 at 3:15

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

4 Comments

Domnick Toretto Over a year ago

Can u tell me how to do it with adepter

Charles Bretana Over a year ago

Just change your code, add the extra single quote where I showed you. What do you mean by adepter ?

Charles Bretana Over a year ago

Sorry, Then I can't help without clearer understanding and lots more information

Charles Bretana Over a year ago

Suggest you extract exact SQL being passed to the database server, and the error message you are getting, and post that....

Andry Wijaya · Accepted Answer · 2016-08-01 06:58:10Z

0

Add this syntax :

Sql = "UPDATE Administretor SET PASSWORD='" & txtNewPass.Text & " where LogIn_id=" & txtOldPass.Text & ""

Clipboard.SetText(Sql)

The query will be in your clipboard. Run it on SQL(whichever you are using), and see if the query runs smoothly?

Please show us what the query generation holds and what the error it produce when running directly from the SQL.

answered Aug 1, 2016 at 6:58

Andry Wijaya

32 bronze badges

Collectives™ on Stack Overflow

Solve error in update query

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related