CODEWITHSUNDEEP
sql-servervb.net
bidipeppercrap
bidipeppercrap

Reputation: 51

Other SqlDataReader need to be closed, althought I've closed any other else

I've closed all the readers properly haven't I? Every time I trigger the function, it causes an error

System.InvalidOperationException: 'Invalid attempt to call Read when reader is closed.

Now I'm really frustated.

What's wrong with my code? Everything seems good.

Private Sub Button_EditData_Click(sender As Object, e As EventArgs) Handles Button_EditData.Click
    FormEnabler()
    Me.TextBox_BranchID.Enabled = False
    Me.Button_AddNew.Enabled = False
    Me.Button_EditData.Enabled = False
    Me.Button_DeleteData.Enabled = False
    Me.Button_Save.Enabled = True
    Me.Button_Cancel.Enabled = True
    Me.Button_ManageThisBranchStock.Enabled = False
    Me.Button_ManageThisBranchEmployee.Enabled = False

    theConnection.Open()

    Dim theEditInputCommand As New SqlCommand
    Dim theEditInputDataReader As SqlDataReader

    theEditInputCommand.Connection = theConnection
    theEditInputCommand.CommandText = "SELECT * FROM Branch WHERE BranchID = '" & Me.TextBox_BranchID.Text & "'"
    theEditInputDataReader = theEditInputCommand.ExecuteReader()

    If theEditInputDataReader.Read() Then
        Me.TextBox_Title.Text = theEditInputDataReader.Item("Title")
        Me.RichTextBox_Address.Text = theEditInputDataReader.Item("Address")
        Me.TextBox_ContactNumber.Text = theEditInputDataReader.Item("ContactNo")
        Me.ComboBox_BranchManager.Text = theEditInputDataReader.Item("BranchManager")
        theEditInputDataReader.Close()
    End If

    theConnection.Close()

    theConnection.Open()

    Dim theEditInputBranchManagerCommand As New SqlCommand
    Dim theEditInputBranchManagerDataReader As SqlDataReader
    Dim theEditInputBranchManagerDataTable As New DataTable

    theEditInputBranchManagerCommand.Connection = theConnection
    theEditInputBranchManagerCommand.CommandText = "SELECT EmployeeID FROM AssignmentDetail WHERE BranchID = '" & Me.TextBox_BranchID.Text & "'"
    theEditInputBranchManagerDataReader = theEditInputBranchManagerCommand.ExecuteReader()
    theEditInputBranchManagerDataTable.Load(theEditInputBranchManagerDataReader)

    If theEditInputBranchManagerDataReader.Read() Then
        Me.ComboBox_BranchManager.ValueMember = "EmployeeID"
        Me.ComboBox_BranchManager.DisplayMember = "EmployeeID"
        Me.ComboBox_BranchManager.DataSource = theEditInputBranchManagerDataTable
        theEditInputBranchManagerDataReader.Close()
    Else
        Me.ComboBox_BranchManager.ValueMember = "'-'"
        theEditInputBranchManagerDataReader.Close()
    End If

    theConnection.Close()
End Sub

The debugger pointed the errors around here:

If theEditInputBranchManagerDataReader.Read() Then
    Me.ComboBox_BranchManager.ValueMember = "EmployeeID"
    Me.ComboBox_BranchManager.DisplayMember = "EmployeeID"
    Me.ComboBox_BranchManager.DataSource = theEditInputBranchManagerDataTable
    theEditInputBranchManagerDataReader.Close()
Else
    Me.ComboBox_BranchManager.ValueMember = "'-'"
    theEditInputBranchManagerDataReader.Close()
End If

Upvotes: 0

Views: 44

Answers (2)

bidipeppercrap
bidipeppercrap

Reputation: 51

Okay so now I resolve that the problem is located in Load(DataTable)

Welp, I've changed my mind and switch it to DataSet instead of DataTable.

Thanks for answering my question. Have a good day.

Oh, and here is the working code

Using theEditCheckBranchManagerDataReader As SqlDataReader = theEditCheckBranchManagerCommand.ExecuteReader()
        If theEditCheckBranchManagerDataReader.Read() Then
            theEditCheckBranchManagerCommand.Dispose()
            theEditCheckBranchManagerDataReader.Close()
            theEditCheckBranchManagerDataReader.Dispose()
            theConnection.Close()
            SqlConnection.ClearPool(theConnection)

            Dim theEditInputBranchManagerCommand As New SqlCommand
            Dim theEditInputBranchManagerDataSet As New DataSet
            Dim theEditInputBranchManagerDataAdapter As New SqlDataAdapter

            theEditInputBranchManagerCommand.Connection = theConnection
            theEditInputBranchManagerCommand.CommandText = "SELECT EmployeeID FROM AssignmentDetail WHERE BranchID = '" & Me.TextBox_BranchID.Text & "'"
            theEditInputBranchManagerDataAdapter.SelectCommand = theEditInputBranchManagerCommand

            theEditInputBranchManagerDataAdapter.Fill(theEditInputBranchManagerDataSet)
            theEditInputBranchManagerDataAdapter.Dispose()
            theEditInputBranchManagerCommand.Dispose()

            Me.ComboBox_BranchManager.DataSource = theEditInputBranchManagerDataSet.Tables(0)
            Me.ComboBox_BranchManager.ValueMember = "EmployeeID"
            Me.ComboBox_BranchManager.DisplayMember = "EmployeeID"
        Else
            theEditCheckBranchManagerCommand.Dispose()
            theEditCheckBranchManagerDataReader.Close()
            theEditCheckBranchManagerDataReader.Dispose()
            theConnection.Close()
            SqlConnection.ClearPool(theConnection)

            Me.ComboBox_BranchManager.DataSource = Nothing
            Me.ComboBox_BranchManager.Items.Clear()
            Me.ComboBox_BranchManager.Text = "-"
        End If
    End Using

Upvotes: 0

Mederic
Mederic

Reputation: 2019

For everything that has IDisposable you should implement USING 

Using reader As SqlDataReader = command.ExecuteReader()
    While reader.Read()
        FPath = reader(0)
    End While
End Using

Also make sure to dispose of the reader .Dispose() if you don't want to use USING finally working with SQL it's always good to use:

SqlConnection.ClearPool(con)

Upvotes: 3

Related Questions