VB.NET - DataGridView substring cell for Number(10,2)

Question

I have a DataGridView with a column where the user can insert double. I have to control the cell value before i insert in my database because the table have a number(10,2) datatype for this field.

My current code :

Dim length As Integer = Nothing    
Dim row As Integer = DTG.CurrentCell.RowIndex
Dim column As Integer = DTG.CurrentCell.ColumnIndex()

With DTG(row).Cells(column)
    length = Len(.Value)
    If Not IsNothing(.Value) Then
            If Not IsNumeric(.Value) Then
                    .Value = 0
            End If

            If length > 10 Then
                    .Value = .Value.SubString(0, 10)
                    If .Value.Contains(".") Then
                        .Value = .Value.SubString(0, 9)
                    End If
            End If
    End If
End With

The length method is not appropriate here, because if my cell contains ".", the length increases.

Examples :

1234567891 => length = 10 => insert : 1234567891

123456789.1 => length = 11 => insert : 123456789

In the 2nd case, i need to insert 123456789.1

Can someone advise me ? Thank you

score 1 · Answer 1 · answered Feb 05 '16 at 10:26

1

You can use .Value.IndexOf(".") to get the number of digits before the decimal separator (<= 10).

answered Feb 05 '16 at 10:26

Bioukh

1,888
1
16
27

Is there something similar working with the .value including digits after the decimal separator ? – nbadaud Feb 05 '16 at 10:30
The number of digits after separator : `Len(.Value) - .Value.IndexOf(".") - 1`. Of course, this only works if `.Value.Contains(".")` is true. – Bioukh Feb 05 '16 at 10:33
I think there is a better way to do this, i'll continue to search. – nbadaud Feb 05 '16 at 10:35

score 1 · Answer 2 · edited May 23 '17 at 12:09

1

If I'm not mistaken, a database field with a datatype of number(10,2) means that it can carry a maximum value of 99999999.99, using this you might change your code to look like

Dim dbMaxNum As Decimal = 99999999.99

With DTG(row).Cells(column)
    If Not IsNothing(.Value) Then
        If Not IsNumeric(.Value) Then 
            .Value = 0
        Else
            .Value = Math.Min( _
                        dbMaxNum, _
                        Math.Round(CDec(.Value), 2, MidpointRounding.AwayFromZero))
        End If
    End If
End With

Where we first round .Value to two decimal places and then we choose the minimum between the result and the maximum your database field can hold (99999999.99)

The option MidpointRounding.AwayFromZero is to prevent unexpected rounding results, for example 32.625 rounding to 32.62 (an issue that was brought up in this question).

edited May 23 '17 at 12:09

Community

1
1

answered Feb 05 '16 at 18:30

Mr.Z

542
2
5
18

read number datatype part on [oracle](https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm) documentation. 7,456,123.89 with NUMBER(9,2) is stored as 7456123.89 – nbadaud Feb 05 '16 at 19:31
I'm not seeing that I'm incorrect about anything that I've said, what are you implying? The documentation says that a datatype `NUMBER(10,2)` is capable of holding a max of 10 digits but 2 of them are reserved for the decimal part (which means the _number part_ cannot be over 8 digits long) so the maximum number that can be held is `99999999.99`. In both your examples, you're attempting to insert numbers that are greater than `99999999.99`. – Mr.Z Feb 07 '16 at 21:28

nbadaud · Accepted Answer · 2017-02-15T07:43:11.890

I finally decided to work with the cell value.

If .Value > 99999999.99 Then
    .Value = Convert.ToDouble(.Value.SubString(0, 8))
End If

I changed the style format to "N2", so the user can't write more than 2 decimals :

.ValueType = GetType(Double)
.Style.Format = "N2"

I also found another way to do this, i could format my column like a masked textbox. I'll try this solution later.

EDIT : The previous answer is pretty bad but it had helped me for a while. I found a better way to handle my problem. I created a function that check the number of digits before the decimal, and if the length is higher than 8, its return False :

Public Function numberDigitsBeforeDecimal(ByVal montant As Double) As Boolean
    Dim beforeDigit As String = montant.ToString.Substring(0, montant.ToString.IndexOf("."))

    If beforeDigit.Length > 8 Then
        Return False
    Else
        Return True
    End If
End Function

VB.NET - DataGridView substring cell for Number(10,2)

3 Answers3