Summary:

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.

Expected result:

The quotes displayed should have a value of Quot_grossamt * 100 in the Quot_Grossamt column.

Actual result (please include error message):

SQL error:

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'

Workaround:

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))
returns numeric(24,6)
as
begin
    declare @result numeric(24, 6)
    select @result = (@value1 * @value2)
    return @result
end
go

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.

More info:

 Download: ASP page (TESTmulti.asp)