CVS Data Extra - Easy points!

Hi All,

Need a bit of help with the following code.

The download function works; having problems with emailing the report as a .csv file attachment.

<%
Dim DSN, RS, SQL, Conn, startdate, enddate, starttime, endtime




startdate = Request.Form("start_month") & "/" & Request.Form("start_day") & "/" & Request.Form("start_year")
enddate = Request.Form("end_month") & "/" & Request.Form("end_day") & "/" & Request.Form("end_year")
starttime = Request.Form("start_hour") & ":" & Request.Form("start_minute")
endtime = Request.Form("end_hour") & ":" & Request.Form("end_minute")

Response.Addheader "Content-Disposition", "inline; filename=ExportSearched.csv"
Response.ContentType = "application/vnd.ms-excel"
DSN="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=E:\domains\0044.uk.com\db\shopping500.mdb"


if isDate(startdate) and isDate(enddate) Then
SQL = "SELECT orders.orderid, orders.ocustomerid, orders.otime, orders.odate, orders.ofirstname, orders.olastname, orders.oaddress, orders.ocity, orders.opostcode, orders.ostate, orders.ocountry, orders.ophone, orders.oshipmethod, orders.oshipname, orders.oshipaddress, orders.oshiptown, orders.oshipzip, orders.oshipstate, orders.oshipcountry, oitems.catalogid, oitems.numitems, oitems.itemname FROM orders INNER JOIN oitems ON orders.orderid = oitems.orderid WHERE orders.odate >= #" & startdate & "# AND orders.odate <= #" & enddate & "# AND orders.otime >= # " & starttime & "# AND orders.otime <= #" & endtime &"# AND orders.opending = 'Pending' AND orders.oprocessed = False"


set Conn=server.createobject("adodb.connection")
Conn.open DSN
set rs = conn.Execute(SQL)

if not rs.eof then
Dim Head
For Each  ID In RS.Fields
Head = Head & ", " &  ID.Name
Next
Head = Mid(Head,3) & vbCrLf

Response.Write Head
Response.Write RS.GetString(,,", ",vbCrLf,"")

mypath = Server.Mappath "temp"
mypath = mypath & "myexcelfile.xls"


Dim Email
Dim MyCDONTSMail
Dim emailrec
Dim msg

emailrec = Request.Form("email")
msg = Response.Write RS.GetString(,,", ",vbCrLf,"")

   Set MyCDONTSMail = CreateObject("CDONTS.NewMail")
   MyCDONTSMail.From= "customer.service@0044.co.uk"
   MyCDONTSMail.To= emailrec
   MyCDONTSMail.Subject="Exported Data"
   MyCDONTSMail.Body= "Data attached"
   MyCDONTSMail.AddAttachment  = mypath
   MyCDONTSMail.Send
   set MyCDONTSMail=nothing



Set RS = nothing
Conn.close
else
Response.Write "No matching rows with the selected date range"
end if


else
Response.Write "Invalid date"
end if

%>

Regards,
Craig
Choice-QuoteAsked:
Who is Participating?
 
sciber_dudeConnect With a Mentor Commented:
Your data was not being sent as the loop already reached rs.EOf

