Open datareader in loop

Posted on 2009-12-18
Last Modified: 2012-05-08
Hi experts,

The attached code is part of a loop. The dataadapter, dataset and datatable are declared Private at the top the module.
The fill command results in an error (in dutch) saying that there is already an open datareader connected to this command.
I don't use a datareader, but I know the fill command creates one internally.
In the same loop the dataadapter, dataset and datatable are disposed and set to nothing, but that doesnt't help.
What am I doing wrong??

Thanks in advance
daKlantgegevens = New SqlDataAdapter(strSQL, conRaptor)

dsKlantgegevens = New DataSet

daKlantgegevens.Fill(dsKlantgegevens, "Klantgegevens")

dtKlantgegevens = dsKlantgegevens.Tables("Klantgegevens")

Open in new window

Question by:NoraWil
    LVL 30

    Expert Comment

    by:Reza Rad
    upload complete code here
    LVL 10

    Accepted Solution

    Are you using any datareader anywhere in the code?

    Author Comment

    Thanks for the fast replies.
    I attached a larger part of the code. The upper part is the calling part, beneath is the called procedure.

    Anv, no I don't use a datareader.
        Dim row As DataRow
          For Each row In dtAfTeDrukkenFacturen.Rows
            ' Overloop 1 voor 1 alle af te drukken facturen.
            'intHoeveelsteFactuur = intHoeveelsteFactuur + 1
            Dim col As DataColumn = dtAfTeDrukkenFacturen.Columns("Fac_intFnr")
            Dim intFactuurnummer As Integer = Convert.ToInt32(row(col))
            col = dtAfTeDrukkenFacturen.Columns("Fac_dtmFactuurdatum")
            dtmFactuurdatum = Convert.ToDateTime(row(col))
            col = dtAfTeDrukkenFacturen.Columns("Fac_strFactCN")
              If row(col).ToString = "C" Then
                blnCreditnota = True
                blnCreditnota = False
              End If
            col = dtAfTeDrukkenFacturen.Columns("Fac_strCodeKlant")
            strCodeKlant = row(col).ToString
            Printer.FontSize = 9
            Call Print_LegeFactuur()
            Call Verzamel_Kopgegevens(strNaam, strNaam2, strStraat, strPostcode, strGemeente, _
                                      strLandcode, strLand, strBTWnummer, strBTWregime)
            Call Print_FactKop(intFactuurnummer, dtmFactuurdatum, strNaam, strNaam2, strStraat, _
                               strPostcode, strGemeente, strLand, strBTWnummer)
            Call Verzamel_FactBlokken(intFactuurnummer)
            Call Print_FactBody(intFactuurnummer) ' De lus die alle blokken afdrukt
            Call Print_FactStaart(intFactuurnummer, "", "", "", "", "", "")
            Call Print_VermeldingOpFactuur(dtmFactuurdatum)
            Call Zet_Afgedrukt_Vlag(intFactuurnummer)
            'Me.prgAfdrukken.Value = intHoeveelsteFactuur / intAantalFacturen * 100
          Next row
        Call HistoriekBijwerken("Factuur " & strVan & " tot " & strTot & " afgedrukt.", conRaptor, 2, "Sub Print_Factuur")
        System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default                      ' Normale cursor
      End Sub
      Public Sub Verzamel_Kopgegevens(ByRef strNaam As String, _
                                      ByRef strNaam2 As String, _
                                      ByRef strStraat As String, _
                                      ByRef strPostcode As String, _
                                      ByRef strGemeente As String, _
                                      ByRef strLandcode As String, _
                                      ByRef strLand As String, _
                                      ByRef strBTWnummer As String, _
                                      ByRef strBTWregime As String)
        Dim strSQL As String = "SELECT Kla_strNaam, Kla_strNaam2, Kla_strStraat, Kla_strPostcode, Kla_strGemeente, " & _
                               "Kla_strLandcode, " & _
                               "Kla_strBTWnummer, Kla_strBTWregime " & _
                               "FROM [Klanten] WHERE Kla_strCodeKlant = '" & strCodeKlant & "'"
        daKlantgegevens = New SqlDataAdapter(strSQL, conRaptor)
        dsKlantgegevens = New DataSet
        dtKlantgegevens = New DataTable
        daKlantgegevens.Fill(dsKlantgegevens, "Klantgegevens")
        dtKlantgegevens = dsKlantgegevens.Tables("Klantgegevens")
        Debug.Print("Aantal rijen in datatable: " & dtKlantgegevens.Rows.Count)
          If Len(dtKlantgegevens.Rows(0)("Kla_strBTWnummer").ToString) > 8 Then                              ' Hou dus geen rekening met BTW = "GEEN"
            strBTWnummer = dtKlantgegevens.Rows(0)("Kla_strBTWnummer").ToString
          End If
          If dtKlantgegevens.Rows(0)("Kla_strNaam").ToString <> String.Empty Then
            strNaam = dtKlantgegevens.Rows(0)("Kla_strNaam").ToString
          End If
          If dtKlantgegevens.Rows(0)("Kla_strNaam2").ToString <> String.Empty Then
            strNaam2 = dtKlantgegevens.Rows(0)("Kla_strNaam2").ToString
          End If
          If dtKlantgegevens.Rows(0)("Kla_strStraat").ToString <> String.Empty Then
            strStraat = dtKlantgegevens.Rows(0)("Kla_strStraat").ToString
          End If
          If dtKlantgegevens.Rows(0)("Kla_strPostcode").ToString <> String.Empty Then
            strPostcode = dtKlantgegevens.Rows(0)("Kla_strPostcode").ToString
          End If
          If dtKlantgegevens.Rows(0)("Kla_strGemeente").ToString <> String.Empty Then
            strGemeente = dtKlantgegevens.Rows(0)("Kla_strGemeente").ToString
          End If
          If dtKlantgegevens.Rows(0)("Kla_strLandcode").ToString <> String.Empty Then
            strLandcode = Trim(dtKlantgegevens.Rows(0)("Kla_strLandcode").ToString)
          End If
          If dtKlantgegevens.Rows(0)("Kla_strBTWregime").ToString <> String.Empty Then
            strBTWregime = Trim(dtKlantgegevens.Rows(0)("Kla_strBTWregime").ToString)
          End If
        strLand = fLand(strLandcode)
        daKlantgegevens = Nothing
        dsKlantgegevens = Nothing
        dtKlantgegevens = Nothing
        'daKlantgegevens = Nothing
      End Sub

    Open in new window

    LVL 10

    Expert Comment

    in Verzamel_Kopgegevens
    why dont you use the same adapter?
    That seems to be the probem.

    Author Comment

    Sorry, sorry Anv, but I do use a DataReader in a different procedure. The datareader is declared locally there, so I thaught it couldnt interfere with the other code. But it does interfere. When I disable the calls to the procedure with te datareader, no error appears.
    So actually your question about the datareader has lead me to the solution.
    LVL 11

    Expert Comment

    You can try this code before calling the adapter.Fill() method.
    If conRaptor.State = ConnectionState.Open Then
            End If

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now