New-UDDataGrid not providing $EventData.Page

Product: PowerShell Universal
Version: 3.7.9

@adam I seem to be having an issue with DataGrids. Hopefully, I am missing something easy. Anyone’s assistance would be greatly appreciated.

$Context.Page missing after first page.
Zero is fine, but 1 never appears

I am passing an array of hashtables. Each hashtable is a row. I have tried passing a array of hashtables, generic list of hashtables, PSCustomObject, calculated properties, with and without the [Array] cast, forcing and array with a subexpression @(), preventing unrolling with ,. I always get the first page of data but I then click next page and I do not see the second page of data.

On the first page, the page indicator looks like this screenshot:

function Out-UDSQLDataGrid {
    param(
        [Parameter(Mandatory)]
        $Context,

        [Parameter()]
        [string]
        $Table = 'InactiveUser',

        [Parameter(Mandatory)]
        $Connection,

        [Parameter()]
        $AccessReviewID
    )
    end {
        $simpleFilter = @()
        Show-UDToast ('C {0} ' -f (@($Context | ForEach-Object { $_ | Select-Object * | Out-String }) -ne '' -join ', ')) -Duration 10000
        if ($null -ne $Context.Filter.Items -and $Context.Filter.Items.Count -gt 0) {
            $linkOperator = $Context.Filter.linkOperator #The link operator is 'AND' or 'OR'. It will always be one or the other for all properties
            foreach ($item in $Context.Filter.Items) {
                $simpleFilter += [PSCustomObject]@{
                    Property = $item.columnField
                    Value    = $item.Value
                    Operator = $item.operatorValue
                }
            }
        }

        if ($null -ne $simpleFilter -and $simpleFilter.Count -gt 0) {
            $count = 1
            foreach ($filter in $simpleFilter) {
                if ($count -gt 1) {
                    $Where += " $($linkOperator) "
                }

                else {
                    $Where += " WHERE "
                }
                switch ($filter.Operator) {
                    "contains" { $Where += " $($filter.Property) LIKE '%$($filter.Value)%' " }
                    "equals" { $Where += " $($filter.Property) = '$($filter.Value)' " }
                    "startsWith" { $Where += " $($filter.Property) LIKE '$($filter.Value)%' " }
                    "endsWith" { $Where += " $($filter.Property) LIKE '%$($filter.Value)' " }
                    "isAnyOf" {
                        $count = 1
                        foreach ($val in $filter.Value) {
                            if ($count -gt 1) {
                                $list += ", '$val'"
                            }

                            else {
                                $list += "'$val'"
                            }
                            $count += 1
                        }
                        $Where += " $($filter.Property) IN ($($list)) "
                    }
                    "isempty" { $Where += " TRIM ($($filter.Property)) IS NULL " }
                    "isnotempty" { $Where += " TRIM ($($filter.Property)) IS NOT NULL " }
                    "notequals" { $Where += " $($filter.Property) != '$($filter.Value)' " }
                    "notcontains" { $Where += " $($filter.Property) NOT LIKE '%$($filter.Value)%' " }
                }
                $count += 1
            }
        }

        else {
            $Where = $null
        }

        $sort = $Context.Sort.'0'
        if ($sort) {
            $sqlSort = "ORDER BY $($sort.field) $($sort.Sort) "
        }

        else {
            $sqlSort = "ORDER BY UserDisplayName asc"
        }

        $sqlPage = "OFFSET $($Context.Page * $Context.PageSize) ROWS FETCH NEXT $($Context.PageSize) ROWS ONLY"


        $Query = @"
SELECT Users.accountEnabled, InactiveUser.UserId, InactiveUser.UserDisplayName, InactiveUser.LastSignIn, InactiveUser.RemoveFromApp
FROM InactiveUser
JOIN Review ON InactiveUser.AccessReviewID = Review.AccessReviewID
JOIN Users ON Users.Id = InactiveUser.UserID
"@

        if ($null -ne $Where) {
            $Where = '{0} {1}.AccessReviewID = {2}' -f $Where, $Table, $AccessReviewId
        }

        else {
            $Where = 'WHERE {0}.AccessReviewID = {1}' -f $Table, $AccessReviewId
        }
        $Query = '{0} {1} {2} {3}' -f $Query, $Where, $sqlSort, $sqlPage

        $totalCount = (Read-GacAFdb -Connection $Connection -Query "SELECT COUNT(*) As Count FROM $Table $Where").Count

        $Rows = [System.Collections.Generic.List[hashtable]]::new()
        $RowList = Read-GacAFdb -Connection $Connection -Query $Query
        foreach ($Item in $RowList) {
            $ThisRow = @{
                'Action'          = if ($Item.RemoveFromApp.value) { 'Remove' } else { 'Keep' }
                'UserDisplayName' = $Item.UserDisplayName.value
                'accountEnabled'  = $Item.accountEnabled.value
                'LastSignIn'      = $Item.LastSignIn.value
                'UserID'          = $Item.UserID.value
            }
            $Rows.Add($ThisRow)
        }
        @{
            rows     = [Array]$Rows
            rowCount = $TotalCount
        }
    }
}
# calling page
New-UDPage -Name 'Application Access Review' -Url '/accessreviewsgrid' -Content {
    # BELOW Verifies that you belong on this page #
    $SESSION:ID = ($ClaimsPrincipal.Claims | Where-Object { $_.Type -eq 'http://schemas.microsoft.com/identity/claims/objectidentifier' }).value
    if (-not $SESSION:ID) {
        Show-UDToast -Message 'Session ID not found.  Please sign in with your Azure AD credentials. Please use incognito mode to sign in if the problem persists' -Persistent -BackgroundColor Red -MessageColor white
        break
    }

    $uri = [uri]$Headers.Referer
    $queryParams = [System.Web.HttpUtility]::ParseQueryString($uri.Query)
    $AccessReviewId = $queryParams['AccessReviewId']

    if (-not $AccessReviewId) {
        Show-UDToast -Message 'To access this dashboard, please use the link emailed to you' -BackgroundColor Red -MessageColor White -Duration 100000
        break
    }

    $Connection = Connect-GacAFdb
    $Connection.open()
    $ReviewQuery = @"
SELECT Groups.GroupDisplayName, Application.AppDisplayName
FROM Review
JOIN Groups ON Review.GroupId = Groups.GroupId
JOIN Application ON Review.AppId = Application.AppId
WHERE Review.AccessReviewID = '$AccessReviewId'
AND Review.ReviewerID = '$SESSION:ID';
"@
    $checksplat = @{
        Query       = $ReviewQuery
        Connection  = $Connection
        ErrorAction = 'Stop'
    }
    try {
        $ReviewTableRow = $null
        if ($ReviewTableRow = Read-GacAFdb @checksplat) {
            New-UDTypography -Align right -Text ('Access review of application {0} for the members of the group {1}' -f $ReviewTableRow.AppDisplayName, $ReviewTableRow.GroupDisplayName) -Variant h5 -FontWeight bold
        }
    }

    catch {
        Write-Host ('[ FAILED READ DB IN ACCESSREVIEWS ] [ SELECT CASE WHEN EXISTS (SELECT 1 FROM Review WHERE AccessReviewID = "{0}" AND ReviewerID = "{1}") THEN "TRUE" ELSE "FALSE" END; ] ERROR [ {2} ]' -f $AccessReviewId, $SESSION:ID, ($Error[0] | Select-Object * | Out-String))
        Show-UDToast ('Failed to read from Database. Stopping. Please notify administrator of AccessReviews page.') -Duration 100000
        break
    }
    if (-not $ReviewTableRow) {
        Show-UDToast -Message 'This Access Review is not assigned to you' -BackgroundColor Blue -MessageColor White -Duration 100000
        break
    }
    # ABOVE Verifies that you belong on this page #
    New-UDDataGrid -LoadRows {
        Out-UDSqlDataGrid -Context $EventData -Connection $Connection -AccessReviewID $AccessReviewId
    } -Columns @(
        @{ field = "Action"; }
        @{ field = "userdisplayname"; }
        @{ field = "accountEnabled"; }
        @{ field = "LastSignIn"; }
        @{ field = "UserID"; }
    ) -RowsPerPageOptions @(10, 25, 50, 100) -PageSize 10 -Pagination
    $Connection.close()
}

Once I click next: OFFSET is empty


Thank you.

Hi,

for me it was often helpful to include Show-UDToast with $Query, $Where and $sqlPage in the function Out-UDSQLDataGrid.
“$($context | convertto-json)” would be possible as well

@Daniel @adam et.al.

Daniel, thanks for the suggestion. I show the toasts here:

1. I open the page and Context.Page = 0. The first 10 rows are properly displayed:

2. I click ">" Next. Notice Nan-Nan [object Object]:

image

3. The next 10 rows are not displayed and Context.Page = NULL. The page loops the same error, toasting the same thing - I eventually see Total Count: 0 and page hangs. I eventually get an open database connection error.

**

Code

**

UDPage: I have taken the calling UDPage down to the bare minimum. It calls the function, Out-UDSQLDataGrid.

The called function, Out-UDSQLDataGrid, is a modified version of https://docs.powershelluniversal.com/userinterfaces/dashboards/components/data-display/data-grid#example-sql-data



Hi,

without knowing the function of ‘$Connection = Connect-GacAFdb’ I would guess that this is the problem.
Before the first construction of the table a ‘$Connection.open()’ happend and after the first representation of the table there comes directly the ‘$Connection.close()’.
If now the change to the next page takes place I would assume that there is no connection anymore. This would explain why the ‘Page.count’ is NULL.
I guess that the connection and disconnection should be part of ‘Out-UDSqlDataGrid’.

