Results 1 to 8 of 8

Thread: Invalid usage of aggregate function Sum() and Type: String

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    5

    Invalid usage of aggregate function Sum() and Type: String

    I created a VB . NET software about 9 years ago to sum the total usage of component amounts from a list of formulas. Essentially, I input a text file - format it so the data goes into a table then it takes a list of formulas from and excel file and adds the component amounts in that formula to generate a forecast.

    Here is a screenshot of the ugly version - for diagnostics.

    https://imgur.com/34MlkXX

    You can see the datatable formula, formula name, date, component, amount ......

    Haven't programmed for almost a decade, but I get a call saying the software ends with a database error which I conclude is the discontinuation of SQL compact version. I essentially recoded the project so the data is loaded into local datatables and I am able to get myself to the very end where the data is ready to be consolidated and I encounter this error.

    https://imgur.com/M57qI2G

    Code:
     For index As Integer = 2 To upperBound Step 2
                    Dim value As Object = dtTempA.Compute(String.Format("SUM({0})", dtTempA.Columns(index).ColumnName), String.Format("[{0}] = '{1}'", dtTempA.Columns(index - 1).ColumnName, code))
                    If TypeOf value Is Decimal Then
                        amount += CDec(value)
                    End If
    
                Next

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Invalid usage of aggregate function Sum() and Type: String

    You can only sum numbers. If the column contains text values (even if those values contain text representations of numbers) then summing it is not possible. If your data is supposed to be numeric, why does the column contain text in the first place? If your data is not numeric, why are you trying to sum it?

  3. #3

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    5

    Re: Invalid usage of aggregate function Sum() and Type: String

    Quote Originally Posted by jmcilhinney View Post
    You can only sum numbers. If the column contains text values (even if those values contain text representations of numbers) then summing it is not possible. If your data is supposed to be numeric, why does the column contain text in the first place? If your data is not numeric, why are you trying to sum it?
    Thank you JMC - originally I had the files uploading to a SQL server compact file (.xsd I believe) where the data types were predetermined using the designer. In recoding project where I am using a dataset, even if I set the column to be summed to a decimal, double etc it will still give me this error. Since I am parsing the data from a text file - do I need to convert the data to a decimal? To give you the broad scope of this software, I am basically summing all of the values that have similar components identified by the previous columns - for example:

    Comp1 Amount 1 Comp2 Amount2
    UB107 30.4 UB108 70.0
    UB107 30 UB108 30.0

    Would result in:

    Comp Amount
    UB107 60.4
    UB108 100.0

    Thanks!

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Invalid usage of aggregate function Sum() and Type: String

    If you are reading data from a text file, then it's coming in as a string. Somebody or something has to convert that to a number for it to be used as a number. With Option Strict OFF, it might possibly get converted implicitly in various places, but that doesn't appear to be happening here, and you shouldn't have Option Strict OFF anyways. If you have Option Strict ON, and you are not explicitly converting from string to number using something like CDec, Decimal.Parse, or Decimal.TryParse (since you said they were decimal numbers), and Option Strict ON doesn't flag some lines, then the strings are not being converted.
    My usual boring signature: Nothing

  5. #5

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    5

    Re: Invalid usage of aggregate function Sum() and Type: String

    Quote Originally Posted by Shaggy Hiker View Post
    If you are reading data from a text file, then it's coming in as a string. Somebody or something has to convert that to a number for it to be used as a number. With Option Strict OFF, it might possibly get converted implicitly in various places, but that doesn't appear to be happening here, and you shouldn't have Option Strict OFF anyways. If you have Option Strict ON, and you are not explicitly converting from string to number using something like CDec, Decimal.Parse, or Decimal.TryParse (since you said they were decimal numbers), and Option Strict ON doesn't flag some lines, then the strings are not being converted.
    I had a hunch that is happening. Beginner question though - why isn't it throwing an error when I input the data into a column with a decimal datatype?

  6. #6
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Invalid usage of aggregate function Sum() and Type: String

    Do you have Option Strict ON? If not, then go to either Project |Properties and check the checkbox for Option Strict on the Compile tab, or better yet, find where you can set it in Tools | Options (I always have to hunt for that one, as I never remember). You really want Option Strict ON, because it forces you to be better.

    However, if you have it on already, and you are not getting an exception, it's likely because the column isn't quite as much decimal as you may have hoped. Your typical datatable isn't strongly typed. You can set a data type, but I think that the IDE doesn't care about that and doesn't enforce the type safety. I believe that all cells are Object for your general datatable, despite what you say. I may be wrong about this, as I can say that I have never looked into it all that much, but it makes sense. After all, even if you say the column is Decimal, you don't get a Decimal from the cell, you get an Object that you still have to cast to Decimal.
    My usual boring signature: Nothing

  7. #7

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    5

    Re: Invalid usage of aggregate function Sum() and Type: String

    Shaggy - I've been turning option strict on/off during this project for testing, currently it is on and the code in the OP is giving me a few errors, as expected.

    Here is the code that is used to to parse the text file - which is delimited by fixed spaces

    Code:
         While Not sr.EndOfStream
    
                        Dim str As String = sr.ReadLine()
                        Dim validLength As Integer = 716
    
                        If str.Length = validLength Then
    
                            Dim dr As DataRow = dtForm.NewRow()
                            dr(0) = str.Substring(0, 13).Replace(" ", "")
    
                            Dim CharIndex As Integer = 59
                            Dim CharLength As Integer = Nothing
    
                            Dim amount As Decimal
                            ColIndex = 1
    
                            While ColIndex <= 40
    
                                dr(ColIndex) = str.Substring(CharIndex, 9).Replace(" ", "")
                                CharIndex = CharIndex + 9
                                ColIndex = ColIndex + 1
    
                                dr(ColIndex) = If(Decimal.TryParse(str.Substring(CharIndex, 8).Replace(" ", ""),
                                                      amount),
                                                      CObj(amount),
                                                      DBNull.Value)
                                CharIndex = CharIndex + 8
                                ColIndex = ColIndex + 1
    
                            End While
    
                            dtForm.Rows.Add(dr)
    
                        End If
    
                    End While
    You can see that "amount" which is the value placed into the datatable and summed later is initially set as a decimal, or no?

  8. #8

    Thread Starter
    New Member
    Join Date
    Jan 2018
    Posts
    5

    Re: Invalid usage of aggregate function Sum() and Type: String

    I see it now - I convert "amount" into an object with "CObj(amount)"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width