Sum DataGridView Column Created by Dataset of XML Files Data

[elainec351]

First off let me thank you all for this forum. It has provided answers to so many of my questions. I am very new to VB.net programming and am struggling with some of the simplest issues.

Next, can anyone help me create a query that sums the refundAmount data from a dataset that is derived by pulling data from multiple XML files. The datasource of the data is not from a database but individual XML files that I am reading in. Due to this, I have been unable to create a Query that I can use to sum the data in the refund table(refundAmount column). At least I haven't found a way, yet.

I have created an dataset and datatable in the DataSource Design, though I don't know that this was necessary as I am creating it in my code. The dataset has the same name as the in the DataSource Design and the datatable is also the same. I did this mainly to specify which columns I wanted in the datagrid. Without this I was getting every field of data in the XML files on the DataGridView. I am sure this is not OK, but it is seemingly NOT hurting anything at the moment.

I have tried adding a column in the datagridview that uses an expression to sum the Amount column but is stays empty even though there is data in the Amount column.

I have tried using the datatable.Compute for both the datagridview and the dataset.datatable. This also left the Total Amount empty.
Am I missing the boat or is there a way to get what I need?

Thanks in advance for any help on this.

Elaine

 

[elainec351]

Adding Code

Sorry everyone, I thought it may be prodent to add the code that is populating the dataset and for which I am trying to compute.

Public Sub ReadXml()
        Dim settings As New XmlReaderSettings()
        Dim sXMLFile As String
        Dim dsDataset As DataSet = New DataSet("dsXMLData")
        Dim dtDataTable As DataTable = New DataTable("refund")
        Dim sXMLFileLocation As String = "P:\"
        Dim currDir As String = Directory.GetCurrentDirectory
        Dim exp As String = "IIF(issuingCountry = 'FRA', 'VAT - Global Refund', 'International VAT - Global Refund')"
        Try
            Directory.SetCurrentDirectory(sXMLFileLocation)
        Catch nopath As Exception
            MsgBox("REX XML Files directory not available.  Please make sure USB object is connected.")
            Exit Sub
        End Try
        Try
            Dim dirs As String() = Directory.GetFiles(sXMLFileLocation, "*.xml")
        Catch nopath As Exception
            Me.StatusStrip1.Text = "Ready......"
            MsgBox("No REX refunds to process.")
            Exit Sub
        End Try
        For Each sXMLFile In Directory.GetFiles(sXMLFileLocation, "*.xml")
            Dim lfx As XmlDocument = New XmlDocument
            Try
                lfx.Load(sXMLFile)
                Dim reader As XmlReader = XmlReader.Create(sXMLFile, settings)
                settings.ConformanceLevel = ConformanceLevel.Fragment
                settings.IgnoreWhitespace = True
                settings.IgnoreComments = True
                Dim nodetransact As New XmlNodeReader(lfx)
                If nodetransact.MoveToContent() = XmlNodeType.Element And nodetransact.Name = "transaction" _
                    And nodetransact.GetAttribute("transactionType").Equals("refund") Then
                    dsDataset.ReadXml(sXMLFile)
                    Try
                        dsDataset.Tables("refund").Columns.Add("Product", GetType(String), exp)
                        dsDataset.Tables("refund").Columns("Product").Expression = exp
                        dsDataset.Tables("refund").Columns.Add("Confirm", GetType(Boolean))
                        dsDataset.Tables("refund").Columns.Add("Fee", GetType(Decimal))
                        dsDataset.Tables("refund").Columns.Add("REXRefund", GetType(Boolean))
                    Catch exAddColumn As Exception

                    End Try
                    dgvTaxRefunds.DataSource = dsDataset
                    dgvTaxRefunds.DataMember = "refund"
                End If

            Catch empty As XmlException
            Finally
                ' Finished with XmlDocument
                lfx = Nothing
                Directory.SetCurrentDirectory(currDir)
            End Try
        Next
        'Sets each REX Records checkbox as true, for future use in manual adding new trans or editing records
        For Each dr As DataRow In dsDataset.Tables("refund").Rows
            dr.Item("REXRefund") = 1
            dr.Item("Confirm") = 1
        Next

        ComputeTotalAmount(dsDataset)
        Try
            Me.ProductsTableAdapter.FillByManualRefundCategory(DsGTMProd.Products)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Me.cmbProduct.Text = "DETAXE Tax Free"
        Me.tbAddAmount.Text = ""
        Me.tbAddAmount.Enabled = True
        Me.tbIssueCountry.Text = ""
        Me.tbIssueCountry.Enabled = True
        Me.tbChequeNo.Text = ""
        Me.tbChequeNo.Enabled = True
    End Sub
    Private Sub ComputeTotalAmount(ByVal dataset As DataSet)
        Dim dsDataset As DataSet = dataset
        Try
            For Each dr As DataRow In dt.Rows
                MsgBox(dr.Item("refundAmount").ToString.Contains(1))
            Next

            aTotalRefundAmounttest = dsDataset.Tables("refund").Compute("Sum(refundAmount)", "Confirm = 1")
            If IsDBNull(aTotalRefundAmount) Then tbTotalAmount.Text = 0 Else tbTotalAmount.Text = aTotalRefundAmounttest
            If tbTotalAmount.Text = vbNullString Or Val(tbTotalAmount.Text) = 0 Then
                tbTotalAmount.Text = Format(0, "0.00")
            End If
            aTotalRefundAmount = tbTotalAmount.Text
        Catch exComputeTotalAmount As Exception
            MsgBox(exComputeTotalAmount.Message)
        End Try
    End Sub

 