Copy this code and test it out! (see if you receive the email). I am not sure of the relative url to the myexcelfile.csv, so change that if necessary in the code and get rid of the comment (') infront of it.

<%
Dim DSN, RS, SQL, Conn, startdate, enddate, starttime, endtime

Startdate = Request.Form("start_month") & "/" & Request.Form("start_day") & "/" & Request.Form("start_year")
enddate = Request.Form("end_month") & "/" & Request.Form("end_day") & "/" & Request.Form("end_year")
starttime = Request.Form("start_hour") & ":" & Request.Form("start_minute")
endtime = Request.Form("end_hour") & ":" & Request.Form("end_minute")

' Response.Addheader "Content-Disposition", "inline; filename=ExportSearched.csv"
' Response.ContentType = "application/vnd.ms-excel"
' DSN="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=E:\domains\0044.uk.com\db\shopping500.mdb"


if isDate(startdate) and isDate(enddate) Then
      SQL = "SELECT orders.orderid, orders.ocustomerid, orders.otime, orders.odate, orders.ofirstname, orders.olastname, orders.oaddress, orders.ocity, orders.opostcode, orders.ostate, orders.ocountry, orders.ophone, orders.oshipmethod, orders.oshipname, orders.oshipaddress, orders.oshiptown, orders.oshipzip, orders.oshipstate, orders.oshipcountry, oitems.catalogid, oitems.numitems, oitems.itemname FROM orders INNER JOIN oitems ON orders.orderid = oitems.orderid WHERE orders.odate >= #" & startdate & "# AND orders.odate <= #" & enddate & "# AND orders.otime >= # " & starttime & "# AND orders.otime <= #" & endtime &"# AND orders.opending = 'Pending' AND orders.oprocessed = False"
      
      set Conn=server.createobject("adodb.connection")
      Conn.open DSN
      Set rs = conn.Execute(SQL)


      If not (Rs.EOF or rs.BOF) then
            Dim Head, TheData
            For Each  ID In RS.Fields
                  Head = Head & "# " &  ID.Name
            Next
            Head = Mid(Head,3) & vbCrLf
            Do until Rs.EOF
                  for each x in Rs.Fields
                        TheData = TheData & x.value & ", "
                  next
                  TheData = left(TheData, len(TheData)-2) & VbCrLf
            rs.movenext
            loop
      
            Response.Write Head
            Response.Write TheData

            Dim mypath

            mypath = "E:\domains\0044.uk.com\db\"
            mypath = mypath & "myexcelfile.csv"

            Set fs = CreateObject("Scripting.FileSystemObject")
            Set writefile = fs.OpenTextFile(mypath, 8, True)
            writefile.writeline(Head)
            writefile.writeline(TheData)
            writefile.close
            set writefile = nothing
            set fs = nothing

            Dim Email
            Dim MyCDONTSMail
            Dim emailrec
            Dim msg

            emailrec = Request.Form("email")

            Set MyCDONTSMail = CreateObject("CDONTS.NewMail")
            MyCDONTSMail.From= "customer.service@0044.co.uk"
            MyCDONTSMail.To= emailrec
            MyCDONTSMail.Subject="Exported Data"
            MyCDONTSMail.Body= "Data attached"
            MyCDONTSMail.AddAttachment  = mypath
            MyCDONTSMail.Send
            set MyCDONTSMail=nothing

            Set RS = nothing
            Conn.close
            
            ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            ' Redirecting the page after sending email
            ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            
            ' response.redirect "db/myexcelfile.csv"
            
      else
            Response.Write "No matching rows with the selected date range"
      end if

else
      Response.Write "Invalid date"
end if
%>
0
 
sciber_dudeCommented:
> msg = Response.Write RS.GetString(,,", ",vbCrLf,"")

This should be ...

msg = RS.GetString(,,", ",vbCrLf,"")
0
 
Choice-QuoteAuthor Commented:
Sorry; ignore that variable... that shouldn't be there at all.

Craig
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
sciber_dudeCommented:
That is not the whole solution, but probably an issue that might cause problems.
You question still remains unanswered.
:) SD
0
 
dwaldnerCommented:
Shouldn't the file be named .csv and not .xls?

mypath = mypath & "myexcelfile.xls"
0
 
dwaldnerCommented:
Also, could you elaborate on the problem?  Error messages?  Things not working properly?
0
 
sciber_dudeCommented:
@ Choice-Quote -

As I read your question, these are what you want your script to do for which your code needs to be modified.

1. Create / read a .CSV file on your server and write the recordset to it
2. Send it as an email attachment.
3. (?) Delete (the contents of) the .csv file

Am I correct?

> MyCDONTSMail.AddAttachment  = mypath
this line should take care of the attachment.

But there is no script there to write to the .csv file. You will need to know about the FileScriptingObject (FSO). Some information about FSO here... http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21042567.html#11431450

:) SD
0
 
Choice-QuoteAuthor Commented:
Do these lines of code not write the .csv file?

mypath = Server.Mappath "temp"
mypath = mypath & "myexcelfile.csv"
0
 
dwaldnerCommented:
No, that just gives the path of the desired file at that location...

As ScriberDude suggested, you have to write the file first before you can attach it...
0
 
dwaldnerCommented:
Set fs = CreateObject("Scripting.FileSystemObject")
Set writefile = fs.OpenTextFile(mypath, 8, True)
writefile.writeline(stringOfTextToWriteForTheFirstLine)
writefile.writeline(stringOfTextToWriteForTheSecondLine)
writefile.close
set writefile = nothing
set fs = nothing

This will create the file for you, if you adapt it properly.

Dan
0
 
Choice-QuoteAuthor Commented:
Now my code looks like this; I get an errot HTTP 500 Internal Server Error (not much help i know, server is remote)

<%
Dim DSN, RS, SQL, Conn, startdate, enddate, starttime, endtime




