ASP Page to update database

TimBiesiek · Nov 2, 2010

Hi all,

I am creating a shopping cart for a shop, using ASP, Javascript and AJAX, but am having a few troubles with it.

I have an ASP page that shows all products in a selected category, with a button next to each product to allow the user to add it to their shopping trolley. When they click this button I want it to use Javascript and AJAX to add the product to the trolley and then update only the display of the trolley info at the top of the screen (In a Div tag with ID of Cart).

The page that shows the products is as below:

Code:

<!--#include virtual="/DB/conn.asp" -->
<html>
<head>        
  <title></title>
  <link href="css.css" rel="stylesheet" type="text/css" />
  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
  <script language="JavaScript" type="text/JavaScript">
<!--
function MM_swapImgRestore() { //v3.0
  var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a[i])&&x.oSrc;i++) x.src=x.oSrc;
}

function MM_preloadImages() { //v3.0
  var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
    var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
    if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}
}

function MM_findObj(n, d) { //v4.01
  var p,i,x;  if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
    d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
  if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
  for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
  if(!x && d.getElementById) x=d.getElementById(n); return x;
}

function MM_swapImage() { //v3.0
  var i,j=0,x,a=MM_swapImage.arguments; document.MM_sr=new Array; for(i=0;i<(a.length-2);i+=3)
   if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];}
}
//-->

[green]function RefreshCart(Func, Prod, Qty)
{
var url
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("Cart").innerHTML=xmlhttp.responseText;
    }
  }
url="/common/cart.asp?Func="+Func+"&Prod="+Prod+"&Qty="+Qty;
xmlhttp.open("GET",url,true);
xmlhttp.send();
}
[/green]
</script>
  </head>
  <body bgcolor="#ffffff" onLoad="MM_preloadImages('Images/Home%202.gif','Images/Products%202.gif','Images/About%20Us%202.gif','Images/Contact%20Us%202.gif','Images/BOM 2.gif')">
    
  <div id="Navigation">
    <!-- #include virtual="/common/Navigation.inc" -->
  </div>
  
<div id="Header">
  <img src="Images/MediumLogo.gif" width="460" height="91">  </div>
  
[red]<div id="Cart">
	<!-- #include virtual="/common/cart.asp" -->
</div>[/red]
  
<%
	dim objConn
	dim RsProducts
	dim RsProductCount
	Dim strSQL
	Dim IntNumFound
	
	Set objConn=Server.CreateObject("ADODB.Connection")
	objConn.Mode=adModeReadWrite
	objConn.ConnectionString = aConnectionString
	objConn.Open

	Set cmd = Server.CreateObject("ADODB.Command")
 
	cmd.ActiveConnection = objConn
	cmd.CommandType = 1
	cmd.Prepared=True
	StrSQL = "SELECT count(*) as ProductCount FROM Product WHERE CategoryID =?"
 
	cmd.CommandText = StrSQL

	Set param = cmd.CreateParameter("CategoryID", 5, 1)

	cmd.Parameters.Append param
	param.value = request.QueryString("CatID")
	Set RsProductCount= cmd.Execute

	Set RsProducts = Server.CreateObject("ADODB.Recordset")

	IntNumFound = RsProductCount("ProductCount")

	'Get products in selected category
	Set cmd2 = Server.CreateObject("ADODB.Command")
 
	cmd2.ActiveConnection = objConn
	cmd2.CommandType = 1
	cmd2.Prepared=True
	StrSQL = "SELECT * FROM Product, Product_Category WHERE Product.CategoryID = Product_Category.CategoryID AND Product.CategoryID =?"
 
	cmd2.CommandText = StrSQL

	cmd2.Parameters.Append param
	Set RsProducts= cmd2.Execute

	if IntNumFound <= 0 then
		response.Write("<div id='Body'><BR>")
		response.Write("<span class='style16'>Hmmm, we seem to have misplaced the products in this category. Please go back and try again...</span>")
		response.Write("</div>")
		response.End()
	else
		response.Write("<div id='Top_Header'>	<span class='style5'><a href='Shopping.asp'>Shopping</a> &gt; ")
		response.Write(RsProducts("Category"))
		response.Write("</span></div>")
		response.Write("<div id='Body'><BR>")
		response.Write("<p class='style1'>Our current " & RsProducts("Category") & "</p>")
		response.write("<table width='791' border='0' cellspacing='0' cellpadding='0' class='style1'>")
		response.write("<tr>")
		response.write("<th width='450' scope='col' align='left'>Product</th>")
		response.write("<th width='110' scope='col' align='left'>Price</th>")
		response.write("<th width='100' scope='col' align='left'></th>")
		response.write("<th width='131' scope='col' align='center'></th>")
		response.write("</tr>")
		
		do while not RsProducts.eof
			response.Write("<tr ")
