Formating a cell after successful validation

[Kardacian]

I have an app with a DataGridView where various cells can be updated. One column has to be validated to "In Process", "Completed" or "Cancelled". The customer wants the flexibility to enter "completed" , "cancelled" or "in process" (ignoring case).
Post validation, automatically format to proper case "Completed", "Cancelled" or "In Process".

The validation routine can confirm the correct phrase has been entered. Since the e.FormattedValue is read only you cant just change it to proper format. So how would one do that post validation? Is it possible if so how?

Private Sub dgvSourceTarget_CellValidating(sender As Object, e As DataGridViewCellValidatingEventArgs) Handles dgvSourceTarget.CellValidating
        Dim colidx As Integer = e.ColumnIndex
        Dim rowidx As Integer = e.RowIndex
        Select Case colidx
 
            Case 7 'Status Column
                If ToProper(e.FormattedValue) <> "Completed" And
                    ToProper(e.FormattedValue) <> "Canceled" And
                    ToProper(e.FormattedValue) <> "In Process" Then
                    MessageBox.Show("The Status value is incorrect!  It must be 'In Process', 'Completed' or 'Canceled'")
                    e.Cancel = True
                End If

        End Select

    End Sub

Other case statements removed for brevity.

[.paul.]

DataGridView1(colic, rowidx).Value = StrConv.ToProperCase(DataGridView1(colic, rowidx).Value.ToString)

Actually, this is how to use StrConv...

StrConv(str, vbProperCase)

[Kardacian]

DataGridView1(colic, rowidx).Value = StrConv.ToProperCase(DataGridView1(colic, rowidx).Value.ToString)

Actually, this is how to use StrConv...

StrConv(str, vbProperCase)

I tried that but

DataGridView1(colidx, rowidx).Value

References the original value (before the edit). So the validated value is what remains.

[.paul.]

Try this...

Dim str As String = DataGridView1(colic, rowidx).Value.ToString
DataGridView1(colic, rowidx).Value = ""
DataGridView1(colic, rowidx).Value = StrConv(str, vbProperCase)

[.paul.]

Actually, i see the problem. You're using the wrong event for formatting...

Private Sub DataGridView1_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
    If e.ColumnIndex = 0 Then
        If e.Value IsNot Nothing Then
            e.Value = StrConv(e.Value.ToString, VbStrConv.ProperCase)
        End If
    End If
End Sub

[.paul.]

The CellFormatting event will run before the CellValidating event ...

[Kardacian]

Using the formatting event works, even though it runs before the validation event. In this case order is not an issue.

   Private Sub dgvSourceTarget_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles dgvSourceTarget.CellFormatting
        If e.ColumnIndex = 7 Then
            If e.Value IsNot Nothing Then
                e.Value = StrConv(e.Value.ToString, vbProperCase)
            End If
        End If
    End Sub
 

    Private Sub dgvSourceTarget_CellValidating(sender As Object, e As DataGridViewCellValidatingEventArgs) Handles dgvSourceTarget.CellValidating
        Dim colidx As Integer = e.ColumnIndex
        Dim rowidx As Integer = e.RowIndex
        Select Case colidx
 
            Case 7 'Status Column
                If StrConv(e.Value.ToString, vbProperCase) <> "Completed" And
                    StrConv(e.Value.ToString, vbProperCase)<> "Canceled" And
                    StrConv(e.Value.ToString, vbProperCase) <> "In Process" Then
                    MessageBox.Show("The Status value is incorrect!  It must be 'In Process', 'Completed' or 'Canceled'")
                    e.Cancel = True
                End If
        End Select

[jmcilhinney]

You can use the CellValidated event to process data after a successful validation, e.g.

Imports System.Globalization
Imports System.Runtime.CompilerServices
 
Public Module StringExtensions
 
    <Extension>
    Public Function ToTitle(source As String) As String
        Return CultureInfo.CurrentCulture.TextInfo.ToTitleCase(source)
    End Function
 
End Module

Private Sub DataGridView1_CellValidating(sender As Object, e As DataGridViewCellValidatingEventArgs) Handles DataGridView1.CellValidating
    Dim columnIndex = e.ColumnIndex
 
    If columnIndex = 0 Then
        Dim validValues = {"In Process", "Completed", "Cancelled"}
        Dim cellValue = CStr(e.FormattedValue)
 
        If Not validValues.Any(Function(s) String.Equals(cellValue, s, StringComparison.CurrentCultureIgnoreCase)) Then
            MessageBox.Show("The Status value is incorrect!  It must be 'In Process', 'Completed' or 'Canceled'")
            e.Cancel = True
        End If
    End If
End Sub
 
