hELP WITH LOADGRID QUERY

[Alfarata]

SQL.ExecQuery("SELECT         VendorName, MachineNumber,Date, Description, Quantity, Price, Quantity * Price AS TOTAL " &
                    "FROM            Parts " &
                    "WHERE [MACHINENUMBER] ='" & PartAsset2 & "'")

this code works fine now when I try to add a Group By I get this error

System.InvalidCastException: 'Conversion from string "ExceQuery Error:
Column 'Parts" to type 'Integer' is not valid.'
Inner Exception
FormatException: Input string was not in a correct format.

SQL.ExecQuery("SELECT         VendorName, MachineNumber,Date, Description, Quantity, Price, Quantity * Price AS TOTAL " &
                    "FROM            Parts " &
                    "WHERE [MACHINENUMBER] ='" & PartAsset2 & "'" &
                     "GROUP By VendorName")

can you tell me why I get this error when using the Group BY phrase?

thanks

[OptionBase1]

The tail end of your "Group By" query will look something like this:

"WHERE [MACHINENUMBER] ='2'GROUP By VendorName"

You are missing a separating space.

[jmcilhinney]

Please fix your Caps Lock key. I can't speak for others but if someone repeatedly makes their posts harder to read, either deliberately or through laziness, then I'm less likely to provide the help they want.

Also, isn't this a continuation of a topic you started in another thread with the same name? Why the new thread?

As for the issue, why would you expect that GROUP BY to work? It seems that you haven't done any research on what GROUP BY does and have just assumed that it will do what you want it to. It obviously doesn't, so the next step should have been some research of your own, rather than asking others to explain the basics to you.

GROUP BY works hand in hand with aggregate functions, e.g. SUM and COUNT. If you're not aggregating anything then there's no grouping to be done. It seems like you're assuming that that SQL will somehow give you a single group for each distinct VendorName value but what exactly do you think those groups should look like? If all the other column values are different then there would need to be multiple rows per group, so exactly what would a group be? You're trying to achieve something impossible without even knowing what the result should be.

[FunkyDexter]

^Both valid answers but I'm not sure either would generate that particular error.

Could you show us the actual sql that's being generated?

[jmcilhinney]

I'm not sure either would generate that particular error.

My suspicion is that the SQL is bad so an error message gets generated from that but some code inside that ExecQuery method is assuming that a numeric result is being returned and the conversion of the error message to an Integer fails. It's a guess though, because, not for the first time, someone has a custom DAL and has chosen not to reveal any of its inner workings while expecting us to work out why it's not working.

[Alfarata]

As you may have noticed I am having problems with this database query. I have little or no training in VB.net or SQL. But, I am trying to figure this out. Let me explain what I’m try to do.
I have a table in 2017 SQL Named Parts.
I collect from a form:
1. PartsusedID int
2. VendorName nvarchar(50)
3.MachineNumber int
4. Date date
5. PartNumber nvarchar(50)
6. Description nvarchar(100)
7. Quantity int
8. Price real
9DeletedPart bit
Now what I would like to do is query for a machineNumber and a record not marked as deleted.
And show a Sum for all parts used for said part number. Show as a total for each vendor.
But I believe that I need additional code or second query to total Price X Quantity first. I seem to remember you don’t save math items separate in a database. So there is no line Total_Parts_Cost.
I do this math in the program but it is not saved. Should I add it to the table?
Or where to go from here.
I have been told to look for a answer and I have been searching I just could use some encouragement on how to proceed.