Error while editing Currency Column after using FillWithData

Welcome Guest! To enable all features please Login or Register.

abraXus
Advanced Member Topic Starter

2020-08-16T11:45:10Z

I want to use FillWithData to populate the IGrid from a dataset bound to an Access database.
I am getting an error while editing a column that is formatted as currency: {0:C2} if the dollar sign is included in the edit. In other words, it will accept 5.21 but will not accept $5.21 as a valid input, even if I just put it in edit mode, not change anything, and then try to move to another cell.

If it matters, the field is defined as a Currency type in Access, and the field that was automatically created in the dataset using Visual Studio looks like this:

My code example shows the way I want to do it, but it gives me the error, and in the second part shows an alternative way that does not give me the error, but the second way would mean a lot of extra code to add the data for each column.

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Me.TblGlassTableAdapter.Fill(Me._JobEstimateData2_0DataSet.tblGlass)

        ' gives error when editing that column if it has a dollar sign in it
        IGrid1.FillWithData(Me._JobEstimateData2_0DataSet.tblGlass)
        For myRowIndex As Integer = 0 To IGrid1.Rows.Count - 1
            IGrid1.Rows(myRowIndex).Tag = Me._JobEstimateData2_0DataSet.tblGlass.Rows(myRowIndex)
        Next
        IGrid1.Cols("ShopPrice").CellStyle.FormatString = "{0:C2}"

        ' or

        ' does not give error while editing
        For Each col As DataColumn In Me._JobEstimateData2_0DataSet.tblGlass.Columns
            Dim newcol As iGCol = IGrid1.Cols.Add()
            newcol.Key = col.ColumnName
        Next
        For Each dr As DataRow In Me._JobEstimateData2_0DataSet.tblGlass
            Dim newRow As iGRow = IGrid1.Rows.Add
            newRow.Cells("GlassID").Value = dr("GlassID")
            newRow.Cells("ShopPrice").Value = dr("ShopPrice")
            ' add more code here for each column manually
        Next
        IGrid1.Cols("ShopPrice").CellStyle.FormatString = "{0:C2}"

    End Sub

Please advise.

Igor/10Tec
Administration

2020-08-17T11:06:22Z

The format string specifies the rule applied to the raw cell value to get its text representation on the screen. To get the cell text, String.Format() with the specified format is applied to the cell value. But it is a one-way street. iGrid knows nothing how to convert the entered text into the corresponding value if there are additional characters as the dollar sign, thousand separators and the like.

What we usually recommend in this case is to remove all those special characters from the edited string in an event handler of the RequestEdit event to provide the user with a string that can be converted back to the value with standard .NET methods like Parse/TryParse (Decimal.TryParse() in your case).

Another - but a more complex way - is to implement your string converter in an event handler of the BeforeCommitEdit event.

abraXus
Advanced Member Topic Starter

2020-08-17T20:58:46Z

I suppose I can work with that, but that does not explain why the 2nd example works just fine while editing. It allows the dollar sign in the edit.

Here is what i came up with that works for my needs:

Imports System.Globalization

    Friend Sub IG_RequestEdit(ig As iGrid, ByRef e As iGRequestEditEventArgs)
        If ig.Cols(e.ColIndex).CellStyle.FormatString IsNot Nothing AndAlso
           ig.Cols(e.ColIndex).CellStyle.FormatString.Contains("{0:C") AndAlso
           e.Text.Length > 0 Then

            Dim RawValue As Decimal
            Try
                RawValue = CDec(e.Text.Replace(NumberFormatInfo.CurrentInfo.CurrencySymbol, ""))
            Catch ex As Exception
                MsgBox("The value in the current cell is not a valid currency value. If you are seeing this, something is very wrong.", vbInformation)
                RawValue = 0
            End Try
            e.Text = RawValue
        End If
    End Sub

I would still like to know why doing it the other way (without FillWithData) works just fine.

Edited by user 2020-08-18T10:34:16Z | Reason: Not specified

Igor/10Tec
Administration

2020-08-18T06:10:16Z

Yes, indeed, it's an interesting point why iGrid does not display the conversion error message in the second case. To understand what is going on, I need a sample to reproduce this. But perhaps, a faster way to figure this out is to get an answer to the following question from you.

iGrid tries to convert the entered text into the corresponding value of the type of the current cell value. If you enter a string with the dollar sign and it is accepted 'as is', then maybe the cell contains a value not of the Decimal type. Can you tell us what is the type of the cell values in the second case?

*****

