Invalid attempt to read when reader is closed Error

Question

I am stumped with this problem, my database is local host and every other form I have no issues with but here, when I click the update button, I get this error which is very frustrating as I am very sure the SQL connection should be open! My code for the button is given.

Error: 'Invalid attempt to read when reader is closed'

Private Sub UpdateButton_Click(sender As Object, e As EventArgs) Handles UpdateButton.Click

    MysqlConn = New MySqlConnection
    MysqlConn.ConnectionString = "server=localhost;userid=root;password=alpine;database=database1"
    Dim Reader As MySqlDataReader

    Dim vals(2, 2) As Decimal
    vals(0, 2) = Convert.ToDecimal(Cbudget.Text.ToString - OutBox.Text.ToString + InBox.Text.ToString)
    vals(1, 0) = Convert.ToDecimal(Newfigbox.Text.ToString)
    vals(2, 0) = Convert.ToDecimal(OutBox.Text.ToString - InBox.Text.ToString)
    vals(1, 1) = Convert.ToDecimal(FigureBox.Text.ToString + Newfigbox.Text.ToString)

    Try
        MysqlConn.Open()
        Dim Query As String
        Query = "UPDATE accounts SET Current_Budget='" & vals(1, 0) & "';"
        Command = New MySqlCommand(Query, MysqlConn)
        Reader = Command.ExecuteReader
        MysqlConn.Close()
    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
        MysqlConn.Close()
    End Try

    Try
        MysqlConn.Open()
        Dim Query As String
        Query = "UPDATE stocktable, clientdetails SET stocktable.Quantity='" & vals(1, 1) & "', clientdetails.Balance='" & vals(2, 0) & "' WHERE 'stocktable.Type_Of_Metal' ='" & ComboBox1.Text & "' AND 'clientdetails.Name' ='" & ClientBox.Text & "';"
        Command = New MySqlCommand(Query, MysqlConn)
        Reader = Command.ExecuteReader
        MysqlConn.Close()
    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
        MysqlConn.Close()
    End Try
    Cbudget.Text = Nothing
    Try
        MysqlConn.Open()
        Dim Query As String
        Query = "SELECT Current_Budget FROM accounts"
        Command = New MySqlCommand(Query, MysqlConn)
        Reader = Command.ExecuteReader
        MysqlConn.Close()
        While Reader.Read
            vals(0, 1) = Reader.GetDecimal("Current_Budget")
            Cbudget.Text = vals(0, 1)
        End While
        MysqlConn.Close()
    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
        MysqlConn.Close()
    End Try

End Sub'

Probably is a copy paste error. In your last query you close the connection before reading. Remove that closing. — Steve, Jan 25 '15 at 17:52

score 2 · Accepted Answer · edited May 23 '17 at 11:51

Probably your problem is just a copy/paste error. It seems that before executing the reader for the last SELECT command you close the connection. (As you do in your previous two UPDATE commands) This effectively is the reason of your error message.
But a part from this you have other problems in your code.

First. you should send your UPDATE using ExecuteNonQuery. The ExecuteReader is used when you want to read back something from your database via a SELECT query. It works also for UPDATE/INSERT/DELETE etc but the infrastructure created for reading is not needed and thus is a useless consumption of resources.

Second. You could send two (or more) commands using a single commandtext. Just separe the two commands with a semicolon and execute the commandtext via ExecuteNonQuery

Third. You should always use a parameterized query to avoid Sql Injection and problem with parsing your values. For example a decimal contains the decimal separator that in your locale is expressed via a particular symbol, but in other locales is different, and thus your queries stop to work. (The same problem happens also with normal strings that contain a single quote). The parameter approach will remove both of these problems.

Finally. All disposable objects like Connections, Commands and Readers should be disposed when you finish to use them. The Using Statement ensures this correct behavior (And If you wonder about the closing of the connection, the End Using also close the connection)

Dim vals(2, 2) As Decimal
vals(0, 2) = Convert.ToDecimal(Cbudget.Text.ToString - OutBox.Text.ToString + InBox.Text.ToString)
vals(1, 0) = Convert.ToDecimal(Newfigbox.Text.ToString)
vals(2, 0) = Convert.ToDecimal(OutBox.Text.ToString - InBox.Text.ToString)
vals(1, 1) = Convert.ToDecimal(FigureBox.Text.ToString + Newfigbox.Text.ToString)

Query = "UPDATE accounts SET Current_Budget=@budget; " & _
        "UPDATE stocktable, clientdetails " & _
        "SET stocktable.Quantity=@qty, " & _
        "clientdetails.Balance=@bal " & _ 
        "WHERE `stocktable`.`Type_Of_Metal` = @type " & _
        "AND `clientdetails`.`Name` = @client;"
Try
    Using MysqlConn = New MySqlConnection(...constring here...)
    Using Command = New MySqlCommand(Query, MysqlConn)
        command.Parameters.AddWithValue("@budget", vals(1, 0))
        command.Parameters.AddWithValue("@qty", vals(1, 1))
        command.Parameters.AddWithValue("@bal", vals(2, 0))
        command.Parameters.AddWithValue("@type", ComboBox1.Text)
        command.Parameters.AddWithValue("@client", ClientBox.Text)
        command.ExecuteNonQuery()

        Command.CommandText = "SELECT Current_Budget FROM accounts"
        Command.Parameters.Clear()        
        Using Reader = Command.ExecuteReader()
            While Reader.Read
                vals(0, 1) = Reader.GetDecimal("Current_Budget")
                Cbudget.Text = vals(0, 1)
            End While
        End Using
    End Using
Catch ex As MySqlException
    MessageBox.Show(ex.Message)
End Try

Invalid attempt to read when reader is closed Error

1 Answers1