Datagridview Filtering and operators in column name

Ask questions about creating Graphical User Interfaces (GUI) in PowerShell and using WinForms controls.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Locked
ClipperXTP
Posts: 50
Last visit: Fri May 14, 2021 6:52 am

Datagridview Filtering and operators in column name

Post by ClipperXTP »

I have a datagridview that is updated by various datatables in my Windows Forms Application.
The datagridview is then filtered with a filter textbox.

The column names are search terms that are entered in a text box and are strings.

The are added to a pscustomobject which then updates and array which is then converted to datatable.

I works fine in general, but when the datagridview column name has an '=' or comma in the column name, the filter doesn't work. This occurs when the column name is an OU name.

I can replace the commas and '=' signs but wondering if there is another way around this?

thanks in advance.

jvierra
Posts: 14797
Last visit: Sun May 16, 2021 12:59 pm
Answers: 9
Has voted: 3 times
Been upvoted: 9 times

Re: Datagridview Filtering and operators in column name

Post by jvierra »

Place "{}" around the column names.

ClipperXTP
Posts: 50
Last visit: Fri May 14, 2021 6:52 am

Re: Datagridview Filtering and operators in column name

Post by ClipperXTP »

Hi Jvierra. Thanks for your reply. I haven't been able to get your suggestion to work.
My code goes li

$Search = $($textboxSearch.text)

$array += [PSCustomObject]@{
ID = $($ID.toupper())
$($Search) = "Not Member"
}

$DT = ConvertTo-DataTable -InputObject $array

Update-DataGridView -DataGridView $datagridviewResults -Item $DT
As I mentioned, the filter works fine as long as the search term does not have commas or '='.

If I put the curly brackets around the column name in the PSCustomObject, the datagridview displays the variable name i.e. '$Search' as the column name:

$array += [PSCustomObject]@{
ID = $($ID.toupper())
{$($Search)} = "Not Member"
}



If I do the following, the datagridview column displays for example {OU=HQ,OU=USA,DC=MyCompany,DC=COM} as the column name, but still the filter does not work:

$array += [PSCustomObject]@{
ID = $($ID.toupper())
"{$($Search)}" = "Not Member"
}

Many thanks in advance for your advice!

jvierra
Posts: 14797
Last visit: Sun May 16, 2021 12:59 pm
Answers: 9
Has voted: 3 times
Been upvoted: 9 times

Re: Datagridview Filtering and operators in column name

Post by jvierra »

it has to be quoted, Still, I have no idea what you are trying to do. When you use a DataTable just use the DataView search methods.

There is no filter anywhere and why are you using data for a column name?

Also, when you write code then only write what is necessary. You are doing things on superstition learned by copying things from teh Internet withou understanding the syntax or how PowerShell is designed to use its coding design.

Example:
  1. $array += [pscustomobject]@{
  2.     ID = $ID.ToUpper()
  3.     $textboxSearch.Text = 'Not Member'
  4. }

ClipperXTP
Posts: 50
Last visit: Fri May 14, 2021 6:52 am

Re: Datagridview Filtering and operators in column name

Post by ClipperXTP »

Hi - thanks for your reply.

I have a textbox I use to filter the datagridview -

FOREACH ($Column in $datagridview.Columns)
{

$RowFilter += "{0} Like '%{1}%' OR " -f $($Column.headertext), $textboxfilter.text

}

$datagridview.DataSource.DefaultView.RowFilter = $RowFilter

I am using data for a column name as when I have thousands of rows in the datagridview, it is 'easier on the eyes' for my agents to see 'Member' or 'Not Member' or 'Yes' or 'No' in a row as opposed to a 50 character OU string listed thousands of time. They can then filter on say Member and export the filtered list.
It works very nicely for other queries but falls down when there is a '=' sign or comma in the datagridview column header.
I can see that the filter is working when I write-host the contents of the variable as I type in the search box, but the datagridview doesn't update.

Many thanks for your help.

jvierra
Posts: 14797
Last visit: Sun May 16, 2021 12:59 pm
Answers: 9
Has voted: 3 times
Been upvoted: 9 times

Re: Datagridview Filtering and operators in column name

Post by jvierra »

I still cannot understand the issue. Why are you creating a column name with the name of an OU?

You have to understand that it is hard to impossible to understand something when there is so much vague and disconnected bits of information. Remember that we cannot see what you are doing or know what it is you see or are trying to do.

ClipperXTP
Posts: 50
Last visit: Fri May 14, 2021 6:52 am

Re: Datagridview Filtering and operators in column name

Post by ClipperXTP »

Hi
I appreciate the follow up.
My agents do a search against a list of IDs.
They want to see if the IDs are a member of an OU.
They copy the name of the OU into a search box on the form and click a search button.

When they click search, the datagridview returns a list of results.
The column name is the OU.
The rows have ID and say 'Yes' or 'No' depending on the search results.

They DO have the ability to sort the datagridview by column and click and drag to copy say, the 'Yes' records, if that is what they want to do.

However, it is easier to type 'Yes' in my filter box and then they hit a Copy Clipboard button I have created.

This works well for other searches against say application group memberships.

I understand that I could construct the datagridview whereby the rows are populated by the OU being searched and say the column was a generic name like OU, but it just looks cleaner the first way.

The filter box I described previously works if I use the replace method to replace the '=' and commas in the OU name before assigning it to the column name. I am guessing that the commas and '=' signs are being treated as operators and not text, I just can't see a way to escape out of them.

Many thanks

jvierra
Posts: 14797
Last visit: Sun May 16, 2021 12:59 pm
Answers: 9
Has voted: 3 times
Been upvoted: 9 times

Re: Datagridview Filtering and operators in column name

Post by jvierra »

Unfortunately that is not what your code says. It says the column name comes from the search box. The column rows are filled with "Not Member".

jvierra
Posts: 14797
Last visit: Sun May 16, 2021 12:59 pm
Answers: 9
Has voted: 3 times
Been upvoted: 9 times

Re: Datagridview Filtering and operators in column name

Post by jvierra »

Here is how to search for a value in a column:
  1. $btnSearch_Click = {
  2.     $rows = $dgvProjects.Rows |
  3.         Where-Object{$_.Cells['OU'].Value = $textBox1.Text}
  4.     $rows | Write-Host
  5. }

ClipperXTP
Posts: 50
Last visit: Fri May 14, 2021 6:52 am

Re: Datagridview Filtering and operators in column name

Post by ClipperXTP »

Many thanks for your help, much appreciated.

Locked