Datatable with column expression-CodePudding

I'm trying to use datatable columns with expression but when a load data, some columns works and other shows "Cannot perform '*' operation on System.Double and System.String"

I use this procedure in vb.net

Load datatable from datareader
Assign previous load expressions to defined columns
List items in html table

I’m using this dataset with data between of columns with Expression.

Can you help me?

UPDATE:

typo: instead of IVA in the first appearance I mean IVAP, the next column name IVA is correct

the image is data copied from SQL management studio over excel with explains about the situation

this is my code

            Dim dtFinalData As New DataTable("DataProcessed")

            For i As Integer = 1 To objReader.FieldCount - 1

                    Dim cColumName As DataColumn = New DataColumn
                    With cDiff
                        .DataType = System.Type.GetType("System.Double")
                        .ColumnName = objReader.GetName(i).ToString()
                    End With
                    dtFinalData.Columns.Add(cColumName)

            Next

            Dim cColWithExpression1 As DataColumn = New DataColumn
            With cColWithExpression1
                .DataType = System.Type.GetType("System.Double")
                .ColumnName = "PCONDT"
                .Expression = "PSINIVA - (PSINIVA * (Discount/100))"
            End With
            dtFinalData.Columns.Add(cColWithExpression1)

            Dim cColWithExpression2 As DataColumn = New DataColumn
            With cColWithExpression2
                .DataType = System.Type.GetType("System.Double")
                .ColumnName = "Ivap"
                .Expression = "(PCONIVA-PCONDT)*Units"
            End With
            dtFinalData.Columns.Add(cColWithExpression2)

            Dim cCol As DataColumn = New DataColumn
            With cCol
                .DataType = System.Type.GetType("System.Double")
                .ColumnName = "IVA"
                .DefaultValue = 21
            End With
            dtFinalData.Columns.Add(cCol)                   

            Dim cColWithExpression3 As DataColumn = New DataColumn
            With cColWithExpression3
                .DataType = System.Type.GetType("System.Double")
                .ColumnName = "PCONIVA"
                .Expression = "(PCONDT*(1 (IVA/100)))"
            End With
            dtFinalData.Columns.Add(cColWithExpression3)    

            If objReader.HasRows Then
                While objReader.Read()
                        Dim RecordRow As DataRow = dtFinalData.NewRow
                        
                        RecordRow("Units") = objReader.Item("Units")
                        RecordRow("PSINIVA") = objReader.Item("PSINIVA")
                        RecordRow("PU") = objReader.Item("PU")
                        RecordRow("IVA") = objReader.Item("IVA")
                        dtFinalData.Rows.Add(RecordRow)
                End While
            End If

UPDATE 2 - SOLVED (I think)

Thanks everyone for the answers, I found the procedure to work with the cascade of column expression, in datatable

Create datatable with column names and numeric data type . In my case UNITS, PSINIVA, PU, DISCOUNT, IVA (I adjust my data source which is in a SQLDatareader to add IVA data column from database)
Add values from data source
Add columns definition with name, data type and the specific formula (Expression) but in sequence, where the previously column complete their data before the next formula. In my case first expression column must be PCONDT, next EXENT, next PCONIVA, and final IVAP

