CODEWITHSUNDEEP
vb.netsql-server-2012
Rangga Gumilang
Rangga Gumilang

Reputation: 7

Error: "There is already an open DataReader associated with this Connection which must be closed first"

i get this error message, this is my code, please help me

Private Sub txtceknofak_Leave(sender As Object, e As EventArgs) Handles txtceknofak.Leave

    Dim nf As String
    nf = "select no_fak from terima_cucian where no_fak = '" & txtceknofak.Text & "'"
    comm = New SqlCommand(nf, conn)
    rdr = comm.ExecuteReader()

    If rdr.HasRows Then
        btproses.Enabled = True
        btproses.Focus()
    Else
        MsgBox("Nomor faktur tidak ada")
        txtceknofak.Clear()
        txtceknofak.Focus()
    End If
    rdr.Close()

End Sub

Upvotes: 1

Views: 922

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460380

Since the connection is not part of the method it seems to be a field in your class. I would discourage from that. Instead use a local variable and open/close it when you need it. Actually the connection-pool will not really open/close the physical connection, so you don't need to be afraid that this is inefficient.

You can ensure that the connection gets closed even on error by using Try-Finally or -more readable and concise- with the Using-statement:

Private Sub txtceknofak_Leave(sender As Object, e As EventArgs) Handles txtceknofak.Leave
    Dim nf As String = "select no_fak from terima_cucian where no_fak = @no_fak"
    Using conn = New SqlConnection(connectionstring)
        Using comm = New SqlCommand(nf, conn)
            comm.Parameters.AddWithValue("@no_fak", txtceknofak.Text)
            conn.Open()
            Using rdr = comm.ExecuteReader()
                If rdr.HasRows Then
                    btproses.Enabled = True
                    btproses.Focus()
                Else
                    MsgBox("Nomor faktur tidak ada")
                    txtceknofak.Clear()
                    txtceknofak.Focus()
                End If
            End Using
        End Using
    End Using ' *** this will also close the connection *** '
End Sub

I have also used sql-parameters to prevent sql-injection.

Upvotes: 2

Kousick Shanmugam Nagaraj
Kousick Shanmugam Nagaraj

Reputation: 856

make new instance of the reader

rdr = new comm.ExecuteReader();

Thanks

Upvotes: 0

Related Questions