Combining two cell values and checking Active Directory.

Ask your Windows PowerShell-related questions, including questions on cmdlet development!
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.
User avatar
falconcurt
Posts: 19
Joined: Wed Apr 20, 2016 9:35 am

Combining two cell values and checking Active Directory.

Post by falconcurt » Wed May 16, 2018 10:55 am

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'
				}
		}

User avatar
jvierra
Posts: 12394
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Combining two cell values and checking Active Directory.

Post by jvierra » Wed May 16, 2018 4:15 pm

What is it that is not happening or what is the error?

User avatar
jvierra
Posts: 12394
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Combining two cell values and checking Active Directory.

Post by jvierra » Wed May 16, 2018 4:25 pm

You code is not doing anything that matches what you are asking. It is only checking the password column and the "Username" column.

User avatar
mxtrinidad
Site Admin
Posts: 153
Joined: Sun Mar 03, 2013 12:42 pm

Re: Combining two cell values and checking Active Directory.

Post by mxtrinidad » Wed May 16, 2018 5:51 pm

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.

:)

User avatar
falconcurt
Posts: 19
Joined: Wed Apr 20, 2016 9:35 am

Re: Combining two cell values and checking Active Directory.

Post by falconcurt » Thu May 17, 2018 6:04 am

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.

User avatar
jvierra
Posts: 12394
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Combining two cell values and checking Active Directory.

Post by jvierra » Thu May 17, 2018 6:09 am

You really don't want to do that. Just mark all of the cells once after you load the grid.

User avatar
mxtrinidad
Site Admin
Posts: 153
Joined: Sun Mar 03, 2013 12:42 pm

Re: Combining two cell values and checking Active Directory.

Post by mxtrinidad » Thu May 17, 2018 7:05 am

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!

User avatar
falconcurt
Posts: 19
Joined: Wed Apr 20, 2016 9:35 am

Re: Combining two cell values and checking Active Directory.

Post by falconcurt » 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?
Attachments
DataGrid.JPG
DataGrid.JPG (19.48 KiB) Viewed 532 times

User avatar
jvierra
Posts: 12394
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Combining two cell values and checking Active Directory.

Post by jvierra » Thu May 17, 2018 7:37 am

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.

User avatar
falconcurt
Posts: 19
Joined: Wed Apr 20, 2016 9:35 am

Re: Combining two cell values and checking Active Directory.

Post by falconcurt » Thu May 17, 2018 11:27 am

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.  
  3.     foreach ($row in $datagridviewResults.Rows)
  4.  
  5.     {
  6.  
  7.         $DataGridArray = $row | Select-Object -ExpandProperty DataBoundItem
  8.  
  9.         $Fullname = $DataGridArray.LastName + ',' + ' ' + $DataGridArray.FirstName
  10.  
  11.         $RowUser = Get-ADUser -filter {cn -eq $Fullname}
  12.  
  13.         If ($RowUser)
  14.  
  15.         {
  16.  
  17.             $row.Cells[0].Style.BackColor = "Red"
  18.  
  19.             $row.Cells[2].Style.BackColor = "Red"
  20.  
  21.         }  
  22.  
  23.     }

Locked