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:

'CalculatedPrice' =
    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

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("CalculatedPrice")%><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("CalculatedPrice")%><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.

Who is Participating?
darksinclairConnect With a Mentor Commented:
This is a shot in the dark.. and perhaps this isnt the best place to be doin it,, but I had a problem similar to this... mine was resolved by adding cursor types onto the end of my recordset Open...

Is your recordset defined currently as :  rsItems.Open SQL, Conn ?

If so, try to change it to rsItems.Open SQl, Conn, 2, 3 .. I think this might have something to do with the way the recordset is opened.

ramyarAuthor Commented:
Thanks Darksinclair!
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:


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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.