Improve company productivity with a Business Account.Sign Up

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

Sort Datagrid with Money Values

Hi

I currently have a datagrid in my vb.net windows app.  The grid holds monetary values however when the user clicks on the top of the column to sort the value it does actually put it in money order it orders it by the first digit

eg

$100.00
$2.00
$90.00

and obviously this isn't in value order.

Can anyone please sugesst what i could do.  Have have placed some code below to give you an idea of how the grid is built from the XML and how the values are allowed to be changed during runtime.

  Private Sub Datagrider()

    ds1.Tables.Clear()

    Dim DataTable As New Data.DataTable("FileContents")
    Dim l As Integer

    With DataTable.Columns
      .Add("fleid")
      .Add("Template_id")
      .Add("Account Name")
      .Add("Ref No")
      .Add("BSB")
      .Add("Account No.")
      .Add("Amount")
      .Add("Payment Ref")
      .Add("Transaction_type")
      .Add("Transaction_code")
    End With

    Dim newIt As ListViewItem
    Dim nodelist As XmlNodeList = xmlDoc.SelectNodes("/deid/deFile")
    Dim node As XmlNode

    l = -1
    For Each node In nodelist
      If node.SelectSingleNode("Template_id").InnerText = FileId Then '"12" Then '
        l += 1
        With DataTable.Rows
          .Add(New Object() {})
          .Item(l).Item(0) = node.Attributes("fleid").InnerText
          .Item(l).Item(1) = node.SelectSingleNode("Template_id").InnerText
          .Item(l).Item(2) = node.SelectSingleNode("Account_Name").InnerText
          .Item(l).Item(3) = node.SelectSingleNode("RefNumber").InnerText
          .Item(l).Item(4) = node.SelectSingleNode("BSB").InnerText
          .Item(l).Item(5) = node.SelectSingleNode("Account_No").InnerText
          .Item(l).Item(6) = node.SelectSingleNode("Amount").InnerText
          .Item(l).Item(7) = node.SelectSingleNode("PaymentRef").InnerText
          .Item(l).Item(8) = node.SelectSingleNode("Transaction_type").InnerText
          .Item(l).Item(9) = node.SelectSingleNode("Transaction_code").InnerText
        End With
        DataTable.AcceptChanges()
      End If
    Next

    Dim GridStyle As DataGridTableStyle = New DataGridTableStyle
    On Error Resume Next
    DataGrid1.TableStyles.Remove(GridStyle)