startdate = Request.Form("start_month") & "/" & Request.Form("start_day") & "/" & Request.Form("start_year")
enddate = Request.Form("end_month") & "/" & Request.Form("end_day") & "/" & Request.Form("end_year")
starttime = Request.Form("start_hour") & ":" & Request.Form("start_minute")
endtime = Request.Form("end_hour") & ":" & Request.Form("end_minute")

Response.Addheader "Content-Disposition", "inline; filename=ExportSearched.csv"
Response.ContentType = "application/vnd.ms-excel"
DSN="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=E:\domains\0044.uk.com\db\shopping500.mdb"


if isDate(startdate) and isDate(enddate) Then
SQL = "SELECT orders.orderid, orders.ocustomerid, orders.otime, orders.odate, orders.ofirstname, orders.olastname, orders.oaddress, orders.ocity, orders.opostcode, orders.ostate, orders.ocountry, orders.ophone, orders.oshipmethod, orders.oshipname, orders.oshipaddress, orders.oshiptown, orders.oshipzip, orders.oshipstate, orders.oshipcountry, oitems.catalogid, oitems.numitems, oitems.itemname FROM orders INNER JOIN oitems ON orders.orderid = oitems.orderid WHERE orders.odate >= #" & startdate & "# AND orders.odate <= #" & enddate & "# AND orders.otime >= # " & starttime & "# AND orders.otime <= #" & endtime &"# AND orders.opending = 'Pending' AND orders.oprocessed = False"


set Conn=server.createobject("adodb.connection")
Conn.open DSN
set rs = conn.Execute(SQL)

if not rs.eof then
Dim Head
For Each  ID In RS.Fields
Head = Head & "# " &  ID.Name
Next
Head = Mid(Head,3) & vbCrLf

Response.Write Head
Response.Write RS.GetString(,,"# ",vbCrLf,"")

Dim mypath

mypath = Server.Mappath "temp"
mypath = mypath & "myexcelfile.csv"

Set fs = CreateObject("Scripting.FileSystemObject")
Set writefile = fs.OpenTextFile(mypath, 8, True)
writefile.writeline(Head)
writefile.writeline(RS.GetString(,,"# ",vbCrLf,""))
writefile.close
set writefile = nothing
set fs = nothing

Dim Email
Dim MyCDONTSMail
Dim emailrec
Dim msg

emailrec = Request.Form("email")


   Set MyCDONTSMail = CreateObject("CDONTS.NewMail")
   MyCDONTSMail.From= "customer.service@0044.co.uk"
   MyCDONTSMail.To= emailrec
   MyCDONTSMail.Subject="Exported Data"
   MyCDONTSMail.Body= "Data attached"
   MyCDONTSMail.AddAttachment  = strPath & "\testText.txt"
   MyCDONTSMail.Send
   set MyCDONTSMail=nothing




Set RS = nothing
Conn.close
else
Response.Write "No matching rows with the selected date range"
end if


else
Response.Write "Invalid date"
end if

%>
0
 
dwaldnerCommented:
You probably don't have write permissions set for the directory you want to write to...make sure that's set for your IUSR_<servername> user.
0
 
Choice-QuoteAuthor Commented:
Correction;

MyCDONTSMail.AddAttachment  = mypath
0
 
Choice-QuoteAuthor Commented:
Double checked; folder temp has write access
0
 
dwaldnerCommented:
And as for how to get the good error message, make sure you disable Friendly HTTP Error Messages

In IE:  Tools -> Internet Options -> Advanced -> Show Friendly HTTP Error Messages (unchecked).

Dan
0
 
Choice-QuoteAuthor Commented:
Ahhh, never knew that!

Error message is;

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/shopping/shopa_downloadorders2.asp, line 37

mypath = Server.Mappath "temp"
------------------------^
0
 
Choice-QuoteAuthor Commented:
I corrected error so;

mypath = "E:\domains\0044.uk.com\db\"

as I know for certain that this folder has write permissions as contains my database files.

Now when I run it tries to save the actual .asp script?
0
 
dwaldnerCommented:
No, when you run it, the ASP script saves the CSV output to a CSV file.  If you go to the directory after running this script, there should be a file called "myexcelfile.csv"....should be at least if all goes well.
0
 
