Open datareader in loop

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

NoraWilAsked:
Who is Participating?
 
anvCommented:
Are you using any datareader anywhere in the code?
0
 
Reza RadConsultant, TrainerCommented:
upload complete code here
0
 
NoraWilAuthor Commented:
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
          Else
            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)
        Printer.NewPage()
        'Me.prgAfdrukken.Value = intHoeveelsteFactuur / intAantalFacturen * 100
      Next row
    Printer.EndDoc()
    dtAfTeDrukkenFacturen.Dispose()
    dsAfTeDrukkenFacturen.Dispose()
    dtFactBlokDetails.Dispose()
    dsFactBlokDetails.Dispose()
    dtFactBlokken.Dispose()
    dsFactBlokken.Dispose()
    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.Dispose()
    daKlantgegevens = Nothing
    dsKlantgegevens.Dispose()
    dsKlantgegevens = Nothing
    dtKlantgegevens.Dispose()
    dtKlantgegevens = Nothing
    'daKlantgegevens.Dispose()
    'daKlantgegevens = Nothing
  End Sub

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
anvCommented:
in Verzamel_Kopgegevens
why dont you use the same adapter?
That seems to be the probem.
0
 
NoraWilAuthor Commented:
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.
0
 
ROMA CHAUHANProject LeadCommented:
You can try this code before calling the adapter.Fill() method.
If conRaptor.State = ConnectionState.Open Then
            conRaptor.Close()
        End If
        conRaptor.Open()
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.