-
Jan 11th, 2018, 04:46 PM
#1
Thread Starter
New Member
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
-
Jan 11th, 2018, 08:02 PM
#2
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?
-
Jan 12th, 2018, 09:43 AM
#3
Thread Starter
New Member
Re: Invalid usage of aggregate function Sum() and Type: String
Originally Posted by jmcilhinney
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!
-
Jan 12th, 2018, 10:15 AM
#4
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
-
Jan 12th, 2018, 10:25 AM
#5
Thread Starter
New Member
Re: Invalid usage of aggregate function Sum() and Type: String
Originally Posted by Shaggy Hiker
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?
-
Jan 12th, 2018, 06:17 PM
#6
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
-
Jan 13th, 2018, 11:15 AM
#7
Thread Starter
New Member
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?
-
Jan 13th, 2018, 11:28 AM
#8
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|