troubleshooting Question

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

Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America asked on
Microsoft SQL Server.NET ProgrammingVisual Basic.NET
5 Comments1 Solution425 ViewsLast Modified:
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")

            connection.Open()

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
                        reader2.Read()
                        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.ExecuteNonQuery()
                            command3 = Nothing

                        End If


                    Else

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


                    End If
                    reader2.Close()
                    command2 = Nothing
                    reader2 = Nothing

                Else

                    ''remove the program from LogProgram table...

                    'sSQL = "UPDATE LogProgram SET [ItExists] = 0 WHERE [ProgramID] = " & lProgramID & " AND [CPUID] = " & lCPUID
                    'command = New SqlCommand(sSQL, connection)
                    'Try
                    '    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
            reader.Close()
            connection.Close()
error-ee.PNG
ASKER CERTIFIED SOLUTION
BuggyCoder

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros