Empty columns in New-UDDataGrid (LoadRows from SQL)

PowerShell Universal
1

Hi,
first of all i love the new datagrid, i got so many request from users for better filters.

I tested the SQL Example from here: Data Grid - PowerShell Universal

But some of the columns are empty.

image
image984Ă—396 19.4 KB

Here is the output of that exact query:
image

Nothing special here
image

One of the errors in the console:

Check the render method of `GridRow`. See https://reactjs.org/link/warning-keys for more information.
    at Suspense
    at GridRow (http://ServerName.Domain.com/vendors-node_modules_mui_material_node_modules_mui_base_utils_appendOwnerState_js-node_module-a32481.d791d2947ed64a636078.bundle.js:10548:5)
    at div
    at http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:190:66
    at GridVirtualScrollerRenderZone (http://ServerName.Domain.com/vendors-node_modules_mui_material_node_modules_mui_base_utils_appendOwnerState_js-node_module-a32481.d791d2947ed64a636078.bundle.js:19522:5)
    at div
    at http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:190:66
    at GridVirtualScrollerContent (http://ServerName.Domain.com/vendors-node_modules_mui_material_node_modules_mui_base_utils_appendOwnerState_js-node_module-a32481.d791d2947ed64a636078.bundle.js:19449:5)
    at div
    at http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:190:66
    at GridVirtualScroller (http://ServerName.Domain.com/vendors-node_modules_mui_material_node_modules_mui_base_utils_appendOwnerState_js-node_module-a32481.d791d2947ed64a636078.bundle.js:19383:5)
    at DataGridProVirtualScroller (http://ServerName.Domain.com/vendors-node_modules_mui_material_node_modules_mui_base_utils_appendOwnerState_js-node_module-a32481.d791d2947ed64a636078.bundle.js:5522:99)
    at div
    at AutoSizer (http://ServerName.Domain.com/vendors-node_modules_mui_material_node_modules_mui_base_utils_appendOwnerState_js-node_module-a32481.d791d2947ed64a636078.bundle.js:10009:5)
    at div
    at http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:190:66
    at GridMainContainer (http://ServerName.Domain.com/vendors-node_modules_mui_material_node_modules_mui_base_utils_appendOwnerState_js-node_module-a32481.d791d2947ed64a636078.bundle.js:14687:100)
    at GridBody (http://ServerName.Domain.com/vendors-node_modules_mui_material_node_modules_mui_base_utils_appendOwnerState_js-node_module-a32481.d791d2947ed64a636078.bundle.js:11085:5)
    at ErrorBoundary (http://ServerName.Domain.com/vendors-node_modules_mui_material_node_modules_mui_base_utils_appendOwnerState_js-node_module-a32481.d791d2947ed64a636078.bundle.js:9868:1)
    at GridErrorHandler (http://ServerName.Domain.com/vendors-node_modules_mui_material_node_modules_mui_base_utils_appendOwnerState_js-node_module-a32481.d791d2947ed64a636078.bundle.js:11191:5)
    at div
    at http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:190:66
    at GridRoot (http://ServerName.Domain.com/vendors-node_modules_mui_material_node_modules_mui_base_utils_appendOwnerState_js-node_module-a32481.d791d2947ed64a636078.bundle.js:14845:100)
    at GridContextProvider (http://ServerName.Domain.com/vendors-node_modules_mui_material_node_modules_mui_base_utils_appendOwnerState_js-node_module-a32481.d791d2947ed64a636078.bundle.js:19880:3)
    at DataGridPro
    at div
    at http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:190:66
    at Box (http://ServerName.Domain.com/vendors-node_modules_mui_material_Box_Box_js-node_modules_mui_material_Checkbox_Checkbox_js-n-339f5b.19dd0b3faab0512afe35.bundle.js:1867:72)
    at highOrderComponent (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:51771:55)
    at ErrorBoundary (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:49994:197)
    at Suspense
    at main
    at UDPage (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:47703:23)
    at highOrderComponent (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:51771:55)
    at Route (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:87692:29)
    at Switch (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:87898:29)
    at div
    at Suspense
    at highOrderComponent (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:51771:55)
    at Suspense
    at InnerThemeProvider (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:43026:70)
    at ThemeProvider (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:41364:5)
    at ThemeProvider (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:43046:5)
    at StyledEngineProvider (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:41549:5)
    at n (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:100616:58)
    at http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:100616:1060
    at Dashboard (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:51098:22)
    at Route (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:87692:29)
    at Switch (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:87898:29)
    at div
    at Router (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:87323:30)
    at BrowserRouter (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:86948:35)
    at App (http://ServerName.Domain.com/index.6720b442206cf6641227.bundle.js:49541:197)

The code i used

function Out-UDSQLDataGrid {
    param(
        [Parameter(Mandatory)]
        $Context, 
        [Parameter(Mandatory)]
        [string]$Table, 
        [Parameter(Mandatory)]
        [string]$SqlInstance,
        [Parameter(Mandatory)]
        [string]$Database
    )

    End {
        $SqlFilter = "WHERE "        
        $SqlParameters = @{}
        $Filter = $Context.Filter 
        foreach ($item in $Filter.Items) {
            $Property = $item.columnField
            $Value = $item.Value

            $Parameter = "Param" + $SqlParameters.Count
            
            if ($item.operatorValue -eq 'contains')
            {
                $SqlParameters.Add($Parameter, "%$Value%") | Out-Null
            } 
            else 
            {
                $SqlParameters.Add($Parameter, $Value) | Out-Null            
            }
            
            switch ($item.operatorValue) {
                "contains" { $SqlFilter += "$Property LIKE $Parameter AND " } 
                "equals" { $SqlFilter += "$Property = $Parameter AND " }  
                "isEmpty" { $SqlFilter += "$Property IS NULL "  }
                "isNotEmpty" { $SqlFilter += "$Property IS NOT NULL "  }
            }
        }

        $SQLFilter += " 1 = 1"

        $TotalCount = (Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -Query "SELECT COUNT(*) As Count FROM $Table $SqlFilter" -SqlParameters $SqlParameters).Count 

        $Sort = $Context.Sort.'0'
        if ($Sort)
        {
            $SqlSort = "ORDER BY $($Sort.field) $($Sort.Sort) "
        }
        else 
        {
            $SqlSort = "ORDER BY 1 "
        }

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

        $Query = "SELECT * FROM $Table $SqlFilter $SqlSort $SqlPage"

        Show-UDToast $Query -Duration 50000

        $Rows = Invoke-DbaQuery -SqlInstance $SqlInstance -Database $Database -Query $Query -As PSObject -SqlParameters $SqlParameters
        $Rows | Export-Csv -Path C:\temp\sqldata.csv -Delimiter ';' -NoTypeInformation

        @{
            rows     = [Array]$Rows
            rowCount = $TotalCount
        }
    }    
}

New-UDDataGrid -LoadRows {  
    Out-UDSqlDataGrid -Context $EventData -SqlInstance $SqlInstance  -Database$Database  -Table "test"
} -Columns @(
    @{ field = "name"; Type= 'string'; Width = '200';}
    @{ field = "FQDN"; Type= 'string'; Width = '200';}
    @{ field = "CPU";  Type= 'number'; Width = '200';}
    @{ field = "RAM";  Type= 'number'; Width = '200';}
    @{ field = "State"; Type= 'string';  Width = '200'; render = {
            if ($EventData.State -eq 'poweredOn') {
                New-UDAlert -Severity 'Success' -Text 'poweredOn'
            }

            if ($EventData.State -eq 'poweredOff') {
                New-UDAlert -Severity 'Error' -Text 'poweredOff'
            }
        } 
    }
) -AutoHeight -Pagination


Product: PowerShell Universal
Version: 3.2.8
2

whats in the column definition. I had some pain to make it work,but got there in the end. columns need to be camelcase or lower case depending on your version.

Note that in the example the filter wont work,
“contains” { $SqlFilter += "$Property LIKE $Parameter AND " } should be
“contains” { $SqlFilter += "$Property LIKE $value AND " }
and in the filter text box you need to use single quotes - these could be put in the code above

if you set custom page size this also needs to be reflected in the SQL paging :wink:

3

Thanks @DavidB ,
all lower case did the trick.

New-UDDataGrid -Id $DataGridID -LoadRows {  
    Out-UDSqlDataGrid -Context $EventData -SqlInstance "dedcs2-wbot02\sqlexpress" -Database "fiegeautomation" -Table "test" -DataGridID $DataGridID
} -Columns @(
    @{ field = "name"; Type= 'string'; Width = '200';}
    @{ field = "fqdn"; Type= 'string'; Width = '200';}
    @{ field = "cpu";  Type= 'number'; Width = '200';}
    @{ field = "ram";  Type= 'number'; Width = '200';}
    @{ field = "state"; Type= 'string';  Width = '200'; render = {
            if ($EventData.State -eq 'poweredOn') {
                New-UDAlert -Severity 'Success' -Text 'poweredOn'
            }

            if ($EventData.State -eq 'poweredOff') {
                New-UDAlert -Severity 'Error' -Text 'poweredOff'
            }
        } 
    }
) -AutoHeight -Pagination

As for the filters, this dindn’t work for me

“contains” { $SqlFilter += "$Property LIKE $value AND " }

but this worked

"contains" { $SqlFilter += "$Property LIKE '%$Value%' AND " }
1 Like