Simple VB.Net sub throws an ununderstandable error

Hi

I have a very simple Sub which selects 5 fields out of one table using a a straightforward query and which then uses a reader to read the selected rows and store the data in another table. However, that Sub throws an error which I just cannot understand.

Here the Sub code:
    Public Sub RefreshBBgIssuerTable()
        Dim conn As New SqlConnection
        Dim sDeleteCommand As String
        Dim sSelectCommand As String
        Dim sInsertCommand As String
        Dim command As New SqlCommand
        Dim commIns As New SqlCommand
        Dim myreader As SqlDataReader
        Dim sTemp As String
        Dim sPhase As String
        Dim nCount As Integer

        sDeleteCommand = ""
        sSelectCommand = ""
        sInsertCommand = ""
        sPhase = ""

        Try
            ' Define connection
            conn.ConnectionString = connStrSQL
            conn.Open()

            ' Delete previous content from BBg isuers table
            sPhase = "Delete previous content from BBg isuers table"
            sDeleteCommand = "DELETE FROM tbBBgIssuers"
            command.Connection = conn
            command.CommandText = sDeleteCommand
            command.ExecuteNonQuery()

            ' Select the data from tbSecurityMasterData
            sPhase = "Select and Insert"
            sSelectCommand = "SELECT DISTINCT BBgIDIssuer, Issuer, BBgIDTopEntity, UltimateParentName, IndustrySectorBBG " & _
                             "FROM tbSecurityMasterData WHERE BBgIDIssuer is not null ORDER BY BBgIDIssuer"
            command.CommandText = sSelectCommand
            myreader = command.ExecuteReader()
            nCount = 0
            ' Loop on selected rows
            While myreader.Read
                ' Insert row in table
                sInsertCommand = "INSERT INTO tbBBgIssuers " & _
                                 "(IssuerBBgID, Issuer, UltimateParentCpnyNo, UltimateParentCpnyName, IndustrySector) " & _
                                 "VALUES " & _
                                 "('" & _
                                 myreader("BBgIDIssuer") & "', '" & _
                                 myreader("Issuer") & "', " & _
                                 myreader("BBgIDTopEntity") & ", '" & _
                                 myreader("UltimateParentName") & "', '" & _
                                 myreader("IndustrySectorBBG") & _
                                 "')"
                commIns.Connection = conn
                commIns.CommandText = sInsertCommand
                commIns.ExecuteNonQuery()
                ' Give them some clues as where we are
                nCount = nCount + 1
                If nCount Mod 200 = 0 Then
                    Call StatusBar("Inserted " & nCount & " rows in BBg issuer table")
                End If
            End While
            ' Stats
            Call StatusBar("Inserted a total of " & nCount & " rows in BBg issuer table")
            ' Close reader and connection
            myreader.Close()
            conn.Close()

        Catch ex As Exception
            sTemp = "Error: " & Err.Description & " in RefreshBBgIssuerTable in phase " & sPhase
            Call HandleErrors(sTemp)
            myreader.Close()
            conn.Close()
        End Try

    End Sub

Open in new window


The error thrown is: There is already an open DataReader associated with this Command which must be closed first
The error happens on the first commIns.ExecuteNonQuery()

Thanks for help.
Bernard
LVL 1
bthouinIT Analyst and developerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
because you are having a reader (which holds the connection) and try to reuse the same connection for different operation.

you can easily fix it by adding "MultipleActiveResultSets=True" to your connection string

have a look at http://emoreau.com/Entries/Articles/2006/11/MARS-and-Asynchronous-ADONet.aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bthouinIT Analyst and developerAuthor Commented:
Thanks, I just defined 2 connections, and it works "as advertised"... :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.