Thanks Daniel it is a simple Managed Identity call

function Connect-GacAFdb {
    [cmdletbinding()]
    param (
        [Parameter()]
        $DataSource = '<redacted>.database.windows.net',

        [Parameter()]
        $InitialCatalog = '<redacted>'
    )

    $resourceURI = "https://database.windows.net/"
    $tokenAuthURI = $env:MSI_ENDPOINT + "?resource=$resourceURI&api-version=2017-09-01"
    $tokenResponse = Invoke-RestMethod -Method Get -Headers @{"Secret" = "$env:MSI_SECRET" } -Uri $tokenAuthURI
    $accessToken = $tokenResponse.access_token
    $SqlConnection = [System.Data.SqlClient.SqlConnection]::New()
    $SqlConnection.ConnectionString = "Data Source=$DataSource ; Initial Catalog = $InitialCatalog"
    $SqlConnection.AccessToken = $AccessToken
    $SqlConnection
}

I will try to remove the close now.Thanks @Daniel

@Daniel I completely removed the $Connection.close() and I get the exact same issue:

@Daniel I am curious, what version of PowerShell Universal Dashboard are you running.

the newest. 3.7.10.

That removing the connection.close() command doesn’t help surprises me now… a little bit :wink: it would have kind of worked for me.

But then, i guess, the function ‘Read-GacAFdb’ should cause an error. Since this happens in the function, it can be that this is not to be seen in the Dashboard. There I would now let log all $error.

I rewrote the function ‘Out-UDSqlDataGrid’ for me to SQLServer module instead of DBATools and it worked… but I don’t use the ‘DataGrid’ very often as I miss the export to Excel then.

1 Like

So I did this to try to play with what we are seeing (no page). Also I added open inside loop (and added close back)

Calling function

Called function

Now it rips through every page without me clicking ">" next . Well I do have to click one ">"

i’m a little confused by NaN.

2023-02-11

i do have the rowcount there. right from start.

whats up with the $Totalcount Value and the query?

i hope you find … the typo :wink: or whatever :slight_smile:

I have this working perfectly in UDTable btw :slight_smile:

@Daniel I really appreciate you taking the time to discuss this with me!

Here is a one minute video that shows the paging through all the pages on its own.

@adam any ideas?

here’s my last thought. as i said, i would look at this again. is there really a number coming back?

1 Like

Yes this is the number in the toast

I have hardcoded the number and it works now! @Daniel you are a saint!

I suppose it is being seen as a string? I will investigate further. :slight_smile:

I still do need to see if the paging is working now by removing my bandaid

son of a gun…

image

.count.value

So everything works now.