Solved

Sort Datagrid with Money Values

Posted on 2004-10-12
8
316 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
  • 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 500 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now