[elainec351]

Possible solution; another question

OK, so I think I have determined why the datatable.compute was not working properly. The ReadXML is bringng the data into the dataset as a string. Compute (Sum()) only works if the data is numeric(duh), but my new problem is how to get the ReadXML to allow me to set the datatype of the data coming in. I have attempted to GetType (which I think also sets the datatype if done properly) on the column that should be receiving the refundAmount data, but I am getting an exception.

Any help would be really appreciated.

Thanks,

 

[cjard]

Part of programming will involve reading the help manuals just for the sheer curiousity of it. Read things tha you dont need right now and remember how to find them later (Its not essential to remember the info for later)

Example, I think i pointed you to the the help pages for DataColumn.Expression before.. you should read the entire thing and think about it.. You may have then remembered seeing:

FUNCTIONS

The following functions are also supported:

CONVERT

Description
Converts particular expression to a specified .NET Framework Type.

Syntax
Convert(expression, type)

Arguments
expression -- The expression to convert.

type -- The .NET Framework type to which the value will be converted.


Example: myDataColumn.Expression="Convert(total, 'System.Int32')"

All conversions are valid with the following exceptions: Boolean can be coerced to and from Byte, SByte, Int16, Int32, Int64, UInt16, UInt32, UInt64, String and itself only. Char can be coerced to and from Int32, UInt32, String, and itself only. DateTime can be coerced to and from String and itself only. TimeSpan can be coerced to and from String and itself only.

This knowledge from http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression(VS.80).aspx may thus help you to write an expression that SUM()s the result of the CONVERT()sion of string to int. Good luck


ps; I intended the tone of this post to be a constructive help.. I hope it didnt come across as a nagging "read the manual, noob!" it wasnt intended that way

 

[cjard]

do please also note that youre working with XML files; they have a schema. DataSets support XML schema, you can definitely make a strongly typed dataset using the designer, to read your xml file into. Aside from solving a lot of code problems where youre addressing data in untyped ways, and having to cast it a lot (slow, generally bad pratice) you can also ensure that the process of reading the file performs this conversion for you

 

[elainec351]

Thank you for the feedback

Thank you so much for the feedback. I am absolutely going to read the information again. I tend to get bogged down in the problem at hand and don't make too much time for exploring (especially while in the middle of a project). I recognize this hampers me a bit, but the pressure of completion....blah blah blah.

I truly do appreciate you help and will take your advice.

Elaine

 

[cjard]

Thank you so much for the feedback. I am absolutely going to read the information again. I tend to get bogged down in the problem at hand and don't make too much time for exploring (especially while in the middle of a project). I recognize this hampers me a bit, but the pressure of completion....blah blah blah.

I truly do appreciate you help and will take your advice.

Elaine

True true.. when boss is breathing down your neck saying "get it done" it can be tempting to just get the answers to your questions, assemble them and voila.. Might have some homework to do then, eh? (Erk! Homework? What's that? I forgot that 50 years ago)

It sounds nerdy, but you might come to love programming for fun. I wrote an server that converts email into SMS and sends them anywhere in the world for $0.02, just to learn a bit about mime types and to have a go at the new XF Server architecture.. What a geek!