asked on

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

Reza Rad

upload complete code here

ASKER CERTIFIED SOLUTION

anv

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NoraWil

ASKER

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

anv

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

NoraWil

ASKER

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.

ROMA CHAUHAN

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