asked on

ASP Issue when using csv attachment

hi all,

i am having an issue where we have a stock system where customers can pick stock and then we receive a csv file containing exactly what they pick.

my issue is as follows, i can make it work so it sends a csv for a single pick (by taking out *While (NOT rcsCart.EOF)

rcsCart.MoveNext()
Wend
*

but when that line is in it, the csv sends but the order is not processed.

attached is the code, if anyone could have a look and tell me where i am going wrong that would be most appreciated!

thanks

<%
Function sendUKMail(fromWho,toWho, strSubject)

strrname = Server.HTMLEncode(Trim(Replace((Request.Querystring("basketno")),""""," ")))
strtxtsave = "D:\websites\northwayweb.co.uk\wwwroot\goms.co.uk\www\GlobalStockControl\picks\" & strrname & ".csv"
stockref = trim(rcsCart.Fields.Item("stockref").Value)
jobno = trim(rcsCart.Fields.Item("jobno").Value)
qty = CartGetQtyForItem(trim(rcsCart.Fields.Item("stockref").Value) & trim(rcsCart.Fields.Item("jobno").Value))

Set fileObj=Server.CreateObject("Scripting.FileSystemObject")
set file1=fileObj.CreateTextFile(strtxtsave)
While (NOT rcsCart.EOF)
file1.WriteLine ("1," & s(HTMLDecode(ACCOUNTNO)) & "," & _
FormatDateTime(Date(), 0) & " " & FormatDateTime(Time(), 4) & "," & _
s(HTMLDecode(stockref)) & "," & s(HTMLDecode(jobno)) & "," & basketno & "," _
& qty & "," & s(HTMLDecode(SH_ACCONTACT)) & "," & s(HTMLDecode(SH_ACTEL)) & _
"," & s(HTMLDecode(SH_ACFAX)) & "," & s(HTMLDecode(SH_SPECIAL_INSTRUCTIONS)) _
& "," & s(HTMLDecode(SH_TIME)) & "," & s(SH_DATE) & "," & altshipflag & "," _
& s(HTMLDecode(SH_ACCLIENT)) & "," & s(HTMLDecode(SH_ACAD1)) & "," & _
s(HTMLDecode(SH_ACAD2)) & "," & s(HTMLDecode(SH_ACAD3)) & "," & _
s(HTMLDecode(SH_ACAD4)) & "," & s(HTMLDecode(SH_ACCONTACT)) & "," & _
s(HTMLDecode(SH_ACTEL)) & "," & s(HTMLDecode(SH_ACFAX)) & "," & _
s(HTMLDecode(CLIENT_REF)) & "," & s(HTMLDecode(JOB_NO)) & "," & _
s(HTMLDecode(COST_CENTRE)) & "," & s(HTMLDecode(SH_NOTES)) & "," & _
s(HTMLDecode(ACCONTACT)) & "," & s(HTMLDecode(ACTEL)) & "," & _
s(HTMLDecode(ACEMAIL)) & ",") 
rcsCart.MoveNext()
Wend
file1.Close

set file1=nothing
set fileObj=nothing

strMessage = strMessage & "CSV Import for pick list " & chr(10)



strInternalMessage = strMessage

'strHost = "83.170.119.187"

' Customer Email
  Set Mail = Server.CreateObject("CDO.Message")
  'Mail.Host = strHost
    Mail.From = "colin@colin-northway.com" ' From address 
    Mail.Subject = "Picklist" &  Server.HTMLEncode(Trim(Replace((Request.Querystring("basketno")),""""," ")))
	Mail.AddAttachment strPath & "D:\websites\northwayweb.co.uk\wwwroot\goms.co.uk\www\GlobalStockControl\picks\" & Server.HTMLEncode(Trim(Replace((Request.Querystring("basketno")),""""," "))) & ".csv"   

Mail.TextBody = strInternalMessage

'Mail.send
'confirmation email to me for testing 
Mail.To = "colin@colin-northway.com"
Mail.To = "colin@colin-northway.com"	
 Mail.send

End Function %>

Open in new window

Bardobrave

Your code is a bit confusing to me.

You're looping through what I assume is an ADO Recordset (rcsCart), however inside the loop you don't access any of it's elements.

Maybe the recordset doesn't has as many results as you think or it's opened in some way that do not allow you to iterate through it.

The first I'd try would be to identify if code is reaching into the loop and how many loops it's doing.
add a file1.WriteLine("NEW ITERATION") just after the while.

If you do not obtain any "NEW ITERATION" lines in your file try to print rcsCart.RecordCount to see how many records it has... maybe this will give you some idea of what is happening.

gaa18

ASKER

basically all it should be doing is loading a basket contents into a csv, it does it for the first item as long as i dont use:
While (NOT rcsCart.EOF)

rcsCart.MoveNext()
Wend

when i place this in there it creates the csv file however it wont process any further!

Bardobrave

Yes... but what contains rcsCart and why you don't acces anyone of it's fields inside the loop?

gaa18

ASKER

because im taking them from variables elsewhere! i only use the rcscart in another part....

the problem is defo with trying to pull the next series of data or with me closing it off incorrectly!

heres the full page of code:

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Includes/adovbs.inc" -->
<!--#include file="Includes/functions.asp" -->
<!--#include file="Includes/constants.asp" -->

<%
If Not IsCustomerLoggedIn() Then
	Response.Redirect(DEFAULT_REDIRECT_PAGE)
End If

'INITIAL SECURITY CHECKS

'Redirect to shipping form if no items are in the cart and came to this page accidentally or by typing the url. 
'NB. During the normal process flow you cannot arrive to this page if the cart is empty
''If Not CartGetNumberOfItems() > 0 Then 
''Response.Redirect("shipping_form.asp")
''End If


Dim basketno, altshipflag
Dim ACCONTACT, ACTEL, ACEMAIL, ACCOUNTNO, SH_ACCLIENT, SH_ACCONTACT, SH_ACTEL, SH_ACFAX, SH_ACAD1, SH_ACAD2, SH_ACAD3, SH_ACAD4
Dim SH_DAY, SH_MONTH, SH_YEAR, SH_TIME, SH_NOTES, SH_DATE, SH_SPECIAL_INSTRUCTIONS, CLIENT_REF, JOB_NO, COST_CENTRE

SH_SPECIAL_INSTRUCTIONS = Server.HTMLEncode(Trim(Replace((Request.Querystring("SH_SPECIAL_INSTRUCTIONS")),""""," ")))
ACCONTACT = Server.HTMLEncode(Trim(Replace((Request.Querystring("ACCONTACT")),""""," ")))
ACTEL = Server.HTMLEncode(Trim(Replace((Request.Querystring("ACTEL")),""""," ")))
ACEMAIL = Server.HTMLEncode(Trim(Replace((Request.Querystring("ACEMAIL")),""""," ")))
ACCOUNTNO = Server.HTMLEncode(Trim(Replace((Request.Querystring("ACCOUNTNO")),""""," ")))
SH_ACCLIENT = Server.HTMLEncode(Trim(Replace((Request.Querystring("SH_ACCLIENT")),""""," ")))
SH_ACCONTACT = Server.HTMLEncode(Trim(Replace((Request.Querystring("SH_ACCONTACT")),""""," ")))
SH_ACTEL = Server.HTMLEncode(Trim(Replace((Request.Querystring("SH_ACTEL")),""""," ")))
'SH_ACFAX = Server.HTMLEncode(Trim(Replace((Request.Querystring("SH_ACFAX")),""""," ")))
SH_ACFAX = ""
SH_ACAD1 = Server.HTMLEncode(Trim(Replace((Request.Querystring("SH_ACAD1")),""""," ")))
SH_ACAD2 = Server.HTMLEncode(Trim(Replace((Request.Querystring("SH_ACAD2")),""""," ")))
SH_ACAD3 = Server.HTMLEncode(Trim(Replace((Request.Querystring("SH_ACAD3")),""""," ")))
SH_ACAD4 = Server.HTMLEncode(Trim(Replace((Request.Querystring("SH_ACAD4")),""""," ")))
SH_TIME = Server.HTMLEncode(Trim(Replace((Request.Querystring("SH_TIME")),""""," ")))
SH_DATE = Server.HTMLEncode(Trim(Replace((Request.Querystring("SH_DATE")),""""," ")))
SH_NOTES = Server.HTMLEncode(Trim(Replace((Request.Querystring("SH_NOTES")),""""," ")))
basketno = Server.HTMLEncode(Trim(Replace((Request.Querystring("basketno")),""""," ")))

CLIENT_REF = Server.HTMLEncode(Trim(Replace((Request.Querystring("CLIENT_REF")),""""," ")))
JOB_NO = Server.HTMLEncode(Trim(Replace((Request.Querystring("JOB_NO")),""""," ")))
COST_CENTRE = Server.HTMLEncode(Trim(Replace((Request.Querystring("COST_CENTRE")),""""," ")))

' Clean up notes
SH_NOTES = replace(SH_NOTES,","," ")
If trim(SH_NOTES) = "Please note special instructions is limited to 80 characters." Then
	SH_NOTES = ""
End If

altshipflag = Ternary((AltAddressExists(ACCOUNTNO, SH_ACCLIENT, SH_ACAD1, SH_ACAD2, SH_ACAD3, SH_ACAD4)), "yes", "no")

dim x
If altshipflag = "no" Then
	x = AltAddressInsert(ACCOUNTNO, SH_ACCLIENT, SH_ACAD1, SH_ACAD2, SH_ACAD3, SH_ACAD4)
End If


  Set rcsClient = Server.CreateObject("ADODB.Recordset")
  rcsClient.ActiveConnection = FP_DB_CLIENTS_LIVE
  rcsClient.Source = "SELECT * FROM clients WHERE accountno = '" & Session("MM_ACNO") & "'"
  rcsClient.CursorType = 0
  rcsClient.CursorLocation = 2
  rcsClient.LockType = 3
  rcsClient.Open

If CartGetNumberOfItems() > 0 Then  
	Set rcsCart = Server.CreateObject("ADODB.Recordset")
	rcsCart.ActiveConnection = DB_TRANSIT()
	rcsCart.Source = "SELECT * FROM transport where (trim(stockref)+trim(jobno)) in (" & CartIdsAsString() & ")"
	rcsCart.CursorType = 0
	rcsCart.CursorLocation = 2
	rcsCart.LockType = 3
	'rcsCart.CursorType = adOpenStatic
	'rcsCart.CursorLocation = adUseClient
	'rcsCart.LockType = adLockReadOnly  
	rcsCart.Open
	'rcsCart.ActiveConnection = Nothing
End If

process_status = sendUKMail("Global Stock <it@colin-northway.com>","it@colin-northway.com", "Picklist attachment alert")

%>



<html>
<head>
<title>Global Options</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript">
<!--
var statusmsg=""
function hidestatus(){
window.status=statusmsg
return true
}
//-->
</script>
<link href="../global_options.css" rel="stylesheet" type="text/css">
<STYLE type="text/css">

.Turq {  background-color: #33FFFF; font-weight: bold}
.Yellow {  background-color: #FFFF00; font-weight: bold}
div.break {page-break-before:always}
<!--
A {
color:#000000;
 text-decoration:none }
A:hover { color:red }
.HAWBTitle {  font-family: Arial, Helvetica, sans-serif; font-size: 38px; font-weight: bold}
.AddressBody {  font-family: Arial, Helvetica, sans-serif; font-size: 16px; font-weight: bold}
.printbox {
	left: 1in;
	top: 1in;
	right: 1in;
	bottom: 1in;
}
-->
</STYLE>
</head>
<body bgcolor="#FFFFFF" text="#000000">


<table border="0" cellpadding="3" cellspacing="0" bordercolor="#C0C0C0" width="95%" style='border-collapse:collapse;border:none;mso-border-alt:solid windowtext .5pt; mso-yfti-tbllook:480;mso-padding-alt:0in 5.4pt 0in 5.4pt;mso-border-insideh: .5pt solid windowtext;mso-border-insidev:.5pt solid windowtext'>
                            <tr>
                              <td>
                                <table width="511"  border="0" cellpadding="0" cellspacing="0">
                                  <tr>
                                    <td height="15" colspan="7"></td>
                                  </tr>
                                  <tr>
                                    <td width="3%" height="22"><img src="../images/misc/spacer.gif" width="15" height="8"></td>
                                    <td colspan="6"><table width="100%"  border="0" cellspacing="0" cellpadding="0">
                                        <tr>
                                          <td width="97%"><img src="../images/stock_control/shipment_details.gif" width="218" height="21"></td>
                                          <td>&nbsp;</td>
                                        </tr>
                                    </table></td>
                                  </tr>
                                  <tr>
                                    <td height="10" colspan="7"></td>
                                  </tr>
                                  <tr>
                                    <td height="10" colspan="7"><table width="507" border="0" cellspacing="0" cellpadding="0">
                                      <tr>
                                        <td width="16">&nbsp;</td>
                                        <td width="283" class="bodystyle"><strong>Items will be sent to: </strong><br>
                                          <%=SH_ACCLIENT %>, <%=SH_ACAD1 %>, <%=SH_ACAD2 %>, <%=SH_ACAD3 %>, <%=SH_ACAD4 %></td>
                                        <td width="15">&nbsp;</td>
                                        <td width="193" valign="top"><span class="bodystyle"><strong>Reference: </strong><%=basketno%> <strong><br>
  										Date &amp; Time of delivery: </strong><br><%=SH_DATE %>&nbsp;<%=SH_TIME %></span></td>
                                      </tr>
                                    </table></td>
                                  </tr>
                                  <tr>
                                    <td height="10" colspan="7"></td>
                                  </tr>
                                  <tr>
                                    <td height="1"></td>
                                    <td colspan="6" background="../images/bookings/slive_bgd.gif" height="1"></td>
                                  </tr>
                                  <tr>
                                    <td colspan="7" height="5"></td>
                                  </tr>
                                  <tr>
                                    <td height="19">&nbsp;</td>
                                    <td valign="top" class="bodystyleBold">Name &amp; description</td>
                                    <td valign="top" class="bodystyleBold">&nbsp;</td>
                                    <td><span class="bodystyleBold">Quantity</span></td>
                                    <td>&nbsp;</td>
                                    <td width="27%"><span class="bodystyleBold">Stock levels </span></td>
                                        <td width="25%"><span class="bodystyleBold">Reorder 
                                          level </span></td>
                                  </tr>
                                  <tr>
                                    <td height="5" colspan="7"></td>
                                  </tr>
                                  <tr>
                                    <td height="1"></td>
                                    <td colspan="6" background="../images/bookings/slive_bgd.gif" height="1"></td>
                                  </tr>
								  
								  
								  
								  
								  
                                  <tr>
                                    <td height="10" colspan="7"></td>
                                  </tr>
								  
<% 
While (NOT rcsCart.EOF)

%>												  
								  
								  
                                  <tr>
                                    <td valign="top">&nbsp;</td>
                                    <td width="28%" valign="top" class="bodystyle"><strong><%=rcsCart.Fields.Item("jobno").Value%></strong><br>(<%=trim(rcsCart.Fields.Item("type").Value)%>)<br>
   									</td>
                                    <td width="2%" valign="top" class="bodystyle">&nbsp;</td>
                                    <td width="12%" valign="top" class="bodystyle"><%
							  If CartItemExists(trim(rcsCart.Fields.Item("stockref").Value) & trim(rcsCart.Fields.Item("jobno").Value)) Then
								Response.Write(CartGetQtyForItem(trim(rcsCart.Fields.Item("stockref").Value) & trim(rcsCart.Fields.Item("jobno").Value)))
								
								Dim stockref, jobno, qty, output_string
								
								stockref = trim(rcsCart.Fields.Item("stockref").Value)
								jobno = trim(rcsCart.Fields.Item("jobno").Value)
								qty = CartGetQtyForItem(trim(rcsCart.Fields.Item("stockref").Value) & trim(rcsCart.Fields.Item("jobno").Value))
								
								output_string = "1," & s(HTMLDecode(ACCOUNTNO)) & "," & FormatDateTime(Date(), 0) & " " & FormatDateTime(Time(), 4) & "," & s(HTMLDecode(stockref)) & "," & s(HTMLDecode(jobno)) & "," & basketno & "," & qty & "," & s(HTMLDecode(SH_ACCONTACT)) & "," & s(HTMLDecode(SH_ACTEL)) & "," & s(HTMLDecode(SH_ACFAX)) & "," & s(HTMLDecode(SH_SPECIAL_INSTRUCTIONS)) & "," & s(HTMLDecode(SH_TIME)) & "," & s(SH_DATE) & "," & altshipflag & "," & s(HTMLDecode(SH_ACCLIENT)) & "," & s(HTMLDecode(SH_ACAD1)) & "," & s(HTMLDecode(SH_ACAD2)) & "," & s(HTMLDecode(SH_ACAD3)) & "," & s(HTMLDecode(SH_ACAD4)) & "," & s(HTMLDecode(SH_ACCONTACT)) & "," & s(HTMLDecode(SH_ACTEL)) & "," & s(HTMLDecode(SH_ACFAX)) & "," &  s(HTMLDecode(CLIENT_REF)) & ","  & s(HTMLDecode(JOB_NO)) & ","  & s(HTMLDecode(COST_CENTRE)) & "," & s(HTMLDecode(SH_NOTES)) & "," & s(HTMLDecode(ACCONTACT)) & "," & s(HTMLDecode(ACTEL)) & ","  & s(HTMLDecode(ACEMAIL)) & ","  

								
								Dim temp_qty
								temp_qty = CartGetQtyPendingOnFile(rcsCart.Fields.Item("stockref").Value, rcsCart.Fields.Item("jobno").Value, CInt(rcsCart.Fields.Item("qtypending").Value))




								CartToOrder(output_string)

							  End if
							  %></td>
                                    <td width="3%" valign="top" class="bodystyle">&nbsp;</td>
                                    <td class="bodystyle"><table width="66%"  border="0" cellspacing="0" cellpadding="0">
                                        <tr>
                                          <td width="57%" class="bodystyle">In stock </td>
                                          <td width="9%" class="bodystyle">&nbsp;</td>
                                          <td width="34%" class="bodystyle"><%=rcsCart.Fields.Item("qtyonhand").Value%></td>
                                        </tr>
                                        <tr>
                                          <td class="bodystyle">Pending</td>
                                          <td class="bodystyle">&nbsp;</td>
                                          <td class="bodystyle"><%=temp_qty%></td>
                                        </tr>
                                    </table></td>
                                    <td valign="top" class="bodystyle"><%=rcsCart.Fields.Item("orderlevel").Value%></td>
                                  </tr>
                                  <tr>
                                    <td height="10" colspan="7" valign="top"></td>
                                  </tr>
                                  <tr>
                                    <td height="1" valign="top"></td>
                                    <td height="1" colspan="6" valign="top" background="../images/bookings/slive_bgd.gif"></td>
                                  </tr>
								  
<%
rcsCart.MoveNext()
Wend
%>													  
								  
								  
                                  <tr>
                                    <td height="15" colspan="7" valign="top"></td>
                                  </tr>
                              
                                  <tr>
                                    <td height="1" valign="top"></td>
                                    <td height="20" colspan="6" valign="top"></td>
                                  </tr>
                                </table>
							</td>
                            </tr>
</table>

</body>
</html>

<%
Function sendUKMail(fromWho,toWho, strSubject)

strrname = Server.HTMLEncode(Trim(Replace((Request.Querystring("basketno")),""""," ")))
strtxtsave = "D:\websites\northwayweb.co.uk\wwwroot\goms.co.uk\www\GlobalStockControl\picks\" & strrname & ".csv"
stockref = trim(rcsCart.Fields.Item("stockref").Value)
jobno = trim(rcsCart.Fields.Item("jobno").Value)
qty = CartGetQtyForItem(trim(rcsCart.Fields.Item("stockref").Value) & trim(rcsCart.Fields.Item("jobno").Value))

Set fileObj=Server.CreateObject("Scripting.FileSystemObject")
set file1=fileObj.CreateTextFile(strtxtsave)
While (NOT rcsCart.EOF)
file1.WriteLine ("1," & s(HTMLDecode(ACCOUNTNO)) & "," & _
FormatDateTime(Date(), 0) & " " & FormatDateTime(Time(), 4) & "," & _
s(HTMLDecode(stockref)) & "," & s(HTMLDecode(jobno)) & "," & basketno & "," _
& qty & "," & s(HTMLDecode(SH_ACCONTACT)) & "," & s(HTMLDecode(SH_ACTEL)) & _
"," & s(HTMLDecode(SH_ACFAX)) & "," & s(HTMLDecode(SH_SPECIAL_INSTRUCTIONS)) _
& "," & s(HTMLDecode(SH_TIME)) & "," & s(SH_DATE) & "," & altshipflag & "," _
& s(HTMLDecode(SH_ACCLIENT)) & "," & s(HTMLDecode(SH_ACAD1)) & "," & _
s(HTMLDecode(SH_ACAD2)) & "," & s(HTMLDecode(SH_ACAD3)) & "," & _
s(HTMLDecode(SH_ACAD4)) & "," & s(HTMLDecode(SH_ACCONTACT)) & "," & _
s(HTMLDecode(SH_ACTEL)) & "," & s(HTMLDecode(SH_ACFAX)) & "," & _
s(HTMLDecode(CLIENT_REF)) & "," & s(HTMLDecode(JOB_NO)) & "," & _
s(HTMLDecode(COST_CENTRE)) & "," & s(HTMLDecode(SH_NOTES)) & "," & _
s(HTMLDecode(ACCONTACT)) & "," & s(HTMLDecode(ACTEL)) & "," & _
s(HTMLDecode(ACEMAIL)) & ",") 
rcsCart.MoveNext()
Wend
file1.Close

set file1=nothing
set fileObj=nothing

strMessage = strMessage & "CSV Import for pick list " & chr(10)



strInternalMessage = strMessage

'strHost = "83.170.119.187"

' Customer Email
  Set Mail = Server.CreateObject("CDO.Message")
  'Mail.Host = strHost
    Mail.From = "colin@colin-northway.com" ' From address 
    Mail.Subject = "Picklist" &  Server.HTMLEncode(Trim(Replace((Request.Querystring("basketno")),""""," ")))
	Mail.AddAttachment strPath & "D:\websites\northwayweb.co.uk\wwwroot\goms.co.uk\www\GlobalStockControl\picks\" & Server.HTMLEncode(Trim(Replace((Request.Querystring("basketno")),""""," "))) & ".csv"   

Mail.TextBody = strInternalMessage

'Mail.send
'confirmation email to me for testing 
Mail.To = "colin@colin-northway.com"
Mail.To = "colin@colin-northway.com"	
 Mail.send

End Function %>
<%

	If CartGetNumberOfItems() > 0 Then	
		CartClear()
		rcsCart.Close()
		Set rcsCart = Nothing
		Set Recordset1 = Nothing
	End If
	
	rcsClient.Close()
	Set rcsClient = Nothing

%>

Open in new window

Bardobrave

Let's see...

First of all... if rcsCart contains all your elements and you store it's field's values on variables out of your loop and use this variables to write data to your file, in the best of cases, you'll obtain a file with X lines of the same results (being X the number of records on your recordset), as your variables doesn't change it's value with every loop's iteration.

Now let's see what is happening to your recordset iteration... I supose there's no error on the response, so we need to know if recordset is at EOF and how many records it has before starting to loop.

put
Response.write rcsCart.EOF & " - " & rcsCart.RecordCount

before your while, execute the page and put here what values it returns.

gaa18

ASKER

i get:

False - -1

Bardobrave

Your proccess runs well when there's no loop and you don't get any error when loop is present.

Your recordset recordcount before starting loop is 1, this means there's only one record in it left when code's executed.

I'm assuming that CSV file arrives empty when you executes code with loop. Is that correct or you're receiving same result as when loop is not present? (a CSV with only one record/line of data I presume)

gaa18

ASKER

i have noticed that even with the loop i get the correct amount of lines however with duplicate data?

i really dont know where im going wrong :'(

Bardobrave

I explained that behaviour before, it's due to your variables.

You store your recordset values in variables out of the loop, then you loop over the recordset, but your values don't change values (because they are set out of the loop) so you loop X times over your recordset but always post same values to your file.

gaa18

ASKER

so how can i solve it?

gaa18

ASKER

the only variables that will be different on each line would be:

stockref, jobno, quantity

how can i approach it now?

ASKER CERTIFIED SOLUTION

Bardobrave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gaa18

ASKER

issue was related to where the loop was starting.... thanks for your help