ExecuteReader requires an open and available Connection - current state is closed

Question

all.
I have been struggling with this issue since it raised its head late last night.
Not sure WHY it is happening, but it is. What used to work with just ONE Artistcon.open()
Now requires me to have it at every new "ExecuteReader".
Below is a sample of the code. Starting with the connection string, and then the actual code.
I have the line marked below, for easy finding.

I have tried to put the ArtistCon.open() within the code area, above the select statement at that area, but it errors out,

web.config => database.config

<connectionStrings>
<add name ="Local" connectionString="Data Source=ServerName\InstanceName;Database=DatabaseName;User ID=UserName;Password=********;MultipleActiveResultSets=True"/>
</connectionStrings>

default.vb.aspx

Dim Artistcon As New SqlConnection
Artistcon = New SqlConnection(ConfigurationManager.ConnectionStrings("Local").ConnectionString)

    Artistcon.Open()
    Dim chWriters As New SqlCommand("SELECT TrackID, WID FROM Tracks WHERE (TrackFileName = @TrackFileName)", Artistcon)
   chWriters.Parameters.Add(New SqlParameter("@TrackFileName", getfile & ".mp3"))
    Dim rschhWriters As SqlDataReader
     rschhWriters = chWriters.ExecuteReader(Data.CommandBehavior.CloseConnection)
     If rschhWriters.Read() Then

     Dim sW As String = strWriters
     Dim leftString = sW.Substring(0, sW.IndexOf("["))
     Dim Wparts As String() = leftString.Split(New Char() {"/"c})

                        ' Loop through result strings with For Each.
     Dim Wpart As String

     For Each Wpart In Wparts
     Dim theWriters As String = Replace(Wpart, " ", "_")

     Dim getWriters As New SqlCommand("SELECT WritersID, WName FROM Writer WHERE (WName = @WName)", Artistcon)
     getWriters.Parameters.Add(New SqlParameter("@WName", theWriters.TrimEnd(CChar("_"))))
     Dim rsWriters As SqlDataReader
     ' =================ERROR LINE BELOW=================
     ' ExecuteReader requires an open and available Connection. The connection's current state is closed.
     rsWriters = getWriters.ExecuteReader(Data.CommandBehavior.CloseConnection) <<< ERROR LINE
     ' =================ERROR LINE ABOVE=================
     If Not rsWriters.Read() Then ' If record exist

     lblWriter.Text = "Insert WName=" & Wpart
     strWriters = "INSERT INTO Writer(WName)VALUES(@WName)"
     WriterCMD = New SqlCommand(strWriters, Artistcon)
     WriterCMD.Parameters.Add(New SqlParameter("@WName", theWriters.TrimEnd(CChar("_"))))
     Try
       WriterCMD.ExecuteNonQuery()
     Catch ex As Exception
     Finally

    End Try
    End If
    rsWriters.Close()
    getWriters.Dispose()

I did not have an issue with this code before until I started adding to it and changing the code up, and then all of a sudden, I began getting this error on every nested record and had to put the ArtistCon.open() on every ExecuteReader, and then I get this one, in a nested statement.

Thanks.

Answer 1

You are specifically asking to close your connection with this call:

Dim rschhWriters As SqlDataReader
rschhWriters = chWriters.ExecuteReader(Data.CommandBehavior.CloseConnection)

So try changing it to just

Dim rschhWriters As SqlDataReader = chWriters.ExecuteReader()

Note: Do favor using your disposable objects with Using - End Using blocks. Your empty Try-Catch is problematic, too, because you are hiding problems that will be difficult to debug later. You want to see any exceptions so you can change your code to prevent them from happening.