%>
  onMouseOver="this.bgColor = '#FFFF99'" onMouseOut ="this.bgColor = '#FFFFFF'"
  <%
			response.write(">")
			response.Write("<td><a href='Product.asp?ProdID=" & RsProducts("ProductID") & "'><p class='style1'>" & RsProducts("ProductName") & "</a></td>") 
			response.Write("<td>" & RsProducts("Price") & "</td>") 
			response.Write("<td>")
			if not isnull(RsProducts("ImageName")) and RsProducts("ImageName") <> "" then
				response.Write("<img src='Shopping/Images/" & RsProducts("ImageName") & "' width='90'>")
			end if
			response.write("</td>") %>
			[blue]<td><button type="button" onclick="RefreshCart('Add', <% response.Write(RsProducts("ProductID")) %> , 1)">Add to Cart</button></td>[/blue]
<%
			response.Write("</tr>")
			RsProducts.movenext
		loop

	end if
	
	RsProducts.close
	set RsProducts = nothing
	RsProductCount.close
	set RsProductCount = nothing
	objConn.Close
	set objConn = Nothing

	%>
</div>       
</body>
</html>

The bit in red is the DIV that I want update without refreshing the page, the bit in blue is the button with the associated procedure that is called and the parameters that are passed to the procedure, and the green bit is the function that is called from the button click.

The page that it is meant to call (cart.asp) is as below:

Code:

<%

	Set objConn=Server.CreateObject("ADODB.Connection")
	objConn.Mode=adModeReadWrite
	objConn.ConnectionString = aConnectionString
	objConn.Open

	Set cmd = Server.CreateObject("ADODB.Command")
 
	cmd.ActiveConnection = objConn
	cmd.CommandType = 1
	cmd.Prepared=True
	StrSQL = "SELECT * FROM Cart WHERE ASPSessionID =?"
 
	cmd.CommandText = StrSQL

	Set param = cmd.CreateParameter("ASPSessID", 5, 1)

	cmd.Parameters.Append param
	param.value = session.SessionID

	IntCartID = 0

	'Create Cart ID if it doesn't exist
	do while IntCartID = 0
		Set RsCart = cmd.Execute
		
		with RsCart
			if .eof then
				StrSQL = "INSERT INTO Cart(ASPSessionID) " & _
					" VALUES(" & session.SessionID & ")"
				objConn.execute StrSQL
			else
				IntCartID = RsCart("CartID")
			end if
		end with
	loop

'If adding a product to cart
	if request.QueryString("Func") = "Add" then
		Set cmd2 = Server.CreateObject("ADODB.Command")
	 
	 	'Get current Price of product
		cmd2.ActiveConnection = objConn
		cmd2.CommandType = 1
		cmd2.Prepared=True
		StrSQL = "SELECT Price FROM Product WHERE ProductID =?"
 
		cmd2.CommandText = StrSQL

		Set param2 = cmd2.CreateParameter("ProdID", 5, 1)

		cmd2.Parameters.Append param2
		param2.value = request.QueryString("Prod")
		Set RsProductPrice= cmd2.Execute

		IntPrice = RsProductPrice("Price")

		'Get items in cart
		StrSQL = "SELECT * FROM Cart_Product WHERE CartID = " & IntCartID & " AND ProductID = ?" 		
		
		Set cmd3 = Server.CreateObject("ADODB.Command")
	 
		cmd3.ActiveConnection = objConn
		cmd3.CommandType = 1
		cmd3.Prepared=True	 
		cmd3.CommandText = StrSQL
	
		cmd3.Parameters.Append param2
		param2.value = request.QueryString("Prod")
		Set RsCartProduct = cmd3.Execute
		
		'If no products in cart yet
		if RsCartProduct.eof = true then
			RsCartProduct.addnew
			RsCartProduct("CartID") = IntCartID
			RsCartProduct("ProductID") = request.QueryString("Prod")
			RsCartProduct("Quantity") = request.QueryString("Qty")
			RsCartProduct("Price") = IntPrice
			RsCartProduct.update
		else
			'If the price is the same, update quantity
			if RsCartProduct("Price") = IntPrice then
				RsCartProduct("Quantity") = RsCartProduct("Quantity") + request.QueryString("Qty")
				RsCartProduct("Price") = IntPrice
				RsCartProduct.update
			else
				'Otherwise add the product with the new price
				RsCartProduct.addnew
				RsCartProduct("CartID") = IntCartID
				RsCartProduct("ProductID") = request.QueryString("Prod")
				RsCartProduct("Quantity") = request.QueryString("Qty")
				RsCartProduct("Price") = IntPrice
				RsCartProduct.update
			end if
		end if
	else
	
	end if