Using parts of the original code (perhaps not so performant), this is the final code

        Dim dtFinalData As New DataTable("DataProcessed")

        For i As Integer = 1 To objReader.FieldCount - 1

                Dim cColumName As DataColumn = New DataColumn
                With cDiff
                    .DataType = System.Type.GetType("System.Double")
                    .ColumnName = objReader.GetName(i).ToString()
                End With
                dtFinalData.Columns.Add(cColumName)

        Next
        
        If objReader.HasRows Then
            While objReader.Read()
                    Dim RecordRow As DataRow = dtFinalData.NewRow
                    RecordRow("Units") = objReader.Item("Units")
                    RecordRow("PSINIVA") = objReader.Item("PSINIVA")
                    RecordRow("PU") = objReader.Item("PU")
                    RecordRow("Discount") = objReader.Item("Discount")
                    RecordRow("IVA") = objReader.Item("IVA")
                    dtFinalData.Rows.Add(RecordRow)
            End While
        End If      
           
        Dim cColWithExpression1 As DataColumn = New DataColumn
        With cColWithExpression1
            .DataType = System.Type.GetType("System.Double")
            .ColumnName = "PCONDT"
            .Expression = "PSINIVA - (PSINIVA * (Discount/100))"
        End With
        dtFinalData.Columns.Add(cColWithExpression1)

        Dim cColWithExpression2 As DataColumn = New DataColumn
        With cColWithExpression2
            .DataType = System.Type.GetType("System.Double")
            .ColumnName = "EXENT"
            .Expression = "IIF(IVA=0,PCONDT*Units,0) "
        End With
        dtFinalData.Columns.Add(cColWithExpression2)

        Dim cColWithExpression3 As DataColumn = New DataColumn
        With cColWithExpression3
            .DataType = System.Type.GetType("System.Double")
            .ColumnName = "PCONIVA"
            .Expression = "(PCONDT*(1 (IVA/100)))"
        End With
        dtFinalData.Columns.Add(cColWithExpression3)    


        Dim cColWithExpression4 As DataColumn = New DataColumn
        With cColWithExpression4
            .DataType = System.Type.GetType("System.Double")
            .ColumnName = "Ivap"
            .Expression = "(PCONIVA-PCONDT)*Units"
        End With
        dtFinalData.Columns.Add(cColWithExpression4)

This is how data appears, after this code

CodePudding user response：

It appears your PCONDT and PCONIVA columns are of type String. Change them to a numeric type and your formulas should work.

CodePudding user response：

Consider using Visual Studio's in-built DataSet tool instead. With it you can easily accomplish all that you've described, with much less effort.

For example, here's the definition for the PCONDT column:

This code:

Private Sub TestDataSet()
  Dim oTable As Test.Db.DataProcessedDataTable
  Dim oRow1 As Test.Db.DataProcessedRow
  Dim oRow2 As Test.Db.DataProcessedRow
  Dim oDb As Test.Db

  oDb = New Test.Db
  oTable = oDb.DataProcessed
  oRow1 = oTable.NewDataProcessedRow
  oRow2 = oTable.NewDataProcessedRow

  oRow1.Units = 2
  oRow1.PSINIVA = 20
  oRow1.PU = 20
  oRow1.Discount = 10
  oRow1.IVA = 21

  oRow2.Units = 1
  oRow2.PSINIVA = 100
  oRow2.PU = 100
  oRow2.Discount = 0
  oRow2.IVA = 21

  oTable.AddDataProcessedRow(oRow1)
  oTable.AddDataProcessedRow(oRow2)

  oDb.WriteXml("Db.xml")
End Sub

...produces this XML:

<?xml version="1.0" standalone="yes"?>
<Db xmlns="http://tempuri.org/Db.xsd">
  <DataProcessed>
    <Units>2</Units>
    <PSINIVA>20</PSINIVA>
    <PU>20</PU>
    <Discount>10</Discount>
    <IVA>21</IVA>
    <PCONDT>18</PCONDT>
    <EXENT>0</EXENT>
    <PCONIVA>21.78</PCONIVA>
    <Ivap>7.560000000000002</Ivap>
  </DataProcessed>
  <DataProcessed>
    <Units>1</Units>
    <PSINIVA>100</PSINIVA>
    <PU>100</PU>
    <Discount>0</Discount>
    <IVA>21</IVA>
    <PCONDT>100</PCONDT>
    <EXENT>0</EXENT>
    <PCONIVA>121</PCONIVA>
    <Ivap>21</Ivap>
  </DataProcessed>
</Db>

...which is, I believe, your desired result.

This approach has the added benefit of eliminating the Magic Strings from your code.