ramyar
asked on
SQL calculated field not showing in ASP page
Hi All,
I am running into a weird problem. I have a SQL that generates results which include a calculated field:
SELECT
A_Items.*,
A_Prices.Price,
'CalculatedPrice' =
CASE
WHEN sp_Action_Flag = 'P' THEN (A_Prices.Price - (A_Prices.Price * sp_Action_Amount))
WHEN sp_Action_Flag = 'F' THEN (A_Prices.Price - sp_Action_Amount)
WHEN sp_Action_Flag = 'L' THEN (sp_Action_Amount)
ELSE A_Prices.Price
END
In SQL Query Analyzer and in the test results of Dreamweaver the results come back fine. However, when I actually view the page in a browser it doesn't show the A_Prices.Price. For example:
<%=rsItems("CalculatedPric e")%><br />
<%=rsItems("Price")%><br />
This displays the correct value for rsItems("CalculatedPrice") but displays nothing for rsItems("Price"). If I flip the two around, it all works:
<%=rsItems("Price")%><br />
<%=rsItems("CalculatedPric e")%><br />
Is there a reason I cannot access the information in PRICE if CALCULATEDPRICE is referrenced first? Is there a work around or better way to do this without having to displayed my SQL generated result last? I'm mean, I can always do the calculation in ASP and bypass the SQL, but that's so much more convenient. Is there just a limitation when I want to do this stuff in SQL? Thanks!
I'm running Windows 2000 Server and MS SQL Server 2000 Developers Edition.
RJ
I am running into a weird problem. I have a SQL that generates results which include a calculated field:
SELECT
A_Items.*,
A_Prices.Price,
'CalculatedPrice' =
CASE
WHEN sp_Action_Flag = 'P' THEN (A_Prices.Price - (A_Prices.Price * sp_Action_Amount))
WHEN sp_Action_Flag = 'F' THEN (A_Prices.Price - sp_Action_Amount)
WHEN sp_Action_Flag = 'L' THEN (sp_Action_Amount)
ELSE A_Prices.Price
END
In SQL Query Analyzer and in the test results of Dreamweaver the results come back fine. However, when I actually view the page in a browser it doesn't show the A_Prices.Price. For example:
<%=rsItems("CalculatedPric
<%=rsItems("Price")%><br />
This displays the correct value for rsItems("CalculatedPrice")
<%=rsItems("Price")%><br />
<%=rsItems("CalculatedPric
Is there a reason I cannot access the information in PRICE if CALCULATEDPRICE is referrenced first? Is there a work around or better way to do this without having to displayed my SQL generated result last? I'm mean, I can always do the calculation in ASP and bypass the SQL, but that's so much more convenient. Is there just a limitation when I want to do this stuff in SQL? Thanks!
I'm running Windows 2000 Server and MS SQL Server 2000 Developers Edition.
RJ
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
no problem ramyar,, I know I spent a good half day trying to figure out my problem... Your's just seemed to ring a bell and I had a good feeling :)
Cheers M8t, Happy Coding!
Cheers M8t, Happy Coding!
ASKER
Your advice led me down the right path. I set my CursorType to 3 (I usually explicitly call my recordsets) and it worked! For anyone else interested, I also found these websites informational:
http://www.psacake.com/web/et.asp
http://www.4guysfromrolla.com/webtech/112998-1.shtml
RJ