I don’t know if this is possible? Any geniuses out there.

I am using Dreamweaver, MS SQL, ASP Vbscript

I am trying to produce product lists using a recordset and repeat region see www.thebigmanshop.co.uk/NewWebsite/prodlistoffers.asp

The recordset I use to produce the list is as follows RS_Products

SELECT ProdID, fldtitle, flddescription, fldimage, fldprice
FROM dbo.bigman_products
WHERE discountID <> '1'

1 being the value for no discount applied.

This list shows all products where an offer has been applied but unfortunately shows the full price rather than the discounted price.

I have 2 tables. bigman_products and bigman_ tblDiscount

dbo.bigman_tblDiscount looks like this. (fldMultiplier is not used anywhere else on the site and has been added by me as it may simplify the calculation. (maybe, maybe not!))

disountID(int), fldDiscount,(int) fldMultiplier, (money,8)
1 0 1
2 5 0.95
3 10 0.90
4 15 0.85
5 20 0.80
6 25 0.75
7 30 0.70
8 35 0.65
9 40 0.60
10 45 0.55
11 50 0.50

dbo.bigman_products looks like this. (there are other fields also)

ProdID (int)
Fldtitle (char)
Flddescription (char)
Fldimage (char)
Fldprice (money,8)

How can I produce a recordset list of discounted products which calculates the correct price?


Russell

Hi Russell

How do you know what ProdID to apply the discount to? There dosn't appear to be any links between the two tables, maybe I'm missing something here.

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
Lee Diggins - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

The recordset produces a list of products based on WHERE discountID &lt;&gt; '1' or some other parameter. Not just 1 product.

Someone has supplied the answers as follows.

1. Change the SQL statement to this (links the product table to the discount table, this assumes you have discountID in both tables):

------------------
SELECT ProdID, fldtitle, flddescription, fldimage, fldprice, fldprice * fldMultiplier AS NEWPRICE FROM dbo.bigman_products, dbo.bigman_tblDiscount WHERE dbo.bigman_products.discountID = dbo.bigman_tblDiscount.discountID AND (discountID &lt;&gt; '1' )
------------------

2. Use the new field, "NEWPRICE", for displaying, etc.

Russell

Hi Russell

Thanks for the update.

Would I be right in assume that this is complete?

Sharing Knowledge Saves Valuable Time!!!
~ ~ ~ ~ ~ ~
Lee Diggins - <i>DMXzone Manager</i>
<font size="1">[ Studio MX/MX2004 | ASP -> VBScript/PerlScript/JavaScript | SQL | CSS ]</font>

Hi Lee. This is complete now thanks.

Russell