Good evening. I am using SQL Server 7 and ASP/VBScript. I am trying to develop a simple shopping cart as a project. I have two tables in my database, CountryShippingAmounts and Orders. I ship products internationally and each country has a specific shipping cost (there are over 50 countries that I ship to, but US shipping is $18.00 and shipping to England is $55.00, to name two examples).

The user enters the quantity of products that he wants to order and also selects his ship-to country from a drop-down menu. The drop-down menu retrieves the country list values from the CountryShippingAmounts table. Here is the sub-routine I use to retrieve values for the country ship-to drop-down menu:

Sub WriteCountryShippingAmounts()
Dim rsCountryShippingAmounts

Set rsCountryShippingAmounts = Server.CreateObject("ADODB.Recordset"
rsCountryShippingAmounts.Open "SELECT * FROM CountryShippingAmounts", oConn

If Not rsCountryShippingAmounts.BOF And Not rsCountryShippingAmounts.EOF Then

Do While rsCountryShippingAmounts.EOF = False
Response.Write "<option value=""" & rsCountryShippingAmounts.Fields("Country".Value & """"
Response.Write ">" & intID & Left(rsCountryShippingAmounts.Fields("Country".Value, 50)
Response.Write "</option>"
rsCountryShippingAmounts.movenext
Loop
End If
End Sub

The CountryShippingAmounts table consists of three fields: ID, Country, ShippingAmount.

1. How do I also display the shipping amount for each country in the drop-down menu? For example, I want the info for the US to read: United States - $18.00.

After the user makes his product selection and selects the ship-to country, he is taken to another page to confirm the order. Once the order is confirmed, the information is inserted into the Orders table.

2. How do I insert the shipping amount to the Orders table? (I do have the Country and ShippingAmount fields in the Orders table.)

3. General question: What is the best way to perform calculations (for example, number of products * shipping = total order price? Should calculations be done in stored procedures or can they be done in the ASP page?

Thank you so much for your help.

Andrea

Hi Andrea

1. rsCountryShippingAmounts.Fields("Country".Value & " - $" & rsCountryShippingAmounts.Fields("ShippingAmount".Value

2. You can get quite clever with this, but, an easy way would be to have two hidden fields, one to hold the country information (should be the ID really, but this does depend on how you've setup your table) and one to hold the shipping value. You can then include both of these hidden fields in your imsert statement.

3. I personally prefer to do all calculations in SQL as it's designed to data crunch, but it's your app and your choice.

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