Format DataGridView Items from XML File

[andrew229]

Hello everyone. I am using this code to fill a DGV.

    Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim xmlFile As XmlReader
        xmlFile = XmlReader.Create("C:\Data.xml", New XmlReaderSettings())
        Dim ds As New DataSet
        ds.ReadXml(xmlFile)
        DataGridView1.DataSource = ds.Tables(0)
    End Sub

This works and my DGV populates. I'd like to format the data, but have no clue where to begin. I'm also a beginner so please explain if possible.

This is a node in my XML:

-<Transaction> 
<TransactionID>661</TransactionID> 
<TransactionDate>41135</TransactionDate> 
<PayToTheOrderOf>Blah Blah Blah</PayToTheOrderOf>
<TransactionAmount>-450</TransactionAmount> 
</Transaction>

1. How do I format the <TransactionDate> to something like 01/01/2012 ?
2. How do I format the <TransactionAmount> to currency ?
3. How do I ORDER the results by <TransactionDate> in DESCENDING order ?

Thanks!

[kevininstructor]

Best to create a schema (here Google is your friend) for your data and type columns such as TransactionAmount from alpha-numeric (a string) to a Decimal or Double. Then in your DataGridView add a column for the trans amount and set the format. The date requires a bit more work, add a DateTime column to the resulting DataTable then after loading the data loop thru each row and set the new date column using the sample code below. If this works then add another column to the DataGridView for the new date column in the DataTable and set the format. For ordering columns you can set the sort via the DataTable DefaultView Sort property.

If FromOADate does not work you will need to figure out how the date was originally done as. BTW 41135 in the example below returns 8/14/2012

Dim SomeValue As Int32 = 41135
Dim MyDate As Date = DateTime.FromOADate(SomeValue)
MessageBox.Show(MyDate.ToShortDateString)

Take your time, if you get stuck show us what you have done code-wise and exactly what is the issue.