Where clause not working in serverside table

PowerShell Universal
1 
Product: PowerShell Universal
Version: 1.5.13

When I filter by any field in a server side table, I encounter an error in the logs:

[03-21-21 05:01:15 PM] [17:01:15][Invoke-DbaQuery] [<DatabaseServerName] Failed during execution | Incorrect syntax near the keyword 'LIKE'. [03-21-21 05:01:15 PM] [17:01:15][Invoke-DbaQuery] [] Failed during execution | Incorrect syntax near the keyword 'LIKE'. Invalid usage of the option NEXT in the FETCH statement. [03-21-21 05:18:17 PM] An error occurred: Cannot bind argument to parameter 'InputObject' because it is null.

NO results are shown.

I’m following the guide in the following blog post Build Server-Side tables with PowerShell Universal Dashboard (ironmansoftware.com)

New-UDTable -Title 'Change log' -ShowPagination -Dense -LoadData { $TableData = ConvertFrom-Json $Body 
                $OrderBy = $TableData.orderBy.field
                if ($OrderBy -eq $null)
                {
                    $OrderBy = "ChangeDate"
                }
            
                $OrderDirection = $TableData.OrderDirection
                if ($OrderDirection -eq $null)
                {
                    $OrderDirection = 'desc'
                }
            
                $Where = ""
                if ($TableData.Filters) 
                {
                    $Where = "WHERE "
            
                    foreach($filter in $TableData.Filters)
                    {
                        $Where += $filter.column.field + " LIKE '%" + $filter.value + "%' AND "
                    }
            
                    $Where += " 1 = 1"
                }
            
                $PageSize = $TableData.PageSize 
                # Calculate the number of rows to skip
                $Offset = $TableData.Page * $PageSize
                $Count = Invoke-DbaQuery -SqlInstance <DatabaseName> -Database 'PowerShellLogging' -Query "SELECT COUNT(*) as count FROM ChangeLog $Where"
            
                $Data = Invoke-DbaQuery -SqlInstance <DatabaseName> -Database 'PowershellLogging' -Query "SELECT * FROM ChangeLog $Where ORDER BY $orderBy $orderdirection OFFSET $Offset ROWS FETCH NEXT $PageSize ROWS ONLY" | ForEach-Object {
                    @{ 
                        Username = $_.Username 
                        SystemName = $_.SystemName
                        ChangeDate = $_.ChangeDate
                        ChangeMade = $_.ChangeMade
                    }
                } 
                $Data | Out-UDTableData -Page $TableData.page -TotalCount $Count.Count -Properties $TableData.properties 
            } -Columns @(
                New-UDTableColumn -Property 'Username' -Sort -Filter -FilterType select
                New-UDTableColumn -Property 'SystemName' -Sort -Filter -FilterType select
                New-UDTableColumn -Property 'ChangeDate' -Sort -Filter
                New-UDTableColumn -Property 'ChangeMade' -Sort -Filter
            ) -Sort -Filter

DB schema:
Username: = Varchar(50)
SystemName = Varchar(50)
ChangeDate = Datetime
ChangeMade = Text

I’m also having another problem where by it always sorts the table by ChangeDate desc. even though the SQL returns asc. So I know the SQL is correct.

I’m running these queries against SQL 2012.

Can anyone suggest why the filter isn’t working?
Thanks. If it’s trying to apply filters to all fields, perhaps it’s because the datetime column isn’t going to accept “like”. If it is that, is it possible to apply specific filters to each column? So for example, everything would require an exact match except ChangeMade?

2

It appears that you likely will have to update the statement conditions for DateTime fields, as (according to this stackoverflow entry) DateTime does not support the LIKE operator.

I’d say try something like this (I don’t have an active SQL Dashboard setup I can validate code with, hence the pseudocode):

        foreach ($filter in $TableData.Filters) {
            if (<column is not of type datetime>) {
                $Where += $filter.column.field + " LIKE '%" + $filter.value + "%' AND "
            } else {
                $where += "(DATEPART(yy, $($filter.column.field))" + " = (DATEPART(yy, $($filter.value)) AND " 
                $where += "(DATEPART(mm, $($filter.column.field))" + " = (DATEPART(mm, $($filter.value)) AND " 
                $where += "(DATEPART(dd, $($filter.column.field))" + " = (DATEPART(dd, $($filter.value))" 
            }
        }

Also, I’m far from a SQL master, but I think this is closer to what you need for datetime filtering