[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Can't edit number in DataGrid with format C or N

I have a column in a DataGrid (VB 2003) which has a format of N or C (N displays the number with commas as thousand separators; C, currency, shows the thousands separator plus a $ at the front--using US culture, of course). I have discovered that I cannot edit a number unless the punctuation (commas and dollar sign) are deleted; you can wipe out the current number and put in something new, but you can't, say, delete a 0 to turn 1,500 into 150. Doing edits of a number is fine, though, if the punctuation is gone, either because the number is less than 1000 (and not using C format) or because I've deleted the punctuation. If I leave the comma in, no error is displayed, but when I tab to the next field, the cell reverts to its value before editing.

Does anyone know of a workaround for this? I don't want to eliminate the commas, since the numbers can get into the millions at times, and reading numbers that large without commas is tedious and prone to error. This doesn't happen for ordinary textboxes.
0
ElrondCT
Asked:
ElrondCT
  • 9
  • 6
1 Solution
 
SanclerCommented:
You don't say how you are achieving the formatting at the moment.

But for _complete_ control, it might be worth you looking at this

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q318581

Although the specific issue it is dealing with is different, the code illustrates how _you_ can take charge of what happens in the editing of formatted values.  All you have to do is code for all the eventualities that a user might produce ;-)

Roger
0
 
ElrondCTAuthor Commented:
Current formatting is using a DataGridColumnStyle, defined in the designer inside the DataGridTableStyle, that has a Format property of N (or C).

The description you link to seems within the range of my abilities, though it would certainly take some time to be confident I've got everything working properly (and I was hoping I had my application ready to release). But I don't understand why the built-in system won't read data in the same format it created.
0
 
SanclerCommented:
>>
But I don't understand why the built-in system won't read data in the same format it created.
<<

I don't KNOW that, but my guess is as follows.  

Formatting is the process of presenting a value in a specific way as a String.  Parsing is the reverse process: that is, converting a string to its equivalent value.  It is possible to format (in this sense) many datatypes, and to parse them back again into those datatypes.  But both formatting and parsing will only work if there is (either in the string representation or the datatype variable) a value that is valid for the datatytpe concerned.

The datatypes you are dealing with here are (I think) decimal and integer of some sort.  The values are, for example, 2.7703 and 1934682.  The C format puts the former as $2.77.  That is not a valid _decimal_ datatype value.  So when the _value_ is to be altered the formatting is stripped off.  The stripping off of the "$" is a form of parsing, but it is not a parsing method of the _decimal_ datatype.  But the point is that it produces a value - 2.77 or 2.7703 - which, even whilst it is being altered, is (although a represented in the grid cell by a string) always parsable back to a valid _decimal_ datatype value by that datatype's own parsing methods.

But Integers with "," as thousand separators won't always do that.  The string "1,934,682" is (with the appropriate cultural etc settings) parsable to an integer.  But ""1,934,68" isn't.  The rules for such parsing are that the commas must come after every third digit counting from the right, and that breaks those rules.

Now, as I say, I don't KNOW how that actually translates into the phenomenon you are seeing.  But it looks to me as though it could provide the basis of a rational explanation.

Roger
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ElrondCTAuthor Commented:
I'm actually most concerned with using an N format, which puts the commas in. What I'm finding is that if the current value is "1,500.00" and I put a "2" in front of it, so the display is "21,500.00", it will not be accepted. The implication is that the parser doesn't accept any commas, even if they're in the right places. (And if I were designing my own parser, as I may have to, I'd simply strip out all the commas.)

What I find strangest about the situation is that an ordinary textbox has no problem with commas. If I have "1,500.00" in an ordinary textbox, I can put a "2" at the beginning of the value, or in the middle ("1,5200.00"), and it will parse with no problems, moving the comma to the correct position between every set of three numbers.

However, your discussion of datatypes may point us to an explanation. My ordinary textboxes are not databound, but the DataGrid is, so it has to do an internal conversion to a numeric datatype, rather than leaving the result in a string form. That suggests that a possible solution would be to make the underlying fields for the DataGrid text fields rather than numeric. But that's going to mean a lot of reworking of code--either I have to change all my references to the database fields from numeric to string (with conversions) throughout the program, or I have to disconnect the DataGrid from the database and load and unload data when I display and save the record (two tables, a parent with all the other fields which I'm currently loading and unloading, and the second table that is displayed in the DataGrid with potentially multiple records per parent record). The latter seems like less work (the changes are confined to a smaller portion of code), but I've never worked with a DataGrid that is independent of a database.

