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
Do more with
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial