Avatar of stephenlecomptejr
Flag for United States of America asked on

Have to loop through one data reader and check another data reader at the same time but get error!

Please note image attachment below of error highlighting line: Dim reader2 As SqlDataReader = command2.ExecuteReader().

I get error message of there is already an open DataReader associated with this command which must be closed first.

I really only have command not command2 opened - so I don't know why the message.  Probably saying I can't have two datareaders opened at the same time.  I don't want to know why this is happening but rather how may I rewrite the code so that this can work?

Basically I have a base table (AllPrograms) full of programs' full path names to check to see if they exist on a person's machine in a log table (LogProgramID).  If it exists already then merely check the datemodified field and make sure its the same - if not then get it updated.

 Dim command As SqlCommand
        Dim command2 As SqlCommand
        Dim command3 As SqlCommand

        Using connection As New SqlConnection("Server=EC-SQL;uid=FKPTracker;pwd=goaway;database=FKPTracker")


sSQL = "SELECT [ProgramID], [ProgramFullName]"
            sSQL = sSQL & " FROM [AllPrograms]"
            sSQL = sSQL & " WHERE [ProgramMonitor] = 1"
            sSQL = sSQL & " ORDER BY [ProgramFullName]"
            command = New SqlCommand(sSQL, connection)
            reader = command.ExecuteReader()
            While reader.Read()

                lProgramID = reader(0).ToString
                sProgram = reader(1).ToString
                sDateModifiedNet = sProgram

                If File.Exists(sProgram) = True Then

                    sSQL = "SELECT [AuditID], [ModifiedDate] FROM [LogProgramID] WHERE [ProgramID] = " & lProgramID & " AND [CPUID] = " & lCPUID
                    command2 = New SqlCommand(sSQL, connection)
                    Dim reader2 As SqlDataReader = command2.ExecuteReader()
                    If reader2.HasRows Then
                        sDateModified = reader2.GetString(reader.GetOrdinal("ModifiedDate"))

                        If sDateModified <> sDateModifiedNet Then

                            sSQL = "UPDATE [LogProgramID] SET [DateModified] = '" & sDateModifiedNet & "'"
                            sSQL = sSQL & " WHERE [ProgramID] = " & lProgramID & " AND [CPUID] = " & lCPUID
                            command3 = New SqlCommand(sSQL, connection)
                            command3 = Nothing

                        End If


                        sSQL = "INSERT INTO [LogProgramID] ([ProgramID], [CPUID], [DateModified]) VALUES (" & lProgramID & ", " & lCPUID & ", " & sDateModifiedNet & ")"
                        command3 = New SqlCommand(sSQL, connection)
                        command3 = Nothing

                    End If
                    command2 = Nothing
                    reader2 = Nothing


                    ''remove the program from LogProgram table...

                    'sSQL = "UPDATE LogProgram SET [ItExists] = 0 WHERE [ProgramID] = " & lProgramID & " AND [CPUID] = " & lCPUID
                    'command = New SqlCommand(sSQL, connection)
                    '    command.ExecuteNonQuery()
                    'Catch ex As Exception
                    '    ErrorMessageString = ex.ToString & " - " & ex.Message
                    '    Call ExceptionClassObject.WriteExceptionErrorToFile(ExceptionErrorFileString, Me.Name, sMsg, ErrorMessageString, WriteErrorMessageString)
                    'End Try

                    'command = Nothing
                End If

            End While

Open in new window

Visual Basic.NET.NET ProgrammingMicrosoft SQL Server

Avatar of undefined
Last Comment

8/22/2022 - Mon

The trouble is that the datareader class maintains an open connection to the database.  This connection cannot be shared.  So you would have to declare and use another connection object or use a datatable or other disconnected object for your queries.


While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.


I need someone's help with rearranging the syntax of the code above to make it work please!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

I would still tend to advise instead that you use a disconnected model like a datatable to loop through, as consuming a connection the entire time you are processing updates is expensive.  If you start to get connection timeouts on your server, you might consider revisiting this.  But if that's working for you, good.