none
How to do real time input validation in a datagridview control RRS feed

  • Question

  • Team,

    I've been working on a form for standard user account creation that I want to make stringent rules on how they are created. Right now, all I've got is some validation checking when the "Create users" button is clicked so that it generates some pop ups if there are errors, but I'm certain there's a way to handle it once the focus is changed or the user tabs out of the box, but I've tried the "CellValidating" event and the "CellFormatting" event, but nothing seems to happen after it changes focus. Not sure what is going on. Below is the code and I really appreciate the help.

    $buttonCreateUsers_Click = {
    	# Subtracting one from row count so it doesn't try to use the last blank row created during run time operations
    	$rowcount = $datagridviewResults.RowCount - 1
    	
    	for ($i = 0; $i -lt $rowcount; $i++)
    	{
    		$EDIPIRegex = "^\d{10}$"
    		$EnteredEDIPI = $datagridviewResults.Rows[$i].Cells['EDIPI'].Value
    		if ($EnteredEDIPI -notmatch $EDIPIRegex) { [System.Windows.Forms.MessageBox]::Show("EDIPI for $DisplayName must be 10 numeric digits", "Error") }
    		
    		$DisplayName = $datagridviewResults.Rows[$i].Cells['LastName'].Value + ", " + $datagridviewResults.Rows[$i].Cells['FirstName'].Value + " " + $datagridviewResults.Rows[$i].Cells['Rank'].Value + " " + $datagridviewResults.Rows[$i].Cells['Title'].Value + " " + $datagridviewResults.Rows[$i].Cells['Organization'].Value
    		$GivenName = $datagridviewResults.Rows[$i].Cells['FirstName'].Value
    		$Surname = $datagridviewResults.Rows[$i].Cells['LastName'].Value
    		try { [datetime]$AccountExpiration = $datagridviewResults.Rows[$i].Cells['ExpirationDate'].Value }
    		catch { [System.Windows.Forms.MessageBox]::Show($_.Exception.Message + ". Please check Account Expiration Date format for $DisplayName. Try the format m/d/yyyy", "Error") }
    		$UPN = $datagridviewResults.Rows[$i].Cells['EDIPI'].Value + "@mil"
    		$MidInit = $datagridviewResults.Rows[$i].Cells['MI'].Value
    		if ($MidInit -ne $null) { $SamAccountName = "$GivenName.$MidInit.$Surname" }
    		else {$SamAccountName = "$GivenName.$Surname"}
    		$Name = $DisplayName
    		$UserDescription = $datagridviewResults.Rows[$i].Cells['Description'].Value
    		try { New-ADUser -DisplayName $DisplayName -GivenName $GivenName -Surname $Surname -UserPrincipalName $UPN -SamAccountName $SAMAccountName -Name $DisplayName -Path "OU=SYS,OU=CTR,OU=Users,OU=HD_JAMO,OU=JNCC-A,DC=afghan,DC=swa,DC=ds,DC=army,DC=mil" -SmartcardLogonRequired $true -Description $UserDescription }
    		catch { [System.Windows.Forms.MessageBox]::Show($_.Exception.Message + ". Please check that first.m.last does not exceed 20 characters or that '$DisplayName' does not exceed 64 characters'", 'Error')}
    	}

    The code above is for the Create user button Click event. Below is for the CellValidating event, which I thought should be taking place on the "EDIPI" column.

    $datagridviewResults_CellValidating=[System.Windows.Forms.DataGridViewCellValidatingEventHandler]{
    #Event Argument: $_ = [System.Windows.Forms.DataGridViewCellValidatingEventArgs]
    	#TODO: Place custom script here
    	for ($i = 0; $i -lt $rowcount; $i++)
    	{
    		$EDIPIRegex = "^\d{10}$"
    		$EnteredEDIPI = $datagridviewResults.Rows[$i].Cells['EDIPI'].Value
    		if ($EnteredEDIPI -notmatch $EDIPIRegex) { [System.Windows.Forms.MessageBox]::Show("EDIPI must be 10 numeric digits","Error")}
    	}
    }

    I appreciate any help.
    Thursday, February 2, 2017 4:59 AM

Answers

  • Cell Validating occurs when you have entered data into a cell and moved away from the cell. The Cell that is validating is $_.RowIndex and $_.ColumnIndex.

    Here is a simple example of how to validate a cell.

    Add-Type -AssemblyName System.Windows.Forms
    $form1 = New-Object System.Windows.Forms.Form
    $datagridview1 = New-Object System.Windows.Forms.DataGridView
    $Column2 = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
    $Column1 = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
    
    $datagridview1_CellValidating = [System.Windows.Forms.DataGridViewCellValidatingEventHandler]{
    	$cell = $datagridview1.Rows[$_.RowIndex].Cells[$_.ColumnIndex]
    	$cell.ErrorText = ''
    	if ($cell.EditedFormattedValue -ne '123') {
    		$msg = '"123" only'
    		$cell.ErrorText = $msg
    	}
    }
    
    $form1.Controls.Add($datagridview1)
    $form1.ClientSize = '854, 480'
    $form1.FormBorderStyle = 'FixedDialog'
    $form1.StartPosition = 'CenterScreen'
    $form1.Text = 'Form'
    $form1.add_Validating($form1_Validating)
    
    $datagridview1.ColumnHeadersHeightSizeMode = 'AutoSize'
    [void]$datagridview1.Columns.Add($Column1)
    $Column1.HeaderText = 'Column1'
    [void]$datagridview1.Columns.Add($Column2)
    $Column2.HeaderText = 'Column2'
    $datagridview1.Dock = 'Fill'
    $datagridview1.add_CellValidating($datagridview1_CellValidating)
    
    $form1.ShowDialog()
    
    



    \_(ツ)_/


    • Edited by jrv Thursday, February 2, 2017 7:30 AM
    • Marked as answer by Joseph Larrew Friday, February 3, 2017 9:22 AM
    Thursday, February 2, 2017 7:26 AM

All replies

  • Cell Validating occurs when you have entered data into a cell and moved away from the cell. The Cell that is validating is $_.RowIndex and $_.ColumnIndex.

    Here is a simple example of how to validate a cell.

    Add-Type -AssemblyName System.Windows.Forms
    $form1 = New-Object System.Windows.Forms.Form
    $datagridview1 = New-Object System.Windows.Forms.DataGridView
    $Column2 = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
    $Column1 = New-Object System.Windows.Forms.DataGridViewTextBoxColumn
    
    $datagridview1_CellValidating = [System.Windows.Forms.DataGridViewCellValidatingEventHandler]{
    	$cell = $datagridview1.Rows[$_.RowIndex].Cells[$_.ColumnIndex]
    	$cell.ErrorText = ''
    	if ($cell.EditedFormattedValue -ne '123') {
    		$msg = '"123" only'
    		$cell.ErrorText = $msg
    	}
    }
    
    $form1.Controls.Add($datagridview1)
    $form1.ClientSize = '854, 480'
    $form1.FormBorderStyle = 'FixedDialog'
    $form1.StartPosition = 'CenterScreen'
    $form1.Text = 'Form'
    $form1.add_Validating($form1_Validating)
    
    $datagridview1.ColumnHeadersHeightSizeMode = 'AutoSize'
    [void]$datagridview1.Columns.Add($Column1)
    $Column1.HeaderText = 'Column1'
    [void]$datagridview1.Columns.Add($Column2)
    $Column2.HeaderText = 'Column2'
    $datagridview1.Dock = 'Fill'
    $datagridview1.add_CellValidating($datagridview1_CellValidating)
    
    $form1.ShowDialog()
    
    



    \_(ツ)_/


    • Edited by jrv Thursday, February 2, 2017 7:30 AM
    • Marked as answer by Joseph Larrew Friday, February 3, 2017 9:22 AM
    Thursday, February 2, 2017 7:26 AM
  • Hey jrv, thanks for the response. This looks like it would do that validation for every cell. Or am I supposed to replace $datagridview1.Rows[$_.RowIndex].Cells[$_.ColumnIndex] with my column index? In my code for the validating event, I do the for loop to go through each row, or should I just do "$cell = $datagridview1.Rows[$_.CurrentRow].Cells[$_.EDIPI]"

    Also, I see that you add the CellValidating event in the code portion where the form and datagridview controls are defined. I guess that probably gets added automatically when I'm using something like Powershell Studio. I've yet to put your code in, but I will the moment I get a chance. Thanks again.

    Thursday, February 2, 2017 8:09 AM
  • Cell validation is called on every cell changed.  You have to filter for the column of interest.

    switch ($cell.OwningColumn.Name) {
    	'EDIPI'   { <#column validation code#> }
    	'Other1' { <#column validation code#> }
    	'Other2' { <#column validation code#> }
    }
    Be sure your columns have names.


    \_(ツ)_/



    • Edited by jrv Thursday, February 2, 2017 8:24 AM
    Thursday, February 2, 2017 8:23 AM
  • Man, thanks for the help. This is has been largely very helpful! There's still one issue though. When I first exit the column, even after entering a valid value, it doesn't seem like it stores it in the variable. I used the following code to validate that statement. If I don't enter an acceptable value, I have it give a dialog box that outputs the value of the variable. Well, I enter an acceptable value and when I move out of the cell, it gives me a blank dialog box, which leads me to believe it isn't capturing the value. If I move back into that cell and then back out, it does not generate any dialog box. See my code below:

    $datagridviewResults_CellValidating=[System.Windows.Forms.DataGridViewCellValidatingEventHandler]{
    #Event Argument: $_ = [System.Windows.Forms.DataGridViewCellValidatingEventArgs]
    	$cell = $datagridviewResults.CurrentCell
    	switch ($cell.OwningColumn.Name)
    	{
    		'EDIPI' {
    			$EDIPIRegex = "^\d{10}$"
    			$EnteredEDIPI = $cell.Value
    			if ($EnteredEDIPI -notmatch $EDIPIRegex) { [System.Windows.Forms.MessageBox]::Show("$EnteredEDIPI") }
    			
    		}
    		'MI' {
    			$MIRegex = '^\w{1}([.])?$|^NMI$|\0'
    			$EnteredMI = $cell.Value.ToString().ToUpper()
    			if ($EnteredMI -notmatch $MIRegex) { [System.Windows.Forms.MessageBox]::Show("Middle initial must be only one letter (period is optional) or be left blank", "Error") }
    		}
    		
    	} #end switch ($cell.OwningColumn.Name)
    }


    Note: I should also mention that leaving it blank/null doesn't really seem to work. So as to say, if I just tab through it without entering a space or a letter and then deleting it, it still generates the dialog box, even if my regex accounts for a null entry. But that could be something I don't understand about how values are stored in a cell if just tabbed through.
    • Edited by Joseph Larrew Friday, February 3, 2017 7:33 AM Additional clarification
    Friday, February 3, 2017 7:29 AM
  • Run the code I posted and look carefully at how it behave then look at the code very carefully to see how I did it.

    I cannot be more obvious or explicit than that.


    \_(ツ)_/

    Friday, February 3, 2017 7:54 AM
  • I see that I missed the "$cell.EdittedFormattedValue" part and putting that (plus the part about "$cell.ErrorText") in there did do exactly like I wanted. I really appreciate the help. Now I just have to figure out how to get it to accept no entry in middle initial column as well as either a single letter or a single letter with a period. For now, I'll post the entire code that works like I intended. Thanks again!

    $datagridviewResults_CellValidating=[System.Windows.Forms.DataGridViewCellValidatingEventHandler]{
    #Event Argument: $_ = [System.Windows.Forms.DataGridViewCellValidatingEventArgs]
    	$cell = $datagridviewResults.CurrentCell
    	$cell.ErrorText = ''
    	switch ($cell.OwningColumn.Name)
    	{
    		'EDIPI' {
    			$EDIPIRegex = "^\d{10}$"
    			if ($cell.EditedFormattedValue -notmatch $EDIPIRegex)
    			{
    				$msg = "EDIPI must be 10 digits exactly"
    				$cell.ErrorText = $msg
    			}
    			
    		}
    		'MI' {
    			$MIRegex = '^\w{1}([.])?$|\0'
    			if ($cell.EditedFormattedValue -notmatch $MIRegex -or $cell.EditedFormattedValue -eq "")
    			{
    				$msg = "Middle initial must be only one letter (period is optional) or be left blank"
    				$cell.ErrorText = $msg
    			}
    		}
    		
    	} #end switch ($cell.OwningColumn.Name)
    }

    Friday, February 3, 2017 9:21 AM
  • D:\scripts> 'D' -notmatch '[a-z]\.{0,1}'
    False
    D:\scripts> 'D.' -notmatch '[a-z]\.{0,1}'
    False

    \_(ツ)_/

    Friday, February 3, 2017 9:49 AM
  • Oh, I guess I should have specified more. It's already working for single letter with or without a period. I just need it to accept a null or blank entry. Usually, the regex special character '\0', but that's not working either. I've tried \s for any kind of white space and that didn't work either. The expression that works in Powershell but doesn't seem to be working in my CellValidating event is below.

    '^\w{1}([.])?$|\0'

    • Edited by Joseph Larrew Friday, February 3, 2017 10:17 AM Added regex
    Friday, February 3, 2017 10:12 AM
  • '' -notmatch '[a-z]{0,1}\.{0,1}'

    I would actually skip the dot and just accept the letter.  Add the dot to all.  Generally we only store the single letter and add the formatted initial's dot on reports and other things at display time.  There are even specific format options that add the dot in output.  We do the same things with telephone numbers and store them as just a string of numeric characters that are formatted based on regional settings.

    Example:

    US number stored is "99999999999' and displayed is "+1(999)999-9999".  "+1" in other regions become "10-999-999-9999"


    \_(ツ)_/




    • Edited by jrv Friday, February 3, 2017 10:33 AM
    Friday, February 3, 2017 10:26 AM
  • As odd as it is, it seems your regex is supposedly matching everything I put in there.
    Friday, February 3, 2017 10:51 AM
  • Because it has to be further restricted.

    'a.' -cnotmatch '^[A-Z]{1,1}\.{0,1}$'


    \_(ツ)_/

    Friday, February 3, 2017 10:59 AM
  • Yu can also set MaxInputLength to 1 or 2 to further restrict the entry.  Also return $_.Cancel = $true forces the user to stay in the column until it validates but the red error flag will not show when this is done.


    \_(ツ)_/

    Friday, February 3, 2017 11:03 AM
  • Right, of course. forgot to add the ^$ when I used yours. So that works for using a single letter with or without the period, but it still doesn't let it work for a null entry.
    Friday, February 3, 2017 11:13 AM
  • A textbox can never have a null entry.  It will always be an empty string.

    D:\scripts> '' -cnotmatch '^[A-Z]{1,1}\.{0,1}$'
    True

    Anything that has "type" string.  Will always be "" when assigned null.

    D:\scripts> [string]$s=$null
    D:\scripts> $s
    
    D:\scripts> $s.Length
    0
    D:\scripts> $s  -cnotmatch '^[A-Z]{1,1}\.{0,1}$'
    True
    D:\scripts>

    And in a match:

    D:\scripts> $null  -cnotmatch '^[A-Z]{1,1}\.{0,1}$'
    True

    A $null is coerced into type string.

    If you don't care about case then remove the "c".


    \_(ツ)_/


    • Edited by jrv Friday, February 3, 2017 12:25 PM
    Friday, February 3, 2017 12:24 PM
  • That would mean it doesn't match, but I want it to match... Ooohhh... I think I figured it out, but I'm not at my desk now. I'll have to try it tomorrow. I think it would be something like this:

    $cell.EdittedFormattedValue -notmatch '^[A-Z]{1,1}\.{0,1}$' -or $cell.EdittedFormattedValue -ne ''


    I had $cell.EdittedFormattedValue -eq '' instead of $cell.EdittedFormattedValue -ne '' in my code above and never tried '-ne'. Hmmm. Interesting.
    • Edited by Joseph Larrew Friday, February 3, 2017 1:28 PM Additional clarification
    Friday, February 3, 2017 1:26 PM
  • You don't need to check for the empty string.  The RegEx does that because it requires at least on character in the first position.

    $cell.EdittedFormattedValue -notmatch '^[A-Z]{1,1}\.{0,1}$'


    \_(ツ)_/

    Friday, February 3, 2017 1:34 PM
  • But I want them to be able to do one of three options:

    1. One letter

    2. One letter with a period

    3. Leave it blank

    No other options than that though.

    Friday, February 3, 2017 1:36 PM
  • That is not what I understood you to say.

    '' -notmatch '^[A-Z\s]{1,1}\.{0,1}$'

    does it.


    \_(ツ)_/

    Friday, February 3, 2017 1:43 PM
  • Not many people stick around this long in a forum. I really appreciate it. I will give that a go tomorrow. Thanks again.
    Friday, February 3, 2017 1:45 PM
  • Okay, so I finally got the regex. It works when you put 0,1 in the range of characters allowed. Putting the \s did not work. So the regex that worked is below.

    ^[A-Z]{0,1}\.{0,1}$

    This allows for my three options of 1)Single letter, 2) Single letter with a period, or 3) Leave it blank. Thanks again for your help, jrv!

    Saturday, February 4, 2017 5:14 AM