none
"There is already an open DataReader" in Visual Studio 2017, Windows Form, VB.Net Project

    Question

  • I trust that I have this in the right forum.  There are many entries in the forum archives talking about the Operation Exception: 'There is already an open DataReader ...." exception but they all point out corrections in VB.Net code constructions and not in 2017 VS environments. 

    I am trying to resolve the following exception.

    I am assuming that these two 'readers' are associated with invocation of queries, since this error is pointing at a query invocation.  In this project, I have one dataset with two queries, both using the Access table ECARS_db (.accdb database).  The above error is being thrown when I execute the 2nd defined and 2nd called query.  The first defined query implements without any such problem as shown in the following picture.

    I have written no code invoking DataReaders or DataReader classes.  I have searched all the VS2017 written code and cannot find any references to 'DataReader'.  There are a number of references to XML & String readers, but I have no idea what that is all about.  These XML & String reader references are shown here

    and here

    I am not at all certain that the information provided is sufficient to understand the problem or identify a fix.  Accordingly I have provided a OneDrive link to the Project if there is someone enterprising enough to jump in. 

    https://1drv.ms/u/s!ArLrJx_AnLzDdhHt45s2Ud4CYaI

    Bob Goodwin


    Bob Goodwin

    Wednesday, March 22, 2017 9:34 PM

Answers

  • Look at this code, search for KP and see what I did. I stopped the data reader issue, now you need to consider what I did and how that affects your code logic. The commented out code was triggering the TextChanged event on you which in turn called the FillByTboCallSign unexpectedly.

    Public Class FrmLogger
        Private ConnectionString As String = ""
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            ConnectionString = ECARS_dbTableAdapter.Connection.ConnectionString
    
            'TODO: This line of code loads data into the 'LoggerDataSet1.ECARS_db' table. You can move, or remove it, as needed.
            Me.ECARS_dbTableAdapter.Fill(Me.LoggerDataSet1.ECARS_db)
            'TODO: This line of code loads data into the 'LoggerDataSet.ECARS_db' table. You can move, or remove it, as needed.
            Me.ECARS_dbTableAdapter.Fill(Me.LoggerDataSet.ECARS_db)
    
        End Sub
    
        Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TboFind.TextChanged
            Me.ECARS_dbTableAdapter.FillByCallSign(Me.LoggerDataSet.ECARS_db, TboFind.Text)
        End Sub
    
        Private Sub TboSuffix_KeyPress(sender As Object, e As KeyPressEventArgs) Handles TboSuffix.KeyPress
            If e.KeyChar = Convert.ToChar(13) Then
                Dim suffix = TboSuffix.Text
                suffix = suffix.Trim
                If suffix.Length > 0 Then
                    LboSuffixList.Items.Add(TboSuffix.Text)
                End If
                TboSuffix.Text = ""
            End If
        End Sub
    
        Private Sub LboSuffixList_MouseClick(sender As Object, e As MouseEventArgs) Handles LboSuffixList.MouseClick
            TboFind.Text = LboSuffixList.SelectedItem
        End Sub
    
        Private Sub LboFindList_SelectedValueChanged(sender As Object, e As EventArgs) Handles LboFindList.SelectedValueChanged
            TboFoundCount.Text = LboFindList.Items.Count
            If TboFind.Text = "" Then
                LboHide.Visible = True  'use ListBox1 to hide values of LboFindList
                TboHide.Visible = True   'use TextBox1 to hide value of TboFoundCount
                ' KP Me.TboCallSign.Text = "  "   ' clear Check in boxes values from results of database null search string 
                Me.TboName.Text = "  "
                Me.TboCity.Text = "  "
                Me.TboState.Text = "  "
                Me.TboECARSNo.Text = "  "
                Me.TboLastCheckedIn.Text = "  "
                Me.TboDataLastChanged.Text = "  "
                Me.TboNotes.Text = "  "
            Else
                LboHide.Visible = False
                TboHide.Visible = False
                'Following transfers values from database entry from LboFindList to Check In text boxes
                ' LboFindList entries are of type DataRowView and must be converted to String before writing to text boxes
                For Each objDataRowView As DataRowView In Me.LboFindList.SelectedItems
                    ' KP Me.TboCallSign.Text = (objDataRowView("Call_Sign").ToString())
                    Me.TboName.Text = (objDataRowView("Handle").ToString())
                    Me.TboCity.Text = (objDataRowView("City").ToString())
                    Me.TboState.Text = (objDataRowView("State").ToString())
                    Me.TboECARSNo.Text = (objDataRowView("ECARS_No").ToString())
                    Dim lastCheckedIn = (objDataRowView("Last_Checked_In").ToString())
                    Dim position = lastCheckedIn.IndexOf(" ")  'Find first location of blank space 
                    If position > -1 Then
                        lastCheckedIn = lastCheckedIn.Substring(0, position)   'Save the left most part of lastCheckedIn out to the blank space
                    End If
                    Me.TboLastCheckedIn.Text = lastCheckedIn
                    Dim dataLastChanged = (objDataRowView("Data_Last_Changed").ToString())
                    position = dataLastChanged.IndexOf(" ")
                    If position > -1 Then
                        dataLastChanged = dataLastChanged.Substring(0, position)
                    End If
                    Me.TboDataLastChanged.Text = dataLastChanged
                    Me.TboNotes.Text = (objDataRowView("Notes").ToString())
                Next
            End If
            If LboFindList.Items.Count = 0 Then  'if no items in the database match the Find string
                ' KP Me.TboCallSign.Text = ""
                Me.TboName.Text = ""
                Me.TboCity.Text = ""
                Me.TboState.Text = ""
                Me.TboECARSNo.Text = ""
                Me.TboLastCheckedIn.Text = ""
                Me.TboDataLastChanged.Text = ""
                Me.TboNotes.Text = ""
            End If
    
        End Sub
    
        Private Sub CmdNewList_Click(sender As Object, e As EventArgs) Handles CmdNewList.Click
            LboSuffixList.Items.Clear()
        End Sub
    
        Private Sub TboCallSign_TextChanged(sender As Object, e As EventArgs) Handles TboCallSign.TextChanged
            Me.ECARS_dbTableAdapter.FillByTboCallSign(Me.LoggerDataSet1.ECARS_db, TboCallSign.Text)
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by BobGn Thursday, March 23, 2017 7:09 PM
    Wednesday, March 22, 2017 11:47 PM
    Moderator

