Link to home
Start Free TrialLog in
Avatar of stephenlecomptejr
stephenlecomptejrFlag 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

Avatar of PaulHews
Flag of Canada image

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.
Avatar of stephenlecomptejr


I need someone's help with rearranging the syntax of the code above to make it work please!
Avatar of BuggyCoder
Flag of India image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.