how to loop through 2 of same products?

jarv

01-20-2010, 09:29 AM

ok, in my shop database, if someone buys 2 (or more) of the same product it then shows in QTY field as 2 or however many they have bought.

how do i write out(loop through) the products that are duplicated?

my code is below:

<%Dim decodeOfficeType, objField, GetStatus%>
<%
Set rs = Server.CreateObject("ADODB.Recordset")
OrderNumber = request.querystring("ONumber")
rs.ActiveConnection = MM_CON_Database_STRING
rs.source = "SELECT tblCustomerRecords.*, tblCustomerOrders.* FROM tblCustomerRecords LEFT JOIN tblCustomerOrders ON " & _
"tblCustomerRecords.OrderNumber = tblCustomerOrders.OrderNumber WHERE tblCustomerRecords.OrderNumber=" & OrderNumber
rs.open()
%>

</head>
<body>

<%' while not rs.eof %>
<%
Dim OrdStat
OrdStat = "x"
For Each objField In rs.fields
Response.Write "<strong>" & objField.name & ":</strong> " & objField.value & " <span style=""color:blue;"">|</span> "
If objField.name = "OrderStatus" Then OrdStat = objField.value
Next
Response.Write "<br /><a href=""del_note.asp?id="&rs("tblCustomerRecords.id")&""">" & rs("tblCustomerRecords.OrderNumber") &"</a>"
%>

<div id="wrapper">
<div id="top"><img src="../../images/build/Delnote_Header.png" /></div>
<div class="deladdress">
DELIVERY ADDRESS<br /><br />
<%response.write(rs("ShippingNameTitle")&" "&rs("ShippingFirstName")&" "&rs("ShippingFirstName")&"<br />"& _
rs("ShippingAddress1")&"<br />"& _
rs("ShippingAddress2")&"<br />"& _
rs("ShippingCity")&"<br />"& _
rs("ShippingCounty")&"<br />"& _
rs("ShippingPostal")&"<br />"& _
rs("ShippingCountry"))%>
</div>
<div class="date1"><%=FormatDateTime(rs("DateOrdered"),vblongdate)%></div>
<div class="billaddress">
BILLING ADDRESS<br /><br />
<%response.write(rs("BillingNameTitle")&" "&rs("BillingFirstName")&" "&rs("BillingFirstName")&"<br />"& _
rs("BillingAddress1")&"<br />"& _
rs("BillingAddress2")&"<br />"& _
rs("BillingCity")&"<br />"& _
rs("BillingCounty")&"<br />"& _
rs("BillingPostal")&"<br />"& _
rs("BillingCountry"))%>
</div>
<br />
<table ID="table" border="1" width="98%" align="center" style="margin-top:10px; margin-bottom:10px;">
<tr>
<td colspan="5">YOUR ITEMS</td>
</tr>
<tr>
<td colspan="5"> </td>
</tr>
<tr>
<td>Code</td>
<td>Colour</td>
<td>Size</td>
<td>Description</td>
<td>Price</td>
</tr>
<% while not rs.eof %>
<tr>
<td><%=rs("descriptionofgoods")%></td>
<td><%=rs("option2")%></td>
<td><%=rs("option1")%></td>
<td><%=rs("descriptionofgoods")%></td>
<td><%=rs("itemPrice")%></td>
</tr>
<% rs.MoveNext() :: wend %>
<tr>
<td></td>
<td></td>
<td></td>
<td><style="text-align:right;"><strong>Sub Total<br /><br />Delivery<br /><br />Total</strong></style></td>
<td><%=rs("LineTotal")%></td>
</tr>
</table>
<div id="footer"></div>
</div>
<%' rs.MoveNext() :: wend %>
</body>
</html>

Also, Where I have: <%=rs("LineTotal")%> this is in the database but not showing?! I get the error: ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/paddycampbell.www.co.uk/www/office/orders/del_note.asp, line 0

jarv

01-20-2010, 01:17 PM

please help

jarv

01-20-2010, 03:16 PM

ok, here is the FOR LOOP to add the items purchased, I need to modify this based on the QTY

So, INSERT INTO tblCustomerOrders based on the number in the QTY field!

For UCCart1__i=0 To UCCart1.GetItemCount()-1
sql = "INSERT INTO tblCustomerOrders (OrderNumber, [qty], [productCode], [descriptionofgoods], [option1], [option2], [itemPrice], [lineTotal]) VALUES ("
sql = sql & "" & replace(session("OrderNumber"),"'","''") & ", "
sql = sql & "" & UCCart1.GetColumnValue("Quantity",UCCart1__i) & ", "
sql = sql & "'" & replace(UCCart1.GetColumnValue("ProductID",UCCart1__i),"'","''") & "', "
sql = sql & "'" & replace(UCCart1.GetColumnValue("Name",UCCart1__i),"'","''") & "', "
sql = sql & "'" & replace(UCCart1.GetColumnValue("Size",UCCart1__i),"'","''") & "', "
sql = sql & "'" & replace(UCCart1.GetColumnValue("Colour",UCCart1__i),"'","''") & "', "
sql = sql & "" & replace(UCCart1.GetColumnValue("Price",UCCart1__i),"'","''") & ", "
sql = sql & "" & replace(UCCart1.GetColumnValue("Total",UCCart1__i),"'","''") & ")"
conn.open
on error resume Next

Can anyone please help?!

Old Pedant

01-20-2010, 07:44 PM

The problem with rs("LineTotal") is obvious.

From YOUR CODE:
<% while not rs.eof %>
...
<% rs.MoveNext() :: wend %> ...
<td><%=rs("LineTotal")%></td>

You need to MOVE the line in red there to *after* the rs("LineTotal"), of course.

Old Pedant

01-20-2010, 07:50 PM

Looks to me like it is picking up the quantity.

Have you DEBUGGED?????

Have you done a RESPONSE.WRITE of that SQL??

Also, you should *NEVER NEVER NEVER* use On Error Resume Next until you get the basic code working. That should *ONLY* be used as a last resort and ONLY if you have code in place that can fix or report the error when it occurs.

You could be getting lots of errors and your code would just *IGNORE* them. Very very bad when doing development.

Old Pedant

01-20-2010, 07:51 PM

Also, you should do the conn.open *ONE TIME* at the top of the ASP page and then NEVER close the connection and use the same connection for ALL operations on the page.

jarv

01-20-2010, 09:06 PM

hi, yes it is picking up the QYT but only inserting each record once not twice if teh QTY is 2 or 3 times if WTY is 3?!

Old Pedant

01-20-2010, 09:49 PM

??? It should *NOT* insert more than ONE TIME!!!!

That what the [qty] field in the database is FOR!

INSERT INTO tblCustomerOrders (OrderNumber, [qty], [productCode], ...

*THAT* is what tells you, later when you display the data, HOW MANY were ordered.

It would be a very very silly and (sorry) brain-dead system if it *DID* add one record for each number in the quantity!

Imagine somebody orders 3,432 of something. Would you *REALLY* want to add 3,432 records to the DB?????

Old Pedant

01-20-2010, 09:55 PM

You REAL problem is that you don't DISPLAY the quantity in the other page/code!

You *NEED* to add it in!

<tr>
<td>Code</td>
<td>Colour</td>
<td>Size</td>
<td>Description</td>
<td>Quantity</td>
<td>Price</td>
</tr>
<% while not rs.eof %>
<tr>
<td><%=rs("descriptionofgoods")%></td>
<td><%=rs("option2")%></td>
<td><%=rs("option1")%></td>
<td><%=rs("descriptionofgoods")%></td>
<td><%=rs("qty")%></td>
<td><%=FormatCurrency(rs("itemPrice"))%></td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td style="text-align:right; font-weight: bold;">Sub Total</td>
<td><%=FormatCurrency(rs("LineTotal")%></td>
</tr>
<% rs.MoveNext() :: wend %>

Your use of
<br/><br/>Dellivery<br/>Total
in there makes NO SENSE at all.

Each line item *should* have a separate subtotal.

You then *ALSO* need to show the total, but only *AFTER* all the line items.

jarv

01-20-2010, 10:45 PM

You REAL problem is that you don't DISPLAY the quantity in the other page/code!

You *NEED* to add it in!

Your use of
<br/><br/>Dellivery<br/>Total
in there makes NO SENSE at all.

I am working on the delivery note! for each Order Number Also, yes I do need to display all records?!

nobody will buy 2,478 dresses they are really expensive!

jarv

01-20-2010, 10:46 PM

ok say I want to keep it as it is, QTY = 2

how would i show that other item as a duplicate?

Old Pedant

01-20-2010, 10:51 PM

My head hurts.

*WHAT* "other item"???