Or I could simply eliminate the commas in the display. But when many of the values entered are in the tens and hundreds of thousands, that's not particularly friendly to my users.
0
 
SanclerCommented:
>>
What I find strangest about the situation is that an ordinary textbox has no problem with commas. If I have "1,500.00" in an ordinary textbox, I can put a "2" at the beginning of the value, or in the middle ("1,5200.00"), and it will parse with no problems, moving the comma to the correct position between every set of three numbers.
<<

But the ordinary textbox is never (so far as I know) dealing with a numerical value specifically parsed with the "N" format.  It is always "A STRING".  That it may be possible to convert that string into a numerical datatype with CType or DirectCast or parse it with a numerical datatype's .Parse method is not (quite) the same as the situation we have here.  I agree that, when I was hypothesising on the problem in my last post, I hadn't thought of that angle.  And I also agree that, now you have raised it, this looks to be more specifically an internal _bound_ datagrid problem.

But that really gets you no nearer to a working solution, does it? ;-(

I don't have any other thoughts right now but, if I get a chance this evening, I'll experiment and see if I can come up with anything.  I'll let you know either way.  But don't hold your breath.

Roger
0
 
SanclerCommented:
This seems to work in principle, although it will need some work.  It's based on your comment that ordinary textboxes behave better in this respect than the datagrid seems to.

For each datagridcolumn that you want to self-parse declare at form level

    Private WithEvents tb<myColumn> As TextBox
    Private modifyng<myColumn> As Boolean = False

Customise those names by (at least) replacing <myColumn> with something that makes sense in your set up.

Somewhere after the form has been initialised (probably in form_Load) put code to assign the textboxes that handle the editing in the column/s you want to self-parse to the textbox/es you have declared.

        tb<myColumn> = DataGridTextBoxColumn<myColumn>.TextBox

Code a function to self-parse each datatype concerned.  Here's a very simple example for Integer

    Private Function myIntParser(ByVal inText As String) As Integer
        Return CType(inText, Integer)
    End Function

In the TextChanged Event of each textbox put code on these lines

    Private Sub tb<myColumn>_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles tb<myColumn>.TextChanged
        If modifying Then Exit Sub
        modifying = True
        tb<myColumn>.Text = myIntParser(tb<myColumn>.Text).ToString("N")
        modifying = False
    End Sub

In the Leave Event of each textbox put code on these lines

    Private Sub tb<myColumn>_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles tb<myColumn>.Leave
        If modifying Then Exit Sub
        modifying = True
        tb<myColumn>.Text = myIntParser(tb<myColumn>.Text)
        modifying = False
    End Sub

In general terms it works with code just for the Leave event: but that leaves "odd looking" values in the cell whilst it is being edited.

I say "in general terms" because it will need some work in the parser/s.  For instance, although the above approach worked OK with many alterations to big numbers it threw a wobbly if I deleted the first digit of 1,234,567 - telling me, not unexpectedly, that string ",234,567" wouldn't cast to type Integer.  And, as it stands, if the user deletes a digit in the middle of a number the cursor then moves to the beginning of the number.  But those are bells and whistles that I'll leave you to add.  The basic approach seems to be one that, with minimal coding, will tackle the underlying issue.

Roger
0
 
ElrondCTAuthor Commented:
Thanks, Roger. Something like this certainly seems much simpler than other alternatives we were bouncing around, though I don't have it working yet. I'm understanding that this statement

 tb<myColumn> = DataGridTextBoxColumn<myColumn>.TextBox

should be referring (on the right side of the equal sign) to the DataGridColumnStyle for this particular column. When I do that, the TextChanged and Leave subs are firing as I edit the field. However, when I try to leave the field, either by tabbing or by clicking on another box in the grid, the value in the TextBox is reverting to the previous value before Leave gets control.

I think I've resolved the parser issue with

       Return CType(inText.Replace(",", ""), Decimal)

That, of course, is not properly globalized; I'll deal with alternative thousands separators at a later date.

My actual code is as follows:

    Private Function ParseRent(ByVal inText As String) As Decimal
        Return CType(inText.Replace(",", ""), Decimal)
    End Function

    Private Sub tbGrossRent_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles tbGrossRent.TextChanged
        If blnModifyingGrossRent Then Exit Sub
        blnModifyingGrossRent = True
        tbGrossRent.Text = ParseRent(tbGrossRent.Text).ToString("N")    ' Show with commas
        blnModifyingGrossRent = False
    End Sub

    Private Sub tbGrossRent_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles tbGrossRent.Leave
        If blnModifyingGrossRent Then Exit Sub
        blnModifyingGrossRent = True
        tbGrossRent.Text = ParseRent(tbGrossRent.Text).ToString         ' Store without commas
        blnModifyingGrossRent = False
    End Sub

If I remove the argument in the ToString in TextChanged, the number displays with commas only when the field is NOT being edited, and I get no errors. That would be a workable but second-best alternative. But I don't understand why I seem to be getting different results (in terms of the ability to save a number with commas in it) from what you are.
0
 
SanclerCommented:
Any validating code?  I'm just rushing past so, whatever your answer, mine won't be for an hour or so.  But it's the first thought that occurs to me.

Roger
0
 
ElrondCTAuthor Commented:
Yes, I've got a whole bunch of validating code in the ColumnChanging event handler for the DataGrid. However, the handler doesn't seem to fire; I have a breakpoint set at the beginning of the handler which is never stopping the program (unless I get rid of the comma so the number is read properly).
0
 
SanclerCommented:
The difference is between

      CType(inText, Integer)

which is what I originally used, or

      CType(inText.Replace(",", ""), Integer)

which I have now also tried - with OK results - on the one hand and

      CType(inText.Replace(",", ""), Decimal)

on the other.  That fails for me, too.  But

      CType(inText.Replace(",", ""), Double)

seems to be working OK.

So it seems to be something to do specifically with the Decimal datatype: whether of itself, or specifically in conjunction with "N" formatting, I'm not sure.  Can you work round that?

Roger
0
 
SanclerCommented:
>>
whether of itself, or specifically in conjunction with "N" formatting, I'm not sure.
<<

Given your comment that "If I remove the argument in the ToString in TextChanged, the number displays with commas only when the field is NOT being edited, and I get no errors.", it looks like it is "specifically in conjunction with "N" formatting".

Which raises the question of whether it would be worth a try using a custom format rather than "N".

I'm off to bed (UK-time).  I'll check in again in the morning.

Roger
0
 
ElrondCTAuthor Commented:
I should have checked for your message before my own bedtime. I'm up too late, but wanted to give a shot at your latest ideas.

I'm not able to get correct results with Double or Integer output of ParseRent(). It's still repainting the textbox with the original entry as soon as I press tab to move to the next cell, if the input value has a comma in it. I'm not sure why my results differ from yours. I've also tried using "#,##0.00" as the format string, but that's probably identical to the meaning of "N". I'll play with a few more format options tomorrow to try to understand the behavior more, but that really is the format I want. It does seem to me that the issue is with the existence of a comma.

I also tried replacing the

    CType(inText.Replace(",", ""), Double)

with

    Double.Parse(intext)

but it makes no difference. I did note that either way, I need to have a check for a null string, which causes an exception.

I still say it seems extremely odd to me that this behavior is not 1) fixed, or 2) better known. Surely it can't be overly unusual to want to display numbers with thousands separators in a DataGrid. Yet here we are.

