Calculating shipping and handling charges

[loveday]

Can someone, please assist me one more time?

I have a page called checkout.asp as part of an ecommerce site we are building.

The way we have it written so far, it calculates tax and adds the tax to customer's total purchase based on a 5% tax.

Right now, we are trying to change that; we don't want to charge taxes any longer, however, we want to add shipping and handling charges.

The approach we want to take is to calculate the s & h charge based on weight ranges, zip code and state code.

So, on our db, we have 2 tables: shipping table and products table.

On shipping table,we have 5 fields: shippingId (autoNumber),fromWeight, toWeight, zip, statecode, and shippingCharges.

Example would be:

FromWeight toWeight Zip stateCode S&H charges
1lbs 5lbs 30303 GA $20.00

Then on the products table is another field called shippingWeight.

Let's assume that that shippingWeight is 4ilbs.

We would then query shipping table to compare the shippingweight with any weight range on shipping table and determine what range the shippingweight falls, and the zip code and grab the shipping charge.

Does anyone know how to implement me?

 

[ChrisHirst]

Try

"SELECT charges FROM s_table WHERE " & prod_weight & " >= from_weight AND " & prod_weight & " <= to_weight AND " & zip & " = zip AND " & state & " = statecode ;"

(on the fly SQL. so not tested but it looks ok)


Chris.

Indifference will be the downfall of mankind, but who cares?

http://www.candsdesign.co.uk

A website that proves the cobblers kids adage.

http://www.cram-system.com

Nightclub counting systems

So long, and thanks for all the fish.

 

[loveday]

hi Chris,
Thanks for the response and sorry for my late response.

Ok, as indicated in my first post, stateCode, zip, fromWeight, toWeight, shippingCharges are from a table called shipping while shippingWeight is from a table called products.

 

[Lothario]

considering you have something like this...

table 1 name: products

productID|weight|StateCode|zip

table 2 name: shipping

StateCode|zip|fromWeight|toWeight|ShippingCharges

Try something like this:

SELECT p.productID,s.ShippingCharges FROM products p, Shipping s WHERE p.weight BETWEEN s.fromWeight AND s.toWeight AND p.zip=s.zip AND p.StateCode=s.StateCode

-L

 

[loveday]

thanks much for the response.

I am more worried about how to automatically add this total to the cost of customer's purchase during checkout.

For instance, the below code will add tax to total customer purchase:

global.asa

[code]
Sub Application_OnStart
	'Tax percentage to add to order total
	'change to match your own situation
	Dim stax
	stax = 0.05
	Application("taxP") = stax
End Sub

then

invoking global.asa

	 	'inttax = isubtotal * Application("taxP")
	 	lDefaultTax = 0.05
		if len(trim(Application("taxP")))>0 then
		  inttax = isubtotal * Application("taxP")
		else
		  inttax = isubtotal * lDefaultTax
		end if

         '--------------add this code--------------'
         'response.write "inttax: " & inttax & "<BR>"
         'response.write "isubtotal: " & isubtotal & "<BR>"
         'response.write "Application(""taxP""): " & Application("taxP") & "<BR>"
         '--------------add this code--------------'


		strHTML = strHTML & "<tr>"
		strHTML = strHTML & "<td></td><td></td><td align='right'><font color='midnightBLUE'></font></td>"
		strHTML = strHTML & "<td background='images/lefttop.jpg' nowrap><font color=000000>S & H included</font></td>"
		strHTML = strHTML & "<td bgColor=lightgoldenrodyellow>" & FormatCurrency(inttax,2) & "</td>"
		strHTML = strHTML & "</tr>"

		isubtotal = isubtotal + inttax

I would like to do similar with the value generated with you guys sql code.

Thanks again

 

[Lothario]

whats the problem you are having?

did that sql query get the correct value....first check that using query analyzer...

-L