[Last Call] Learn how to a build a cloud-first strategyRegister Now


Open datareader in loop

Posted on 2009-12-18
Medium Priority
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
ID: 26078903
upload complete code here
LVL 10

Accepted Solution

anv earned 2000 total points
ID: 26078953
Are you using any datareader anywhere in the code?

Author Comment

ID: 26079034
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 10

Expert Comment

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

Author Comment

ID: 26079250
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

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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
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…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Loops Section Overview
Suggested Courses

830 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