dwaldnerCommented:
(assuming you've changed the name of your file as suggested from myexcelfile.xls to myexcelfile.csv).
0
 
Choice-QuoteAuthor Commented:
Ah right.

It has created the myexcelfile.csv - however, only contains header rows 4 times, no data.

Craig
0
 
dwaldnerCommented:
Response.Write RS.GetString(,,"# ",vbCrLf,"")

I don't have any experience with using RS.GetString, but are you sure that its outputting correctly?  When you run the script, I notice that you have this outputting to the screen.  Does it provide information to the screen and not to the csv?
0
 
sciber_dudeCommented:
Why not replace

>Response.Write RS.GetString(,,", ",vbCrLf,"")  

with this..

If not (Rs.EOF or rs.BOF) then
  Do until Rs.EOF
     for each x in Rs.Fields
        TheData = TheData & x.value & ", "
     next
     TheData = left(TheData, len(TheData)-2) & VbCrLf
   rs.movenext
end if

and change this line
> writefile.writeline(RS.GetString(,,"# ",vbCrLf,""))
To
writefile.writeline(TheData)


Btw, you will also have to delete all the contents of the .csv file before writing to it. The code is in the link i gave ya.

:) SD
0
 
Choice-QuoteAuthor Commented:
I wanted to give the option for the user to download the data to their desktop as well as receiving it on email.

I can removed this if you feel it may be causing problems.

Craig
0
 
sciber_dudeCommented:
I am sorry, I meant this..

As dwaldner had suggested... Instead of using RS.GetString(,,", ",vbCrLf,""), why not use the code i gave you.

Not sure how RS.GetString works.

:) SD

0
 
dwaldnerCommented:
If you want the user to download the data to their desktop, instead of emailing it to them as an attachment, just do this:

response.redirect "/db/myexcelfile.csv"

Or whatever your website path is for the csv file.  This will pop up the results to their screen so that they can save it to their system.
0
 
Choice-QuoteAuthor Commented:
Thanks guys; Getting there!

The code below returns the following error;

Microsoft VBScript compilation error '800a0400'

Expected statement

/shopping/shopa_downloadorders2.asp, line 33

end if
^

(line 33 is the end of the if statement you gave me)


<%
Dim DSN, RS, SQL, Conn, startdate, enddate, starttime, endtime




startdate = Request.Form("start_month") & "/" & Request.Form("start_day") & "/" & Request.Form("start_year")
enddate = Request.Form("end_month") & "/" & Request.Form("end_day") & "/" & Request.Form("end_year")
starttime = Request.Form("start_hour") & ":" & Request.Form("start_minute")
endtime = Request.Form("end_hour") & ":" & Request.Form("end_minute")

Response.Addheader "Content-Disposition", "inline; filename=ExportSearched.csv"
Response.ContentType = "application/vnd.ms-excel"
DSN="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=E:\domains\0044.uk.com\db\shopping500.mdb"


if isDate(startdate) and isDate(enddate) Then
SQL = "SELECT orders.orderid, orders.ocustomerid, orders.otime, orders.odate, orders.ofirstname, orders.olastname, orders.oaddress, orders.ocity, orders.opostcode, orders.ostate, orders.ocountry, orders.ophone, orders.oshipmethod, orders.oshipname, orders.oshipaddress, orders.oshiptown, orders.oshipzip, orders.oshipstate, orders.oshipcountry, oitems.catalogid, oitems.numitems, oitems.itemname FROM orders INNER JOIN oitems ON orders.orderid = oitems.orderid WHERE orders.odate >= #" & startdate & "# AND orders.odate <= #" & enddate & "# AND orders.otime >= # " & starttime & "# AND orders.otime <= #" & endtime &"# AND orders.opending = 'Pending' AND orders.oprocessed = False"


set Conn=server.createobject("adodb.connection")
Conn.open DSN
set rs = conn.Execute(SQL)


If not (Rs.EOF or rs.BOF) then
  Do until Rs.EOF
     for each x in Rs.Fields
        TheData = TheData & x.value & ", "
     next
     TheData = left(TheData, len(TheData)-2) & VbCrLf
   rs.movenext
end if

if not rs.eof then
Dim Head
For Each  ID In RS.Fields
Head = Head & "# " &  ID.Name
Next
Head = Mid(Head,3) & vbCrLf


Response.Write Head
Response.Write TheData

Dim mypath

mypath = "E:\domains\0044.uk.com\db\"
mypath = mypath & "myexcelfile.csv"

Set fs = CreateObject("Scripting.FileSystemObject")
Set writefile = fs.OpenTextFile(mypath, 8, True)
writefile.writeline(Head)
writefile.writeline(TheData)
writefile.close
set writefile = nothing
set fs = nothing

Dim Email
Dim MyCDONTSMail
Dim emailrec
Dim msg

emailrec = Request.Form("email")


   Set MyCDONTSMail = CreateObject("CDONTS.NewMail")
   MyCDONTSMail.From= "customer.service@0044.co.uk"
   MyCDONTSMail.To= emailrec
   MyCDONTSMail.Subject="Exported Data"
   MyCDONTSMail.Body= "Data attached"
   MyCDONTSMail.AddAttachment  = mypath
   MyCDONTSMail.Send
   set MyCDONTSMail=nothing




