Using the multiplication operator (*) with SeletSql() causes an error.
Steps to Reproduce:
1. Add the attached custom APS page (TESTmulti.asp) to a menu (e.g. the User menu).
2. Run it. It should display a list of quotes created by the current user.
The quotes displayed should have a value of Quot_grossamt * 100 in the Quot_Grossamt column.
Actual result (please include error message):
SELECT Quot_orderQuoteID, Quot_Status, Quot_reference, Quot_Description, (Quot_grossamt count(*) as fcount 100) as Quot_grossamt, Quot_grossamt_CID FROM Quotes WITH (NOLOCK) where Quot_CreatedBy = 1 Incorrect syntax near 'count'
There's a few ways around this. The first would be to create a new function to do the multiplication for you:
create function dbo.NUM_MULTIPLY (@value1 as numeric(24, 6), @value2 as numeric(24, 6))
declare @result numeric(24, 6)
select @result = (@value1 * @value2)
This can then be used in your SQL statement:
var strSQL = "SELECT orde_status, orde_reference, orde_description, Orde_synchstatus, Orde_OrderQuoteID, orde_grossamt_CID, dbo.NUM_MULTIPLY(orde_grossamt, 10) as orde_grossamt FROM Orders WHERE orde_opportunityId = " + myRecordId;
This is a little messy. Another method might be to add a computed column to the table, and alias that off as orde_grossamt in your statement, though we tend to discourage adding columns directly to the database.
A third option would be to use a custom view, and calculate the value there, then use the view in your SELECT statement. This would be the neatest option, and what we would recommend.
Download: ASP page (TESTmulti.asp)