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") 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()