Private Sub DataGridView1_CellValidated(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValidated
    Dim columnIndex = e.ColumnIndex
 
    If columnIndex = 0 Then
        Dim cell = DataGridView1(columnIndex, e.RowIndex)
        Dim cellValue = CStr(cell.Value)
 
        cell.Value = cellValue.ToTitle()
    End If
End Sub

As for actually formatting the input, what's the point? You already have the values with the correct casing written in your code twice so why not just use hard-coded values that already have the correct case?

Private validValues As String() = {"In Process", "Completed", "Cancelled"}
 
 
Private Sub DataGridView1_CellValidating(sender As Object, e As DataGridViewCellValidatingEventArgs) Handles DataGridView1.CellValidating
    Dim columnIndex = e.ColumnIndex
 
    If columnIndex = 0 Then
        Dim cellValue = CStr(e.FormattedValue)
 
        If Not validValues.Any(Function(s) String.Equals(cellValue, s, StringComparison.CurrentCultureIgnoreCase)) Then
            MessageBox.Show("The Status value is incorrect!  It must be 'In Process', 'Completed' or 'Canceled'")
            e.Cancel = True
        End If
    End If
End Sub
 
Private Sub DataGridView1_CellValidated(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellValidated
    Dim columnIndex = e.ColumnIndex
 
    If columnIndex = 0 Then
        Dim cell = DataGridView1(columnIndex, e.RowIndex)
        Dim cellValue = CStr(cell.Value)
 
        cell.Value = validValues.First(Function(s) String.Equals(cellValue, s, StringComparison.CurrentCultureIgnoreCase))
    End If
End Sub

That said, why not just use a combo box column for this and have them select the appropriate value with the appropriate casing? It's less typing for the user and avoids any mistyping too.

[Kardacian]

My code in post 7 gives me the result that I want. The user can type in any case that they want. If they enter the proper phrase the text will get formatted to the proper case and saved to the DB. However I dont like using CellFormatting.

The CellFormatting event fires often more so than I expected. I put a simple counter in and just the act of clicking a column causes it to fire. Which means the code will run even when the value of a cell has not changed. Since other columns will need similar work albeit simple I dont want code to run unnecessarily. ie if the contents haven't changed don't check the formatting.

The other thing I noticed is the CellFormatting event does not file until you complete your edit of the cell. So if the validation fails formatting does not occur which makes sense. But, if you escape out it will fire also.



As jmcilhinney has suggested, using the CellValidated event is a much better choice. The code only runs if the data has changed. Even better the Validated event does not fire if you start to edit the cell and then cancel out by hitting the Escape key (also makes sense). The load time of the grid is noticeably faster when the the CellFormatting event is not used. In my opinion avoid that event if you can or keep the code in it short as possible.

Using the CellValidated will be my go to solution to start.

I was not able to use the cell.Value.ToTitle statement. It repeatedly causes an exception error. I did add the extension code as outlined above. I tried a few different ways and it compiles, but I must be implementing it wrong. so for now I am using the StrConv

cell.Value = cell.Value.ToTitle() 'crashes
cell.Value = StrConv(cell.Value, vbPropperCase) 'works


An even better solution would be to use a combobox and I must admit I did not realize that is an option. Before I can go down that road I have to ensure that change to the layout is acceptable. Plus I have to figure out how to do it.

[jmcilhinney]

I was not able to use the cell.Value.ToTitle statement. It repeatedly causes an exception error. I did add the extension code as outlined above. I tried a few different ways and it compiles, but I must be implementing it wrong. so for now I am using the StrConv

cell.Value = cell.Value.ToTitle() 'crashes
cell.Value = StrConv(cell.Value, vbPropperCase) 'works

A crash is an unhandled exception. You should do some debugging and examine the exception to see why it is being thrown and then you can address it. The fact that cell.Value.ToTitle() compiles suggests that you have Option Strict Off, so you should turn that On straight away. That would force you to cast the Value property, which is type Object, as type String. My suspicion is that you are allowing the cell to remain empty, in which case the Value would be Nothing and that, I think, would throw an ArgumentNullException when calling TextInfo.ToTitleCase.

[Kardacian]

A crash is an unhandled exception. You should do some debugging and examine the exception to see why it is being thrown and then you can address it. The fact that cell.Value.ToTitle() compiles suggests that you have Option Strict Off, so you should turn that On straight away. That would force you to cast the Value property, which is type Object, as type String. My suspicion is that you are allowing the cell to remain empty, in which case the Value would be Nothing and that, I think, would throw an ArgumentNullException when calling TextInfo.ToTitleCase.

The cell can't be null because it only happens when I try to change the cell and it has to pass the validation to get to the statement in question. Plus the db table the cell is tied to is not permitted to be anything but the three values. I will do more debugging I dont like not knowing why something breaks or doesn't produce the expected results.

[Kardacian]

I found it, next time I need to pay closer attention.

cell.Value = ToTitle(cell.Value) ' works
cell.Value = cellValue.ToTitle() ' crashes

[.paul.]

cell.Value is Type Object. JM's ToTitle extension is an extension for Type String...

cell.Value = cell.Value.ToString().ToTitle()

[Kardacian]

cell.Value is Type Object. JM's ToTitle extension is an extension for Type String...

cell.Value = cell.Value.ToString().ToTitle()

Im sure it matters but whats the difference in the way its working for me now versus your suggestion?

[.paul.]

cell.Value = ToTitle(cell.Value) ' works, but you're using an extension method incorrectly, also you're passing an Object in place of a String, so there's an implicit conversion.

Do as jmcilhinney suggested and turn Option Strict on in your VB Defaults, then you'll be forced to fix your typecasting errors...

[Kardacian]

cell.Value = ToTitle(cell.Value) ' works, but you're using an extension method incorrectly, also you're passing an Object in place of a String, so there's an implicit conversion.

Do as jmcilhinney suggested and turn Option Strict on in your VB Defaults, then you'll be forced to fix your typecasting errors...

Your revised statement worked with no other changes needed. I did Turn on Option Strict. only needed to correct two Excel export statements.

[jmcilhinney]

cell.Value is Type Object. JM's ToTitle extension is an extension for Type String...

cell.Value = cell.Value.ToString().ToTitle()

That will certainly work but I'm not a fan of calling ToString on a String. If something is and should already be a String then you should cast it as type String, just like you would cast any other type. Calling ToString implies that you're converting something that is not a String to a String representation. I would tend to use CStr for simplicity but DirectCast is the most correct.