Already a DataReader opened which must be closed first

Question

I'm trying to replace the "last time logged in date" on a database. As for not the database shows a String that says 'first session'.

I've done a lot to this application. Create an account, verifying that it doesn't already exists, etc.

This is my connection Function, which I created to be called from wherever on the program:

Function ServerConnection()
    Try
        MyConnection.ConnectionString = "server=127.0.0.1;user=root;password=password;database=titulo"
        MyConnection.Open()
        Return True
    Catch ex As Exception
        Return False
    End Try
End Function

This is what happens when you 'login' using your username and password (nevermind the SQLInjection issues):

Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click
    ServerConnection()
    Try
        Dim MyCommand As New MySqlCommand("select * from titulo.accounts where accountName='" & txtUsername.Text & "' and accountPassword='" & Hash512(txtPassword.Text, txtUsername.Text) & "'", MyConnection)
        Dim MyReader As MySqlDataReader
        MyReader = MyCommand.ExecuteReader
        If Not MyReader.HasRows Then
            MsgBox("No se ha encontrado ninguna cuenta con estas credenciales.")
        Else
            MyReader.Read()
            connectedAccount.Accounts(MyReader.GetInt32("id"), MyReader.GetString("accountName"), MyReader.GetString("accountMail"), MyReader.GetString("accountLogon"))
            CharacterManager.Show()
        End If
        MyReader.Dispose()
        MyReader.Close()
        MyConnection.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        MyConnection.Dispose()
    End Try
End Sub

So far it works, I can login and on the next form I can see the data stored on the 'connectedAccount' object.

This is where everything stopped working:

Private Sub CharacterManager_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Try
        ServerConnection()
        Dim MyCommand As New MySqlCommand("update titulo.accounts set accountLogon='" & lastLogon & "' where id= '" & connectedAccount.GetAccountId & "'", MyConnection)
        MyCommand.ExecuteNonQuery()
        lblAccountName.Text = connectedAccount.GetAccountName
        lblAccountLogon.Text = connectedAccount.GetAccountLogon
        MyConnection.Close()
    Catch ex As MySqlException
        MsgBox(ex.Message)
    Finally
        MyConnection.Dispose()
    End Try
End Sub

Whenever I run this I get a

There is already an open DataReader associated with this Connection which must be closed first.

How should I proceed?

Answer 1

In that second code snippet, you call ExecuteReader and then you call CharacterManager.Show() before closing that data reader. Showing a form will raise its Load event and in the Load event handler of that form you are calling ExecuteNonQuery... while the data reader is still open. See the issue?

Perhaps what you should be doing is use a data adapter to populate a DataTable, editing the data in that and then saving all the changes with one call to the Update method of that data adapter.