Set RS = nothing
Conn.close
else
Response.Write "No matching rows with the selected date range"
end if


else
Response.Write "Invalid date"
end if

%>
0
 
dwaldnerCommented:
Missing the end of the Do while loop...add that in and you'll be fine.

Dan
0
 
dwaldnerCommented:
(put a Loop in after the rs.moveNext)
0
 
Choice-QuoteAuthor Commented:
It's not bringing up any errors; however it's not selected any data whilst it was doing before modifications.

Any ideas?

Craig
0
 
dwaldnerCommented:
It's not selected any data?  Could you elaborate on that?
0
 
sciber_dudeCommented:
oops! A typo!

Get rid of the comment (') before this line.

DSN="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=E:\domains\0044.uk.com\db\shopping500.mdb"

:) SD
0
 
Choice-QuoteAuthor Commented:
You're my f'in hero! Cheers buddy!

Made a slight adjustment to the AddAttachment thingie... other than that; perfect!

<%
Dim DSN, RS, SQL, Conn, startdate, enddate, starttime, endtime

Startdate = Request.Form("start_month") & "/" & Request.Form("start_day") & "/" & Request.Form("start_year")
enddate = Request.Form("end_month") & "/" & Request.Form("end_day") & "/" & Request.Form("end_year")
starttime = Request.Form("start_hour") & ":" & Request.Form("start_minute")
endtime = Request.Form("end_hour") & ":" & Request.Form("end_minute")

' Response.Addheader "Content-Disposition", "inline; filename=ExportSearched.csv"
' Response.ContentType = "application/vnd.ms-excel"

DSN="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=E:\domains\0044.uk.com\db\shopping500.mdb"


if isDate(startdate) and isDate(enddate) Then
     SQL = "SELECT orders.orderid, orders.ocustomerid, orders.otime, orders.odate, orders.ofirstname, orders.olastname, orders.oaddress, orders.ocity, orders.opostcode, orders.ostate, orders.ocountry, orders.ophone, orders.oshipmethod, orders.oshipname, orders.oshipaddress, orders.oshiptown, orders.oshipzip, orders.oshipstate, orders.oshipcountry, oitems.catalogid, oitems.numitems, oitems.itemname FROM orders INNER JOIN oitems ON orders.orderid = oitems.orderid WHERE orders.odate >= #" & startdate & "# AND orders.odate <= #" & enddate & "# AND orders.otime >= # " & starttime & "# AND orders.otime <= #" & endtime &"# AND orders.opending = 'Pending' AND orders.oprocessed = False"
     
     set Conn=server.createobject("adodb.connection")
     Conn.open DSN
     Set rs = conn.Execute(SQL)


     If not (Rs.EOF or rs.BOF) then
          Dim Head, TheData
          For Each  ID In RS.Fields
               Head = Head & "# " &  ID.Name
          Next
          Head = Mid(Head,3) & vbCrLf
          Do until Rs.EOF
               for each x in Rs.Fields
                    TheData = TheData & x.value & ", "
               next
               TheData = left(TheData, len(TheData)-2) & VbCrLf
          rs.movenext
          loop
     
          Response.Write Head
          Response.Write TheData

          Dim mypath

          mypath = "E:\domains\0044.uk.com\db\"
          mypath = mypath & "myexcelfile.csv"

          Set fs = CreateObject("Scripting.FileSystemObject")
          Set writefile = fs.OpenTextFile(mypath, 8, True)
          writefile.writeline(Head)
          writefile.writeline(TheData)
          writefile.close
          set writefile = nothing
          set fs = nothing

          Dim Email
          Dim MyCDONTSMail
          Dim emailrec
          Dim msg

          emailrec = Request.Form("email")

          Set MyCDONTSMail = CreateObject("CDONTS.NewMail")
          MyCDONTSMail.From= "customer.service@0044.co.uk"
          MyCDONTSMail.To= emailrec
          MyCDONTSMail.Subject="Exported Data"
          MyCDONTSMail.Body= "Data attached"
          MyCDONTSMail.AttachFile(mypath)
          MyCDONTSMail.Send
          set MyCDONTSMail=nothing

          Set RS = nothing
          Conn.close
         
          ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
          ' Redirecting the page after sending email
          ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
         
          ' response.redirect "db/myexcelfile.csv"
         
     else
          Response.Write "No matching rows with the selected date range"
     end if

else
     Response.Write "Invalid date"
end if
%>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.