All replies


  • Regards Les, Livingston, Scotland

    Wednesday, March 22, 2017 9:45 PM
  • Thanks for the education.  It never occurred to me that people would copy code to their own code editor.  I started to use the 'insert code block' but noticed that it lost the highlighting of where the 'reader' references were, so I went to the images.  In this case, since I posted the entire project, those who are using VS2017, won't need to copy any code. 

    In the future, I'll do it right.

    Bob, Newport News, VA, US, retired naval officer and engineer who is trying to teach himself how to use VS2017 and to make a project for my amateur radio hobby.


    Bob Goodwin

    Wednesday, March 22, 2017 9:58 PM
  • Hi

    You can easily highlight within a code block, just select (say) a line of code and use the Bold button in the toolbar (for example).

    I use VS 2017, and if I wanted to help, I would need to retype the code - why do you say that 2017 users don't need to do that?

    I think you are referring to the link - many people are fearful of downloading code.


    Regards Les, Livingston, Scotland


    • Edited by leshay Wednesday, March 22, 2017 10:07 PM
    Wednesday, March 22, 2017 10:04 PM
  • I was referring to downloading the project code so that no one would to copy anything, and they would see all code in full context.  The little code segments in the images seem likely to me to be unrelated to the issue. That said, later tonight, I'll try to go back in and replace the images with code segments.

    Bob Goodwin


    Bob Goodwin

    Wednesday, March 22, 2017 10:52 PM
  • Look at this code, search for KP and see what I did. I stopped the data reader issue, now you need to consider what I did and how that affects your code logic. The commented out code was triggering the TextChanged event on you which in turn called the FillByTboCallSign unexpectedly.

    Public Class FrmLogger
        Private ConnectionString As String = ""
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            ConnectionString = ECARS_dbTableAdapter.Connection.ConnectionString
    
            'TODO: This line of code loads data into the 'LoggerDataSet1.ECARS_db' table. You can move, or remove it, as needed.
            Me.ECARS_dbTableAdapter.Fill(Me.LoggerDataSet1.ECARS_db)
            'TODO: This line of code loads data into the 'LoggerDataSet.ECARS_db' table. You can move, or remove it, as needed.
            Me.ECARS_dbTableAdapter.Fill(Me.LoggerDataSet.ECARS_db)
    
        End Sub
    
        Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TboFind.TextChanged
            Me.ECARS_dbTableAdapter.FillByCallSign(Me.LoggerDataSet.ECARS_db, TboFind.Text)
        End Sub
    
        Private Sub TboSuffix_KeyPress(sender As Object, e As KeyPressEventArgs) Handles TboSuffix.KeyPress
            If e.KeyChar = Convert.ToChar(13) Then
                Dim suffix = TboSuffix.Text
                suffix = suffix.Trim
                If suffix.Length > 0 Then
                    LboSuffixList.Items.Add(TboSuffix.Text)
                End If
                TboSuffix.Text = ""
            End If
        End Sub
    
        Private Sub LboSuffixList_MouseClick(sender As Object, e As MouseEventArgs) Handles LboSuffixList.MouseClick
            TboFind.Text = LboSuffixList.SelectedItem
        End Sub
    
        Private Sub LboFindList_SelectedValueChanged(sender As Object, e As EventArgs) Handles LboFindList.SelectedValueChanged
            TboFoundCount.Text = LboFindList.Items.Count
            If TboFind.Text = "" Then
                LboHide.Visible = True  'use ListBox1 to hide values of LboFindList
                TboHide.Visible = True   'use TextBox1 to hide value of TboFoundCount
                ' KP Me.TboCallSign.Text = "  "   ' clear Check in boxes values from results of database null search string 
                Me.TboName.Text = "  "
                Me.TboCity.Text = "  "
                Me.TboState.Text = "  "
                Me.TboECARSNo.Text = "  "
                Me.TboLastCheckedIn.Text = "  "
                Me.TboDataLastChanged.Text = "  "
                Me.TboNotes.Text = "  "
            Else
                LboHide.Visible = False
                TboHide.Visible = False
                'Following transfers values from database entry from LboFindList to Check In text boxes
                ' LboFindList entries are of type DataRowView and must be converted to String before writing to text boxes
                For Each objDataRowView As DataRowView In Me.LboFindList.SelectedItems
                    ' KP Me.TboCallSign.Text = (objDataRowView("Call_Sign").ToString())
                    Me.TboName.Text = (objDataRowView("Handle").ToString())
                    Me.TboCity.Text = (objDataRowView("City").ToString())
                    Me.TboState.Text = (objDataRowView("State").ToString())
                    Me.TboECARSNo.Text = (objDataRowView("ECARS_No").ToString())
                    Dim lastCheckedIn = (objDataRowView("Last_Checked_In").ToString())
                    Dim position = lastCheckedIn.IndexOf(" ")  'Find first location of blank space 
                    If position > -1 Then
                        lastCheckedIn = lastCheckedIn.Substring(0, position)   'Save the left most part of lastCheckedIn out to the blank space
                    End If
                    Me.TboLastCheckedIn.Text = lastCheckedIn
                    Dim dataLastChanged = (objDataRowView("Data_Last_Changed").ToString())
                    position = dataLastChanged.IndexOf(" ")
                    If position > -1 Then
                        dataLastChanged = dataLastChanged.Substring(0, position)
                    End If
                    Me.TboDataLastChanged.Text = dataLastChanged
                    Me.TboNotes.Text = (objDataRowView("Notes").ToString())
                Next
            End If
            If LboFindList.Items.Count = 0 Then  'if no items in the database match the Find string
                ' KP Me.TboCallSign.Text = ""
                Me.TboName.Text = ""
                Me.TboCity.Text = ""
                Me.TboState.Text = ""
                Me.TboECARSNo.Text = ""
                Me.TboLastCheckedIn.Text = ""
                Me.TboDataLastChanged.Text = ""
                Me.TboNotes.Text = ""
            End If
    
        End Sub
    
        Private Sub CmdNewList_Click(sender As Object, e As EventArgs) Handles CmdNewList.Click
            LboSuffixList.Items.Clear()
        End Sub
    
        Private Sub TboCallSign_TextChanged(sender As Object, e As EventArgs) Handles TboCallSign.TextChanged
            Me.ECARS_dbTableAdapter.FillByTboCallSign(Me.LoggerDataSet1.ECARS_db, TboCallSign.Text)
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by BobGn Thursday, March 23, 2017 7:09 PM
    Wednesday, March 22, 2017 11:47 PM
    Moderator
  • Thanks Karen.  That has certainly isolated the issue nicely.  This does present an issue to the logic of my program.  At the moment I don't see a way to work around it, but its early yet.  If I disable the 'TboCallSign changed' event, I can leave all three of the commented out lines active and the program repeatedly does what I need it to do up to this point.

    If I leave the 'TboCallSign changed' event active, I can get basic data flow if I comment out the first line that you commented out, but leave the other two active.  The interesting effect of that is that

    a. on odd numbered clicks of the data in the LboSuffix listbox, the data is properly written to the TboFind textbox and the LboFindList listbox properly fills with those call signs in the database that are partial matches to the clicked data in the LboSuffix listbox.  In other words, the first query of finding a partial match works.

    b.  on even numbered clicks of the data in the LboSuffix listbox, the data is properly written in the TboFind text box but, improperly, no data is written into the LboFindList listbox. The next click, on a different Suffix list box entry, then correctly results in data in the Find listbox.  There is something happening here that I don't understand so I need to troubleshoot. 

    I guess that I'm about to learn more about the debugger and how to trace data flow and values of variables.

    Ultimately,  I need to be able to manually enter a TboCallSign data value and the associated values below it in order to enter new data into the database.

    Let me troubleshoot a bit before I sign this off as solved.  I'll get back not later than Friday.

    Are the queries the 'datareaders'?  Why can't two be open at the same time?  They are NOT executing at the same time, but in sequence as the data flows from Suffix listbox to Find text box to Find listbox to CallSign textbox?  To me, there does not appear to be circular logic that should trigger an exception.

    Bob Goodwin


    Bob Goodwin

    Thursday, March 23, 2017 2:26 AM
  • In regards to DataReader, the underlying code that was generated for you has one DataReader per table which if done without using generated code but you writing code you could close the DataReader once done with executing the query but since you are dealing with generated code there is no way to close the DataReader unlike if it was written by you.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, March 23, 2017 9:41 AM
    Moderator
  • Karen, I am going to back this project up a bit and shift to VB.net coding for the database links.  If I can do this via code written for the purpose vice auto generated by VS2017, I think I will be better able to achieve the goals of the project.  Thanks again for finding the source of the problem.

    Bob Goodwin

    Thursday, March 23, 2017 7:11 PM