CODEWITHSUNDEEP
.netvb.net
Anthony
Anthony

Reputation: 37

Check if MySQL UPDATE query was successful or not

I have a login system, that will have an option to change a user password if forgotten once they answer their security question correctly.

Everything is working fine, except I'm unsure how to determine if it was successful or not.

If TextBox1.Text = TextBox2.Text Then
            Dim conn As MySqlConnection
            conn = New MySqlConnection("server=localhost; user id=root; password=; database=testing")
            Dim username As Boolean = True
            conn.Open()
            Dim sqlquery As String = "UPDATE user SET password='" & TextBox1.Text & "' WHERE id='" & frmLogin.CurrentUserID & "';"
            Dim data As MySqlDataReader
            Dim adapter As New MySqlDataAdapter
            Dim command As New MySqlCommand
            command.CommandText = sqlquery
            command.Connection = conn
            adapter.SelectCommand = command
            data = command.ExecuteReader
            Dim i As Integer = command.ExecuteNonQuery()
            If (i > 0) Then
                MessageBox.Show("Success!")
            Else
                MessageBox.Show("Failed!")
            End If
            data.Close()
            conn.Close()


        Else
            MsgBox("Passwords must match!")
        End If

It should show a message box saying "Success!" if it worked and "Failed!" if not. I'm getting an error --> 'There is already an open DataReader associated with this Connection which must be closed first.'

Upvotes: 1

Views: 1374

Answers (1)

Steve
Steve

Reputation: 216243

That's not the correct way to execute an UPDATE query.
You don't need to call any ExecuteReader and you don't need any MySqlDataAdapter. Of course leaving the reader open triggers your actual error because the connection cannot execute any other command until it is freed from serving the reader. But you have also three big problems in your code not immediately evident.

You should always execute parameterized queries and never create sql commands concatenating strings from the user input. (Search about Sql Injection).

You should always enclose your MySqlConnection in a using statement block to correctly dispose it.

Using conn as MySqlConnection = New MySqlConnection("server=localhost; user id=root; password=; database=testing")
    Dim username As Boolean = True
    conn.Open()
    Dim sqlquery As String = "UPDATE user SET password=@pass WHERE id=@id"
    Dim command As New MySqlCommand
    command.CommandText = sqlquery
    command.Connection = conn
    command.Parameters.Add("@pass", MySqlDbType.VarChar).Value = TextBox1.Text
    command.Parameters.Add("@id", MySqlDbType.Int32).Value = frmLogin.CurrentUserID
    Dim i As Integer = command.ExecuteNonQuery()
    If (i > 0) Then
        MessageBox.Show("Success!")
    Else
        MessageBox.Show("Failed!")
    End If
End Using

There is another problem but this is more complex to solve. Storing passwords in clear text inside a database is considered a very high security risk. You should learn how to Hash and Salt passwords before storing them in the database

Upvotes: 3

Related Questions