Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sort Datagrid with Money Values

Posted on 2004-10-12
8
Medium Priority
?
325 Views
Last Modified: 2012-05-05
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
Comment
Question by:cafferm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 12294134
Can you add a hiden row in the datagrid with a long / integer data type and sort by that?
0
 

Author Comment

by:cafferm
ID: 12294781
the sort is being done by the datagrid title itself
0
 
LVL 19

Accepted Solution

by:
arif_eqbal earned 2000 total points
ID: 12294908
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:cafferm
ID: 12295010
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
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 12295114
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
 

Author Comment

by:cafferm
ID: 12295224
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
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 12295342
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
 

Author Comment

by:cafferm
ID: 12367055
I couldn't get this to work even with some work around but i got there in the end using the DataGridTextBoxColumn

Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question