tableadd:
    ds1.Tables.Add(DataTable)

    With DataGrid1
      .DataSource = ds1.Tables("FileContents")
      .TableStyles.Add(GridStyle)
    End With
    DataGrid1.Controls(0).Height = 0
    Dim cm As CurrencyManager
    cm = CType(Me.BindingContext(DataGrid1.DataSource, DataGrid1.DataMember), CurrencyManager)
    CType(cm.List, DataView).AllowNew = False
    CType(cm.List, DataView).AllowEdit = True

    With GridStyle
      .MappingName = "FileContents"
      .GridColumnStyles(0).Width = 0
      .GridColumnStyles(1).Width = 0
      .GridColumnStyles(2).Width = 125
      .GridColumnStyles(3).Width = 125
      .GridColumnStyles(4).Width = 75
      .GridColumnStyles(5).Width = 75
      .GridColumnStyles(6).Width = 75
      .GridColumnStyles(7).Width = 125
      .GridColumnStyles(8).Width = 0
      .GridColumnStyles(9).Width = 30
      .GridColumnStyles(2).ReadOnly = True
      .GridColumnStyles(3).ReadOnly = True
      .GridColumnStyles(4).ReadOnly = True
      .GridColumnStyles(5).ReadOnly = True
      .GridColumnStyles(6).ReadOnly = False
      .GridColumnStyles(7).ReadOnly = False
      .GridColumnStyles(9).ReadOnly = True
    End With

    AddHandler ds1.Tables(0).ColumnChanged, New DataColumnChangeEventHandler(AddressOf OnColumnChanged)

  End Sub

  Private Shared Sub OnColumnChanged(ByVal sender As Object, ByVal args As DataColumnChangeEventArgs)

    Dim nodelist As XmlNodeList = xmlDoc.SelectNodes("/deid/deFile")
    Dim node As XmlNode
    Dim j As String, i As Integer

    Try
      If args.Column.ColumnName = "Amount" And flag = False Then
        j = Format(CDbl(args.ProposedValue.ToString()), "c")
        flag = True
        args.ProposedValue = j
        args.Row(args.Column) = j
      End If

      If args.Column.ColumnName = "Payment Ref" And flag = False Then
        i = Len(args.ProposedValue.ToString)
        If i > 18 Then
          MsgBox("The Payment Ref is greater than 18 characters.  Only the first 18 will be used", MsgBoxStyle.Critical + MsgBoxStyle.OKOnly, "Payment Ref")
        End If
      End If

      flag = False
      For Each node In nodelist
        If node.Attributes("fleid").Value = args.Row("fleid").ToString() Then
          node.SelectSingleNode("Amount").InnerText = args.Row("Amount").ToString()
          node.SelectSingleNode("PaymentRef").InnerText = args.Row("Payment Ref").ToString()
        End If
      Next

      If SaveXML(xmlFile) = False Then Exit Sub

    Catch otherexcep As Exception
      MsgBox("This is not a valid number format", MsgBoxStyle.Exclamation + MsgBoxStyle.OKOnly, "Invalid Number")
      flag = True

      If args.Column.ColumnName = "Amount" And flag = True Then
        args.ProposedValue = Format(CDbl("0"), "c")
        args.Row(args.Column) = Format(CDbl("0"), "c")
        flag = False
      End If
    End Try
  End Sub

as you can see i convert the value to a currency as they leave the cell.

thank you in advance

Matt
0
cafferm
Asked:
cafferm
  • 4
  • 3
1 Solution
 
flavoCommented:
Can you add a hiden row in the datagrid with a long / integer data type and sort by that?
0
 
caffermAuthor Commented:
the sort is being done by the datagrid title itself
0
 
arif_eqbalCommented:
Well you need to Format the underlying data
set the Format property of the DataGridTextBoxColumn for that particular column to The Currency format

When the Grid knows that the data being displayed is currency it'll automatically format it even when sorting...
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
caffermAuthor Commented:
I do i use the DataGridTextBoxColumn  is this instance please

am trying to use it but it still doesn't seem to be working
0
 
arif_eqbalCommented:
Use Alignment Property as well

        Dim Col As New DataGridTextBoxColumn
        Col.MappingName = "Price"
        Col.HeaderText = "Price in $"
        Col.Width = 150
        Col.Format = "C"
        Col.Alignment = HorizontalAlignment.Right

        TableStyle1.GridColumnStyles.Add(Col)
        DataGrid1.TableStyles.Add(TableStyle1)
0
 
caffermAuthor Commented:
Make sense i just can't get to work within my code - shown below

    ds1.Tables.Clear()

    Dim DataTable As New Data.DataTable("FileContents")
    Dim l As Integer

    With DataTable.Columns
      .Add("fleid")
      .Add("Template_id")
      .Add("Account Name")
      .Add("Ref No")
      .Add("BSB")
      .Add("Account No.")
      .Add("Amount")
      .Add("Payment Ref")
      .Add("Transaction_type")
      .Add("Transaction_code")
    End With

    Dim newIt As ListViewItem
    Dim nodelist As XmlNodeList = xmlDoc.SelectNodes("/deid/deFile")
    Dim node As XmlNode

    l = -1
    For Each node In nodelist
      If node.SelectSingleNode("Template_id").InnerText = FileId Then '"12" Then '
        l += 1
        With DataTable.Rows
          .Add(New Object() {})
          .Item(l).Item(0) = node.Attributes("fleid").InnerText
          .Item(l).Item(1) = node.SelectSingleNode("Template_id").InnerText
          .Item(l).Item(2) = node.SelectSingleNode("Account_Name").InnerText
          .Item(l).Item(3) = node.SelectSingleNode("RefNumber").InnerText
          .Item(l).Item(4) = node.SelectSingleNode("BSB").InnerText
          .Item(l).Item(5) = node.SelectSingleNode("Account_No").InnerText
          .Item(l).Item(6) = node.SelectSingleNode("Amount").InnerText
          .Item(l).Item(7) = node.SelectSingleNode("PaymentRef").InnerText
          .Item(l).Item(8) = node.SelectSingleNode("Transaction_type").InnerText
          .Item(l).Item(9) = node.SelectSingleNode("Transaction_code").InnerText
        End With
        DataTable.AcceptChanges()
      End If
    Next

    Dim GridStyle As DataGridTableStyle = New DataGridTableStyle
    On Error Resume Next
    DataGrid1.TableStyles.Remove(GridStyle)

