• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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("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.

RJ
0
ramyar
Asked:
ramyar
  • 2
1 Solution
 
darksinclairCommented:
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.

Cheers,
0
 
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:

http://www.psacake.com/web/et.asp
http://www.4guysfromrolla.com/webtech/112998-1.shtml

RJ
0
 
darksinclairCommented:
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!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now