As for the problem in general, what would you recommend to do with the cell text formatted with a format string when the user starts editing? For example, wouldn't it be better to activate the cell editor with the cell value converted to string with the common .ToString() method so all special characters like the dollar sign and thousand separators would be removed automatically? Try to analyze the problem for frequently used data types like integer, float, decimal, date and bool.

abraXus
Advanced Member Topic Starter

2020-08-18T10:20:10Z

Here is the requested information for the second case:

? IGrid1.Cells(e.RowIndex,e.ColIndex).Type
NotSet {0}
? IGrid1.Cells(e.RowIndex,e.ColIndex).FormatString
Nothing
? IGrid1.Cols(e.ColIndex).CellStyle.FormatString
"{0:C2}"

I was curious about these same values when run using the first case, and oddly enough, they are exactly the same.

Note: These values were obtained in the immediate window while the program was paused by a Stop statement in the IGrid1.RequestEdit event.

Please let me know if there is any other information you need.

Igor/10Tec
Administration

2020-08-18T15:02:15Z

I meant the type of the VALUE of a cell, i.e. the type of the IGrid1.Cells(e.RowIndex,e.ColIndex).Value expression.

abraXus
Advanced Member Topic Starter

2020-08-18T15:42:19Z

If i am understanding:

? IGrid1.Cells(e.RowIndex,e.ColIndex).Value.GetType

returns: (same in both examples)

{Name = "Decimal" FullName = "System.Decimal"}

Igor/10Tec
Administration

2020-08-19T06:38:26Z

Then I simply can't understand why "$5.21" is accepted only in the second case. I need a sample to launch on my dev pc to reproduce the problem. Can you prepare and send it to us to our support email?

abraXus
Advanced Member Topic Starter

2020-08-19T11:25:57Z

I have sent a full sample project that demonstrates the problem (including an Access database) to the support email address that I have.

Please let me know if there is anything else you need.

Igor/10Tec
Administration

2020-08-20T06:14:20Z

#10

Yes, got it - thank you.

I added the second population method to your sample. However, it does not add rows to the grid. The loop

For Each dr As DataRow In Me.CurrencyTestDataSet.tblGlass
    Dim newRow As iGRow = IGrid1.Rows.Add
    newRow.Cells("GlassID").Value = dr("GlassID")
    newRow.Cells("ShopPrice").Value = dr("ShopPrice")
Next

does nothing as tblGlass.Rows is a collection with 0 rows.

Can you add the second population method to the sample that really adds rows and send it to us again?

abraXus
Advanced Member Topic Starter

2020-08-20T07:27:39Z

#11

It looks like you may have inadvertently removed this line at the top

Me.TblGlassTableAdapter.Fill(Me.CurrencyTestDataSet.tblGlass)

Is it required to fill the dataset.

I'll zip up the changed project and send it to support email.

Edited by user 2020-08-20T07:28:28Z | Reason: Not specified

Igor/10Tec
Administration

2020-08-21T12:10:14Z

#12

I investigated this issue. To explain it briefly, the difference is in how the FillWithData() method creates grid columns for the data table and sets their properties. If you call FillWithData(), IGrid1.Cols(1).CellStyle.ValueType is set to System.Decimal. In the second case it is null (Nothing in VB). Depending on this setting, iGrid uses two different algorithms to get the new cell value from the entered string - Convert.ChangeType() in the first case and Decimal.Parse() in the second case. The first function fails for strings starting with '$'.

Obviously, the conversion algorithm must be enhanced for situations like yours, but it's a complex conversion algorithm used as a universal solution for all possible data types and we need to analyze the situation thoroughly before amending the algorithm. At the moment, to solve the problem quickly for the FillWithData-based population, I can recommend calling the following statement after you called FillWithData():

IGrid1.Cols(1).CellStyle.ValueType = Nothing

abraXus
Advanced Member Topic Starter

2020-08-21T12:59:05Z

#13

Thank you. That answers my question perfectly.

I just tried your suggestion for a temporary fix and it seems to also work perfectly for my needs.

I consider this a solution, but as you said, you may want to look into handling this differently in your code in the future, as I'm sure I won't be the only one who runs into this. No need to prioritize this just for me tho. It's working just fine with the extra line of code.

Igor/10Tec
Administration

2020-11-27T15:27:59Z

#14

iGrid.NET v10.0.20 released today supports editing of formatted numeric values out-of-the-box, so this problem is no longer an issue.

Important Information:

The 10Tec Forum uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close