I am hoping this is just a case of having been banging my head too long, or someone has seen something similar.
I have a 2-page dashboard for calculating ROI on customer projects. The first page asks a number of questions and saves these to a small sqlite file. The user can submit up to 5 projects at a time, and then the second page shows a table of the man-hours and dollars saved for the project. It has (up until today) been working.
For the initial build I was simply looking in the default location for the sqlite files and finding the one that matched *.tmp, the rest of the file name was random. When a user closes out it deletes the file, so no need to worry about the name. Moving forward we’ll have multiple people accessing at the same time, so I thought it would be better to begin naming the files using the $SessionID variable. Since changing this section, page two generates the following error:
The logic for page one all seems to be working just the same as it ever did. I first do a check for the existence of the file (in case they are adding multiple projects) and if it does not exist, create it:
try {
Get-Item -Path "C:\Temp\$($SessionID).tmp" -ErrorAction Stop
} catch {
$sDB = "C:\Temp\$($SessionID).tmp"
$sQuery = "Create Table ROI (ProcessName TEXT, ProcessDescription TEXT, Frequency TEXT, Time TEXT, TimeType TEXT, FTECount TEXT, Salary TEXT, DataSource1 TEXT, `
DataSource2 TEXT, DataSource3 TEXT, DataSource4 TEXT, ProcessSteps TEXT, MFA TEXT, MFAType TEXT, ErrorLevel TEXT, Criticality TEXT, HoursPerYear TEXT, DollarsPerYear TEXT)"
try {
Invoke-SQLiteQuery -Query $sQuery -Datasource $sDB -ErrorAction Stop
While (!(Get-Item -Path $sDB)) {
Start-Sleep -Milliseconds 50
}
} catch {
Show-UDModal -Content {
New-UDTypography -Text "Unable to create temp file"
}
}
}
On the server I can confirm the file is created. If I do an invoke-sqlitequery output to gridview I can confirm all the information I have entered is in the database.
On page 2, however, even though my logic is the same, I am getting the error above. My try/catch block for the existence of the file is not erroring out, so not sure what is causing this. I thought perhaps the $SessionID was not persisting for some reason, but even when I hard-code the file path I get the same error.
$sDB = "C:\Temp\$($SessionID).tmp"
try {
Get-Item -Path "$($sDB)" -ErrorAction Stop
$sData = (Invoke-SQLiteQuery -Query "Select * From ROI" -DataSource "C:\Temp\$($SessionID).tmp") | % {
$splitCount = (($_.ProcessDescription).Split("`n")).Count
$sDesc = ""
$dSource = $_.DataSource1
if (!($_.MFA -eq "Yes")) {$type = "null"} else {$type = $_.MFAType}
foreach ($a in 0..($splitCount - 1)) {
$sDesc += "$(($_.ProcessDescription).Split("`n")[$a])`r`n"
}
$sDesc += ($_.ProcessDescription).Split("`n")[$splitCount]
foreach ($b in @($_.DataSource2, $_.DataSource3, $_.DataSource4)) {
if (!($b -eq "Select")) {
$dSource += "`r`n$($b)"
}
}
switch($_.ErrorLevel) {
{$_ -match "Low"} {$err = "Low"}
{$_ -match "Medium"} {$err = "Medium"}
{$_ -match "High"} {$err = "High"}
}
@{
ProcessName = $_.ProcessName
ProcessDescription = $sDesc
Frequency = $_.Frequency
ProcessTime = "$($_.Time) $($_.TimeType)"
FTECount = $_.FTECount
FTESalary = $_.Salary
DataSource1 = $dSource
StepCount = $_.ProcessSteps
MFA = $_.MFA
MFAType = $type
ErrorLevel = $err
Criticality = $_.Criticality
HoursPerYear = $_.HoursPerYear
DollarsPerYear = $_.DollarsPerYear
}
}
New-UDTable -Id "table" -Columns $Columns -Data $sData
New-UDButton -Id "btnClose" -Text "Close" -OnClick {
Get-ChildItem -Path "C:\Temp" -Filter "$($User).*.tmp" |
Remove-Item -Force
Invoke-UDRedirect -Url 'https://dev.jeremiahlogan.com/ROI'
}
} catch {
New-UDTypography -Id "table" -Text "No Data Found" -Align 'center' -Style @{
"font-size" = "48px"
"font-weight" = "bold"
}
}
What makes me scratch my head is if I run all of this in PS directly (subsituting a gridview for the table), it works just fine. So I cannot figure out what would be causing this, and why is seems to be PSU related:
#In PS
$sDB = "C:\Temp\$($SessionID).tmp"
try {
Get-Item -Path "$($sDB)" -ErrorAction Stop
$sData = (Invoke-SQLiteQuery -Query "Select * From ROI" -DataSource "C:\Temp\$($SessionID).tmp") | % {
$splitCount = (($_.ProcessDescription).Split("`n")).Count
$sDesc = ""
$dSource = $_.DataSource1
if (!($_.MFA -eq "Yes")) {$type = "null"} else {$type = $_.MFAType}
foreach ($a in 0..($splitCount - 1)) {
$sDesc += "$(($_.ProcessDescription).Split("`n")[$a])`r`n"
}
$sDesc += ($_.ProcessDescription).Split("`n")[$splitCount]
foreach ($b in @($_.DataSource2, $_.DataSource3, $_.DataSource4)) {
if (!($b -eq "Select")) {
$dSource += "`r`n$($b)"
}
}
switch($_.ErrorLevel) {
{$_ -match "Low"} {$err = "Low"}
{$_ -match "Medium"} {$err = "Medium"}
{$_ -match "High"} {$err = "High"}
}
@{
ProcessName = $_.ProcessName
ProcessDescription = $sDesc
Frequency = $_.Frequency
ProcessTime = "$($_.Time) $($_.TimeType)"
FTECount = $_.FTECount
FTESalary = $_.Salary
DataSource1 = $dSource
StepCount = $_.ProcessSteps
MFA = $_.MFA
MFAType = $type
ErrorLevel = $err
Criticality = $_.Criticality
HoursPerYear = $_.HoursPerYear
DollarsPerYear = $_.DollarsPerYear
}
}
New-UDTable -Id "table" -Columns $Columns -Data $sData
New-UDButton -Id "btnClose" -Text "Close" -OnClick {
Get-ChildItem -Path "C:\Temp" -Filter "$($User).*.tmp" |
Remove-Item -Force
Invoke-UDRedirect -Url 'https://dev.jeremiahlogan.com/ROI'
}
} catch {
New-UDTypography -Id "table" -Text "No Data Found" -Align 'center' -Style @{
"font-size" = "48px"
"font-weight" = "bold"
}
}
Product: PowerShell Universal
Version: 3.9.21