%>

<table width="160" border="1" cellspacing="0" cellpadding="5">
	<tr>
    	<td width="160" valign="top" align="center">
    		<img src="Images/Trolley.gif" width="70" height="70" />
        </td>        
	</tr>
    <tr>
    	<td align="left" class="style5">
        	Products in cart: 
			<%
	'Show total number and total price of products in cart
	Set cmd4 = Server.CreateObject("ADODB.Command")
 
	cmd4.ActiveConnection = objConn
	cmd4.CommandType = 1
	cmd4.Prepared=True
	StrSQL = "Select cart.cartid, sum(quantity) as quantity, price from Cart, Cart_Product where cart.cartid = cart_product.cartid and Aspsessionid = " & session.SessionID & " group by cart.cartid, price"
 
	cmd4.CommandText = StrSQL

	Set RsCart = cmd4.Execute
	
	IntQty = 0
	IntExtPrice = 0
	
	do while not RsCart.eof
		IntExtPrice = IntExtPrice + RsCart("Quantity") * RsCart("Price")
		IntQty = IntQty + RsCart("Quantity")
		RsCart.movenext
	loop
	
	response.Write(IntQty)
	response.Write("<br>Cart Total: NZD$" & IntExtPrice)
			%>
		</td>
    </tr>
    <tr>
    <td align="center">
    	<a href="ViewCart.asp">Check out</a>
    </td>
    </tr>
</table>

I have tried putting a response.write("Hi") statement with a response.end after the 'if request.QueryString("Func") = "Add"' statement to see if the Func querystring was being sent, but it didn't show that, so I can only assume it isn't refreshing the cart.asp page properly.

Can anyone help me out here? I'm quite new to AJAX/Javascript, but am ok with ASP...

ChrisHirst · Nov 2, 2010

the XMLHttpRequest Object will only work for one call then it has to be reset.

http://ajaxian.com/archives/reusing-xmlhttprequest-without-abort

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

tsuji · Nov 7, 2010

[0] >><button type="button" onclick="RefreshCart('Add', <% response.Write(RsProducts("ProductID")) %> , 1)">Add to Cart</button>
The attribute type is pointless and should be taken out.

[1] The main problem that I see (assuming no competing xmlhttp call...) is that the table Cart has never a role of ASPSessionID having the sessionID as value initially. That row is not established during the inital call to the page with 3 colored blocks in the post. (Confirm if that is wrong, but I don't see anywhere there to establish the data in the table Cart.)

Hence, when cart.asp is called via xmlhttp by clicking the button, this loop will never lead to IntCartID <> 0.
[tt]
IntCartID = 0

'Create Cart ID if it doesn't exist
do while IntCartID = 0
Set RsCart = cmd.Execute

with RsCart
if .eof then
StrSQL = "INSERT INTO Cart(ASPSessionID) " & _
" VALUES(" & session.SessionID & ")"
objConn.execute StrSQL
else
IntCartID = RsCart("CartID")
end if
end with
loop
[/tt]
The reason is that when loop the first time, it insert a new row with AspSessionID column populated, but none the other possible columns. Starting from the 2nd time it loops, it finds the rows, but IntCartID is always null or default valued (probably 0 again)... Hence the loop is an infinite loop. The conditional on request.QueryString("Func")="Add" is never executed because it is still looping...

[2] Hence, I would say: Make sure when the first asp is called, the AspSessionID is populated in the table Cart... with CartID column containing some meaningfull data (<>0)?

TimBiesiek · Nov 7, 2010

Worked this problem out - It seems to have been caching the asp trolley page, so I made sure that it didn't cache it and all seems to work really well now! Also had to change the XMLHttpRequest Object so that it was refreshed after each call as mentioned by ChrisHirst.

tsuji - No, it is not an infinite loop, that bit works well. It first inserts the ASPSessionID value, and then refreshes the table. When it loops through the 2nd time, the eof is false, so it sets IntCartID to the CartID, which is an auto incrementing ID column, thus IntCartID is NOT always 0.

Thanks for the posts guys!

tsuji · Nov 7, 2010

Ok, I take your words for it.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

ASP Page to update database

TimBiesiek

Programmer

ChrisHirst

IS-IT--Management

tsuji

Technical User

TimBiesiek

Programmer

tsuji

Technical User

Similar threads

Part and Inventory Search

Sponsor