tableadd:
    ds1.Tables.Add(DataTable)

    With DataGrid1
      .DataSource = ds1.Tables("FileContents")
      .TableStyles.Add(GridStyle)
    End With


    DataGrid1.Controls(0).Height = 0
    Dim cm As CurrencyManager
    cm = CType(Me.BindingContext(DataGrid1.DataSource, DataGrid1.DataMember), CurrencyManager)
    CType(cm.List, DataView).AllowNew = False
    CType(cm.List, DataView).AllowEdit = True

    With GridStyle
      .MappingName = "FileContents"
      .GridColumnStyles(0).Width = 0
      .GridColumnStyles(1).Width = 0
      .GridColumnStyles(2).Width = 125
      .GridColumnStyles(3).Width = 125
      .GridColumnStyles(4).Width = 75
      .GridColumnStyles(5).Width = 75
      .GridColumnStyles(6).Width = 75
      .GridColumnStyles(7).Width = 125
      .GridColumnStyles(8).Width = 0
      .GridColumnStyles(9).Width = 30
      .GridColumnStyles(2).ReadOnly = True
      .GridColumnStyles(3).ReadOnly = True
      .GridColumnStyles(4).ReadOnly = True
      .GridColumnStyles(5).ReadOnly = True
      .GridColumnStyles(6).ReadOnly = False
      .GridColumnStyles(7).ReadOnly = False
      .GridColumnStyles(9).ReadOnly = True
    End With


any suggestions

thanks
0
 
arif_eqbalCommented:
When you are writing

.GridColumnStyles(0).Width = 0
.GridColumnStyles(1).Width = 0

etc.

For the particular column which has Currency DataType Explicitly create a DataGridTextBoxColumn and Add to the GridStyle collection

Better still you do it for each Column in the grid

Your code will the have (in place of all GridStyle code)



.Add("fleid")
      .Add("Template_id")
      .Add("Account Name")
      .Add("Ref No")
      .Add("BSB")
      .Add("Account No.")
      .Add("Amount")



        Dim Col As New DataGridTextBoxColumn
        Col.MappingName = "fleid"
        Col.HeaderText = "fleid"
        Col.Width = 150
        GridStyle .GridColumnStyles.Add(Col)

       Dim Col As New DataGridTextBoxColumn
        Col.MappingName = "Template_id"
        Col.HeaderText = "Template_id"
        Col.Width = 150
        GridStyle .GridColumnStyles.Add(Col)

..... So on for each column and for currency column it will be like

       Dim Col As New DataGridTextBoxColumn
        Col.MappingName = "Amount"
        Col.HeaderText = "Amount"
        Col.Width = 150
        Col.Format = "C"
        Col.Alignment = HorizontalAlignment.Right
      GridStyle .GridColumnStyles.Add(Col)

Then add the GridStyle to the  DataGrid

        DataGrid1.TableStyles.Add(TableStyle1)
0
 
caffermAuthor Commented:
I couldn't get this to work even with some work around but i got there in the end using the DataGridTextBoxColumn

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now