Page 1 of 2

Combining two cell values and checking Active Directory.

Posted: Wed May 16, 2018 10:55 am
by falconcurt
I am trying to combine the first two cells in a row. One is the Firstname and the other is Lastname. I want to check Active Directory and make sure that combination doesn't exists as the CN. If it does, I want to use the CellPaintingEventHandler to turn the backcolor to red. I can check one cell but combing the two and checking i am having difficulty with. I have two other columns that i am checking and they work just fine. Here is what i currently have under the CellPaintingEventHandler.

Code: Select all

if ($datagridviewResults.Columns[$_.ColumnIndex].Name -eq "Password")
		{
			$cell1 = $datagridviewResults.Rows[$_.RowIndex].Cells[$_.ColumnIndex]	
			if (checkPasswordmulti $cell1.EditedFormattedValue)
				{
					$_.CellStyle.BackColor = 'White'
				}
			else
				{
					$_.CellStyle.BackColor = 'Red'
					$buttonImport.Enabled = $false
				}
		}
		
	if ($datagridviewResults.Columns[$_.ColumnIndex].Name -eq "Username")
		{
			$cell2 = $datagridviewResults.Rows[$_.RowIndex].Cells[$_.ColumnIndex]
			$Student = get-aduser -filter "samaccountname -eq '$($cell2.EditedFormattedValue)'" -Properties samaccountname, distinguishedname
		
			if ($Student)
				{
					$_.CellStyle.BackColor = 'Red'
					$buttonImport.Enabled = $false
				}
			else
				{
					$_.CellStyle.BackColor = 'White'
				}
		}

Re: Combining two cell values and checking Active Directory.

Posted: Wed May 16, 2018 4:15 pm
by jvierra
What is it that is not happening or what is the error?

Re: Combining two cell values and checking Active Directory.

Posted: Wed May 16, 2018 4:25 pm
by jvierra
You code is not doing anything that matches what you are asking. It is only checking the password column and the "Username" column.

Re: Combining two cell values and checking Active Directory.

Posted: Wed May 16, 2018 5:51 pm
by mxtrinidad
Don't you think it would be much easier to create the code to match the FirstName+UserName outside the DataGridView? This way you can build the result PSObject to Display the ones with the issues on a separate code, like a custom function.

:)

Re: Combining two cell values and checking Active Directory.

Posted: Thu May 17, 2018 6:04 am
by falconcurt
Ok, so my overall goal is to have the end user have the cell marked red for issues on the spreadsheet. The code i posted are for the username and password columns. I am able to wrap my head around just checking one column for an issue but the problem i am having is combining two of them and marking the cells red if there is an issue. I could create an outside function or just a variable that matches Firstname + Lastname but how would i mark the cells red to notify of the issue. I am just having an issue on how would i go about adding that in the System.Windows.Forms.DataGridViewCellPaintingEventHandler.

Re: Combining two cell values and checking Active Directory.

Posted: Thu May 17, 2018 6:09 am
by jvierra
You really don't want to do that. Just mark all of the cells once after you load the grid.

Re: Combining two cell values and checking Active Directory.

Posted: Thu May 17, 2018 7:05 am
by mxtrinidad
I was trying to give the hint of creating a psobject property to identify the ones with issues.
Then, use that object property object value to paint the cell.

I think it should work!

Re: Combining two cell values and checking Active Directory.

Posted: Thu May 17, 2018 7:11 am
by falconcurt
Which part are you saying no to? So, should i collect all of the rows in the Datagrid and run a foreach loop for each row and pull out the first two cells per row? So in turn that variable will hold the Firstname and Lastname. Then take that variable and check Active Directory?

Re: Combining two cell values and checking Active Directory.

Posted: Thu May 17, 2018 7:37 am
by jvierra
falconcurt wrote: Thu May 17, 2018 7:11 am Which part are you saying no to? So, should i collect all of the rows in the Datagrid and run a foreach loop for each row and pull out the first two cells per row? So in turn that variable will hold the Firstname and Lastname. Then take that variable and check Active Directory?
Yes. Paint is not useful for this kind of thing. Paint is at the cell level and you want to address multiple cells. I is easiest to prep the data first.

Re: Combining two cell values and checking Active Directory.

Posted: Thu May 17, 2018 11:27 am
by falconcurt
That explains why I had issues trying to place it in the Paint Event. I was able to get what i was looking for by using the following code. Maybe this will help others.
  1.     Update-DataGridView -DataGridView $datagridviewResults -Item $ImportStudentUsers -AutoSizeColumns DisplayedCells
  2.     foreach ($row in $datagridviewResults.Rows)
  3.     {
  4.         $DataGridArray = $row | Select-Object -ExpandProperty DataBoundItem
  5.         $Fullname = $DataGridArray.LastName + ',' + ' ' + $DataGridArray.FirstName
  6.         $RowUser = Get-ADUser -filter {cn -eq $Fullname}
  7.         If ($RowUser)
  8.         {
  9.             $row.Cells[0].Style.BackColor = "Red"
  10.             $row.Cells[2].Style.BackColor = "Red"
  11.         }  
  12.     }