Hmm, quick question: Are you testing in VB 2005 or 2003? I'm using 2003, and I wonder if there's been an improvement in this handling in 2005. Not that that will necessarily get me any closer to a solution--being at the very end of this project, I don't want to move to a new platform and delay release for however long it'll take to make the needed tweaks. But it could explain why we're getting different results.
0
 
SanclerCommented:
Two things.

First, it must have been a bit late last night when I was testing ;-(

Although I changed the datatype in my database, and in the parser, I had overlooked that I was using a strongly typed dataset and I didn't regenerate that.  So the results weren't really reliable.

What I did this morning was set up a new table in my test database.  It's Access, but I don't think anything turns on that.  It has four fields

   ID - autonumber, primary key
   theDouble - Double
   theDecimal - Decimal
   theLong - Long Integer (equates to Integer in .NET)

Then, in VB.NET 2003 (which I recalled, from our previous discussions, you are using, so that's what I'd been testing with all along) I dragged a dataadapter on to a new form and configured it to that table, and then used it to generate a dataset.  Then I added a datagrid and configured three columns with "N".  I also added a button, just so that I could try updating to make sure changes got back to the database.  Then I coded in line with our discussions.  My idea was to try to replicate, so far as I could, what I thought your set-up was, but only so far as was necessary to test the principles involved here.  It worked OK for all the datatypes.

But, the second thing, I was moving from cell to cell with the mouse, not with tabbing.  As soon as, in the light of your comment [EMPHASIS added]

>>
It's still repainting the textbox with the original entry as soon as I PRESS TAB to move to the next cell
<<

I tried tabbing, I had the same results as you report.  

The problem here, I think, is the rather convoluted and inaccessible management of keyboard input so far as a datagrid is concerned.  You will notice, I think, that if you are actually editing a cell - in that you have already made at least one change in it - and you then tab "out of it" the movement out of it doesn't occur straight away as it would if you were just "in" the cell, rather than actually "editing" it.  The first tab in those circumstances appears (to me) just to reset the cell for editing purposes: it is the second tab that actually causes the move to the next cell.  In terms of the approach we had been taking the implication seems to be that the system finds (what it regards as) an invalid value in the cell and replaces it with the original value BEFORE our TextBox_Leave code is firing.  And because the tab key press is not caught by any of the "normal" Key_<something> events in this particular scenario (textbox, grid, form with KeyPreview=True) there is no easy workaround.  There may be something by hooking into the windows messaging, but you'd need to ask someone else about that: it's not my forte.

What I have been able to do, however, is use the approach from the link I first posted to overload the .Commit method of the DataGridColumn.  Here's the code from that link with the amendments I made to test it for a Decimal datatype formatted with "N".

Public Class Form2
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'DataGrid1
        '
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point(32, 32)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(416, 200)
        Me.DataGrid1.TabIndex = 0
        '
        'Form2
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(496, 266)
        Me.Controls.Add(Me.DataGrid1)
        Me.Name = "Form2"
        Me.Text = "Form2"
        CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private ds As New DataSet
    Private WithEvents tbdec As TextBox
    Private doingDec As Boolean = False

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '
        ' Generate sample data
        '
        ' Two columns:
        '    Decimal - uses CRTextBoxColumn for column style.
        '    Integer - uses standard DataGridTextBoxColumn for column style.
        '
        Dim dt As New DataTable
        dt.TableName = "TestTable"
        dt.Columns.Add("Amount", GetType(Decimal))
        dt.Columns.Add("ColB", GetType(Integer))
        ds.Tables.Add(dt)
        dt.Rows.Add(New Object() {123456789, 4})
        dt.Rows.Add(New Object() {-3.45, 5})
        dt.Rows.Add(New Object() {4.25, 6})
        dt.Rows.Add(New Object() {-1.33, 7})

        '
        ' Create DataGridTableStyle and DataGridColumnStyle objects
        ' and add them the the DataGrid.
        '
        Dim ts As New DataGridTableStyle, cs As DataGridColumnStyle

        ' Add the custom column style.
        Dim mycol As CRTextBoxColumn = New CRTextBoxColumn
        mycol.Format = "N"
        mycol.FormatInfo = Nothing

        cs = mycol
        cs.Width = 120
        cs.MappingName = "Amount"        ' Map to decimal column.
        cs.HeaderText = "Charge/Payment"

        ts.GridColumnStyles.Add(cs)

        ' Add the standard column style.
        cs = New DataGridTextBoxColumn
        cs.Width = 100
        cs.MappingName = "ColB"          ' Map to integer column.
        cs.HeaderText = "Integer Col"
        ts.GridColumnStyles.Add(cs)

        ts.MappingName = "TestTable"     ' Map table style to TestTable.
        DataGrid1.TableStyles.Add(ts)

        DataGrid1.DataSource = ds
        DataGrid1.DataMember = "TestTable"

        tbdec = mycol.TextBox

    End Sub

    Private Function parseDec(ByVal inText As String) As Decimal
        Return CType(inText.Replace(",", ""), Decimal)
    End Function

    Class CRTextBoxColumn
        Inherits DataGridTextBoxColumn

        'Protected Overrides Function GetColumnValueAtRow(ByVal cm As CurrencyManager, ByVal RowNum As Integer) As Object
        '    '
        '    ' Get data from the underlying record and format for display.
        '    '
        '    Dim oVal As Object = MyBase.GetColumnValueAtRow(cm, RowNum)
        '    If oVal.GetType Is GetType(DBNull) Then
        '        Return ""                         ' String to display for DBNull.
        '    Else
        '        ' CDec on next statement will throw an exception if this
        '        ' column style is bound to a column containing non-numeric data.
        '        Dim Temp As Decimal = CDec(oVal)
        '        Return Temp.ToString("N")             ' positive number
        '        'If Temp >= 0 Then
        '        '    Return Temp.ToString("N")             ' positive number
        '        'Else
        '        '    Return (-Temp).ToString("0.00") & "CR"   ' negative number
        '        'End If
        '    End If
        'End Function

        Protected Overrides Function Commit(ByVal cm As CurrencyManager, ByVal RowNum As Integer) As Boolean
            '
            ' Parse the data and write to underlying record.
            '
            Me.HideEditBox()   ' return focus to the DataGrid control
            Dim box As DataGridTextBox = CType(Me.TextBox, DataGridTextBox), Value As Decimal
            ' Do not write data if not editing.
            If box.IsInEditOrNavigateMode Then Return True
            If TextBox.Text = "" Then   ' in this example, "" maps to DBNull
                SetColumnValueAtRow(cm, RowNum, DBNull.Value)
            Else
                ' Parse the data.
                Try
                    'If TextBox.Text.ToUpper.EndsWith("CR") Then
                    '    Value = -Decimal.Parse(TextBox.Text.Substring(0, TextBox.Text.Length - 2))
                    'Else
                    Value = Decimal.Parse(TextBox.Text)
                    'End If
                Catch
                    Return False    ' Exit on error and display old "good" value.
                End Try
                SetColumnValueAtRow(cm, RowNum, Value)   ' Write new value.
            End If
            Me.EndEdit()   ' Let the DataGrid know that processing is completed.
            Return True    ' success
        End Function

    End Class

    Private Sub tbdec_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles tbdec.TextChanged
        If doingDec Then Exit Sub
        doingDec = True
        tbdec.Text = parseDec(tbdec.Text).ToString("N")
        doingDec = False
    End Sub
End Class

Although, on its face, it looks as though you have to code your own grid columns/styles rather than using those generated in the GUI by the designer, in practice you could do this by copying what the designer had put in the Designer generated code region into the form_load sub and just making minor alterations to that.

Roger
0
 
ElrondCTAuthor Commented:
We're in business! With a little more experimenting, I found that it's not necessary to use the tbdec TextBox, which eliminates the issue of the cursor position being reset to the beginning of the TextBox in the middle of the edit. It also isn't necessary to use cs as a DataGridColumnStyle; I can do all the settings to mycol and add that directly to the ts DataGridTableStyle. I also changed the output of a null string from DBNull to 0. Everything now looks like it's working the way I would expect.

I deeply appreciate the time you've taken on this and the many other questions I've had working through this project. I don't know how I would have completed it without your help. I just have a bit more QA testing I want to do, and hopefully later this week it's going to be ready for release. I'll post a link to the demo in case you're interested in seeing what your efforts have enabled me to do.
0
 
SanclerCommented:
Good, glad it's sorted.  Sorry it was a bit round the houses getting there.

Yes, I would be interested to see the finished product.  Posting a link in this thread would be best.

Roger
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now