Need URGENT help with all items not showing in email

humbletechie · Aug 27, 2002

The code that sends the email, only list the first item in the cart, the cart items are pulled from an access database. The items are all in the database but only the first item shows in the email that is sent. Below is the code, can someome please help? Thanks..

<% option explicit %>
<% response.buffer=true %>

<%
sub sendMail(RecipientAddr, From, Subject, Message) 'As String
dim Mail
Set Mail = Server.CreateObject("Persits.MailSender&quot

Mail.Host = "mail.luggagebase.com" ' Specify a valid SMTP server
Mail.From = From ' Specify sender's address, this must be an 'AveHost.com hosted domain name for the email form to fire.
Mail.FromName = "LuggageBase.com Service" ' Specify sender's name

Mail.AddAddress RecipientAddr ' Name is optional
Mail.AddReplyTo From

Mail.Subject = Subject
Mail.Body = Message

On Error Resume Next
Mail.Send
If Err <> 0 Then
Response.Write "Error encountered: " & Err.Description
End If

end sub

dim str

http://WWWRoot,

strDBRoot, strDBName,strConnectionString
'Get the root

http://www path

str

http://WWWRoot

= server.mappath("/&quot

strDBRoot = str

http://WWWRoot

& "\..\data\luggagebase\"
strDBName = "luggage.mdb"
strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBRoot & strDBName
DIM oConn,rs,sql
set oConn= Server.CreateObject("ADODB.connection&quot

set rs=Server.CreateObject("ADODB.recordset&quot

oConn.ConnectionString = strConnectionString
oConn.Open

DIM custid
DIM bfirstname,blastname,bcompany,baddr1,baddr2,bcity,bstate,bzip,bphone,bemail
DIM sfirstname,slastname,scompany,saddr1,saddr2,scity,sstate,szip,sphone
DIM ccnum,cctype,ccmonth,ccyear
DIM TotalRetail,TotalShipping,TotalTax,TotalOrder

custid = CheckForCustID()

bfirstname = trim(request.form("bfirstname&quot

)
blastname = trim(request.form("blastname&quot

)
bcompany = trim(request.form("bcompany&quot

)
baddr1 = trim(request.form("baddr1&quot

)
baddr2 = trim(request.form("baddr2&quot

)
bcity = trim(request.form("bcity&quot

)
bstate = trim(request.form("bstate&quot

)
bzip = trim(request.form("bzip&quot

)
bphone = trim(request.form("bphone&quot

)
bemail = trim(request.form("bemail&quot

)

sfirstname = trim(request.form("sfirstname&quot

)
slastname = trim(request.form("slastname&quot

)
scompany = trim(request.form("scompany&quot

)
saddr1 = trim(request.form("saddr1&quot

)
saddr2 = trim(request.form("saddr2&quot

)
scity = trim(request.form("scity&quot

)
sstate = trim(request.form("sstate&quot

)
szip = trim(request.form("szip&quot

)
sphone = trim(request.form("sphone&quot

)

cctype = trim(request.form("cardtype&quot

)
ccnum =trim(request.form("ccnum&quot

)
ccmonth =trim(request.form("ccmonth&quot

)
ccyear =trim(request.form("ccyear&quot

)

TotalRetail = trim(request.form("totalretail&quot

)
TotalShipping = trim(request.form("totalshipping&quot

)
TotalTax = trim(request.form("totaltax&quot

)
TotalOrder = trim(request.form("totalorder&quot

)

'first we update billing info
sql = "update customers set "
sql = sql & " [firstname]='"&bfirstname&"', "
sql = sql & " [lastname]='"&blastname&"', "
sql = sql & " [company]='"&bcompany&"', "
sql = sql & " [addr1]='"&baddr1&"', "
sql = sql & " [addr2]='"&baddr2&"', "
sql = sql & " [city]='"&bcity&"', "
sql = sql & " [state]='"&bstate&"', "
sql = sql & " [zip]='"&bzip&"', "
sql = sql & " [phone]='"&bphone&"' where [id]= "&custid&" ;"

'response.write "<br>"&sql&"<br>"

oConn.execute(sql)

dim shiptoid
shiptoid = 0
''if we have shipping info create shipto
if(sfirstname <> "&quot

then
sql = "INSERT INTO cart_shipto ([custid],[firstname],[lastname],[company],[addr1],[addr2],[city],[state],[zip],[phone]) values("
sql = sql &custid &","
sql = sql & "'"&sfirstname&"',"
sql = sql & "'"&slastname&"',"
sql = sql & "'"&scompany&"',"
sql = sql & "'"&saddr1&"',"
sql = sql & "'"&saddr2&"',"
sql = sql & "'"&scity&"',"
sql = sql & "'"&sstate&"',"
sql = sql & "'"&szip&"',"
sql = sql & "'"&sphone&"');"

'response.write "<br>"&sql&"<br>"

oConn.execute(sql)
'now get shiptoid
sql = "select max([id]) as shiptoid from cart_shipto ;"
rs.open sql,oConn
do while not rs.eof
shiptoid = rs("shiptoid&quot

rs.movenext
loop
rs.close()
end if

' now we set up the insert into cart_final checkout
sql = "INSERT INTO Cart_FinalCheckout ([custid],[custshiptoid],[cctype],[ccnum],[ccexp],[totalretail],[totalshipping],[totaltax],[totalorder]) values( "
sql = sql & custid &","
sql = sql & shiptoid &","
sql = sql & "'"&cctype&"',"
sql = sql & "'"&ccnum&"',"
sql = sql & "'"&ccmonth&"/"&ccyear&"',"
sql = sql & TotalRetail &","
sql = sql & TotalShipping &","
sql = sql & TotalTax &","
sql = sql & TotalOrder &&quot

"

'response.write "<br>"&sql&"<br>"

oConn.Execute(sql)
DIM OrderNumber
OrderNumber=0
sql = "select max([ordernum]) as ordernumber from cart_finalcheckout ;"
'response.write "<br>"&sql&"<br>"
rs.open sql,oConn
do while not rs.eof
OrderNumber = rs("ordernumber&quot

rs.movenext
loop
rs.close()

sql = "update cart_items set [ordernumber] = "&OrderNumber&" where [custid]="&custid&" and [ordernumber]=0 ;"
'response.write "<br>"&sql&"<br>"
oConn.Execute(sql)

%>
<html>
<head>
<title>LuggageMan Confirmation Page</title>
</head>
<body bgcolor="#FFFFFF">
<center>






<p>
<table width="640" cellpadding="0" cellspacing="0" border="0">
<tr><td bgcolor="#FF0000"><center><font size="+1" face="Arial" color="#FFFFFF">

Order Confirmation

</font></center></td><td align="right"><IMG SRC="images/noclose.gif" ALIGN=RIGHT WIDTH="80" HEIGHT="10" BORDER="0" HSPACE="0" VSPACE="0"></td></tr>
</table>
<p>
<table width="640" cellpadding="0" cellspacing="0" border="0">
<tr><td valign="top">
<font size="-1" face="Arial">
<b>Order Confirmation</b>
<p>
Thank you for placing your order (<b>Order# <%=OrderNumber%></b>). Your order will be processed and shipped as quickly as possible.
<p>
<table width="640" border="1" cellpadding="1" cellspacing="1">
<tr>
<td colspan="2"><%= bfirstname %> <%= blastname %><br>
<%= bcompany %><br>
<%= baddr1 %><br>
<%= baddr2 %><br>
<%= bcity %>, <%= bstate %> <%= bzip %><br>
<%= bphone %><br>
<%= bemail %><br></td>
</tr>
<tr>
<td colspan="2">
<table width="640" cellpadding="0" cellspacing="0" border="0">
<tr>
<td height="23"><b>Sku</b></td>
<td height="23"><b>Color</b></td>
<td height="23"><b>Desc</b></td>
<td height="23" align="center"><b>Qty</b></td>
<td height="23" align="right"><b>Retail</b></td>
<td height="23" align="right"><b>Shipping</b></td>
<td height="23" align="right"><b>Total</b></td>
<td height="23"> </td>
</tr>
<%

sql = "select cart_items.*,products.* from cart_items INNER JOIN Products ON (Cart_Items.colorcode = Products.ColorCode) AND (Cart_Items.SKu = Products.SKU) where cart_items.[custID] = "& custid &" and cart_items.[ordernumber] = "&OrderNumber
'response.write "<BR>"&sql&"<BR>"
rs.open sql,oConn
DIM productName
do while not rs.eof

productName = trim(rs("productName&quot

)

if(left(productName,1) = """&quot

then
productName = mid(productName,2)
end if
if(right(productName,1) = """&quot

then
productName = mid(productName,1,(len(productName)-1))
end if
if(instr(productName,"""""&quot

> 0) then
productName = Replace(productName,"""""","""&quot

end if

%>
<tr>
<td><%= rs("brandID&quot

%> <%=rs("fullsku&quot

%></td>
<td><%=trim(rs("colordesc&quot

)%></td>
<td><%=productName%></td>
<td align="center"><%=rs("qty&quot

%></td>
<td align="right"><%=formatcurrency(rs("Retail&quot

)%></td>
<td align="right"><%=formatcurrency(rs("cart_items.shipping&quot

)%></td>
<td align="right"><%=formatcurrency( ( rs("Retail&quot

*rs("qty&quot

) + ( rs("cart_items.shipping&quot

*rs("qty&quot

) ) %></td>
<td> </td>
</tr>
<%
rs.movenext
loop

rs.movefirst
rs.Close()

%>
<tr>
<td colspan="5"> </td>
<td align="right">Total Retail</td>
<td align="right"><%=FormatCurrency(TotalRetail)%></td>
<td> </td>
</tr>
<tr>
<td colspan="5"> </td>
<td align="right">Total Shipping</td>
<td align="right"><%=FormatCurrency(TotalShipping)%></td>
<td> </td>
</tr>
<tr>
<td colspan="5"> </td>
<td align="right">Total Tax</td>
<td align="right"><%=FormatCurrency(TotalTax)%></td>
<td> </td>
</tr>
<tr>
<td colspan="5"> </td>
<td align="right">Total Order</td>
<td align="right"><b><%=FormatCurrency(TotalOrder)%></b></td>
<td> </td>
</tr>
</table>

<p>
If you have any questions, please <a href="contactus.asp">contact us</a>.
<p>

</font>
<p>
<center>
<script language="Javascript1.2">
<!--
// please keep these lines on when you copy the source

var message = "Click to Print this Page for your Records";

function printpage() {
window.print();
}

document.write("<form><input type=button "
+"value=\""+message+"\" onClick=\"printpage()\"></form>&quot

;

//-->
</script>
</center></td></tr>




</table>

<%
oConn.close
set oConn = Nothing
set rs = Nothing
' send the email
dim from:from = "sales@luggagebase.com"
dim sendTo:sendTo = bemail
dim subject:subject = "Luggagebase.com - OrderNumber #" & OrderNumber

dim strMessage

strMessage = " "& bfirstname &","& vbCrLf &_
"Thank you for your order! Placed on "& date &"."& vbCrLf & vbCrLf &_
"Your Order Number is "& cstr(OrderNumber) &". "&_
"This number may be used to track the progress of your order "&_
"by contacting our customer service department." & vbCrLf & vbCrLf &_
"Your Name and Address" & vbCrLf &_
bfirstname & " " & blastname & vbCrLf

If Len(bcompany) > 0 Then strMessage = strMessage & bcompany & vbCrLf

strMessage = strMessage & baddr1 & vbCrLf

If Len(baddr2) > 0 Then strMessage = strMessage & baddr2 & vbCrLf

strMessage = strMessage & bcity & ", " & bstate & " " & bzip & vbCrLf
strMessage = strMessage & vbCrLf

If (bfirstname & blastname) <> (sfirstname & slastname) and (sfirstname <> "&quot

Then
strMessage = strMessage & vbCrLf & "Ship-To Name and Address" & vbCrLf &_
sfirstname & " " & slastname & vbCrLf

If Len(scompany) > 0 Then strMessage = strMessage & scompany & vbCrLf

strMessage = strMessage & saddr1 & vbCrLf

If Len(saddr2) > 0 Then strMessage = strMessage & saddr2 & vbCrLf

strMessage = strMessage & scity & ", " & sstate & " " & szip & vbCrLf

strMessage = strMessage & vbCrLf

End If

strMessage = strMessage & vbCrLf & "Your order includes the following items:" & vbCrLf
' 1 2 3 4 5 6 7 8 9 10
'1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
strMessage = strMessage & "Item Number / Description Price Qty " & vbCrLf
'123456789012345678901234567890 99,999.99 12345 678 99,999.99

DIM oConn2,rs2
set oConn2= Server.CreateObject("ADODB.connection&quot

set rs2=Server.CreateObject("ADODB.recordset&quot

oConn2.ConnectionString = strConnectionString
oConn2.Open
rs2.open sql,oConn2,3,3

rs2.movefirst
do while not rs2.eof

productName = trim(rs2("productName&quot

)

if(left(productName,1) = """&quot

then
productName = mid(productName,2)
end if
if(right(productName,1) = """&quot

then
productName = mid(productName,1,(len(productName)-1))
end if
if(instr(productName,"""""&quot

> 0) then
productName = Replace(productName,"""""","""&quot

end if

strMessage = strMessage &_
left( rs2("fullsku&quot

& " / " & productName & space(30),30) & space(2) &_
right( space(10) & formatcurrency(rs2("Retail&quot

),10) &_
right( space(10) & rs2("qty&quot

,10) &_
space(1) & trim(rs2("colordesc&quot

) & vbCrLf &_
" " & vbCr & vbCr

rs2.movenext
loop

rs2.Close()

strMessage = strMessage & "SubTotal: " & right(space(10) & formatcurrency(TotalRetail) ,10) & vbCrLf &_
"Sales Tax: " & right(space(10) & formatcurrency(TotalTax) ,10) & vbCrLf &_
"Shipping: " & right(space(10) & formatcurrency(TotalShipping) ,10) & vbCrLf &_
"Order Total: " & right(space(10) & formatcurrency(TotalOrder),10) & vbCrLf & vbCrLf &_
"FOR YOUR INFORMATION : "& vbCrLf & vbCrLf &_
"* Returns and Exchanges : Go to

http://www.luggagebase.com,

then visit our Customer Service department and choose from the appropriate menu. "& vbCrLf & vbCrLf &_
"Customer Service e-mail address : service@luggagebase.com"& vbCr & vbCr &_
"* Customer Service Hours : "& vbCrLf &_
" 8:30-5:00 PST (Pacific Time)"& vbCrLf &_
"* Off-line Order Desk : "& vbCrLf &_
" United States residents: 1-888-832-1201"& vbCrLf &_
"* All orders may be subject to Voice Authorization" & vbCrLf & vbCrLf &_
"* luggagebase.com retains the right to refuse service to anyone" & vbCrLf & vbCrLf &_
"* The ""List"", and/or ""SRP"" prices associated with many products throughout luggagebase.com are the manufacturer's "&_
"suggested retail prices which are intended to be used for reference only and do not necessarily represent the present "&_
"or former selling price of luggagebase.com or any specific retailer." & vbCrLf & vbCrLf

call SendMail(sendTo, From, Subject, strMessage)

%>

</center>
</body>
</html>

denoxis · Aug 27, 2002

sql = "update cart_items set [ordernumber] = "&OrderNumber&" where [custid]="&custid&" and [ordernumber]=0 ;"

is this the one you're inserting your cart items into the database? It's not in a loop or anything???

humbletechie · Aug 27, 2002

This statement updates the cart with the next available order #. I looked at the database and the order numbers are all being updated without a loop statement. Here is something that might help also, the part that writes out the html receipt prints out everything fine, but only the email is the part that gets cut off after the first cart item.

Thanks in advance!

Tarwn · Aug 27, 2002

Place a print statement inside the loop that creates the email body, perhaps a counter that incrememnts with each loop or somesuch, so that you can see that it is actually going through the whole process.
Also, I noticed with the second OPen statement you are using the flags 3,3 while with the first (working) open you are not using flags, could this be making the differance?
-Tarwn ------------ My Little Dictionary ---------
Reverse Engineering - The expensive solution to not paying for proper documentation

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!

Need URGENT help with all items not showing in email

humbletechie

IS-IT--Management

denoxis

Programmer

humbletechie

IS-IT--Management

Tarwn

Programmer

Similar threads

Part and Inventory Search

Sponsor