Solved: download recordset (in CSV)

>>however in ie6 it just han

It shouldn't...I use that code in IE all the time. If you take that code out, does the recordset display to the screen properly?

dirklance

ASKER

it sure does.

dirklance

ASKER

I might also add i'm using it in an IFRAME if that makes any diff.

alorentz

>>I might also add i'm using it in an IFRAME if that makes any diff.

Hmmm...not sure. I don't use IFRAME unless absolutely have to...so never tried it.

You'll have to post the rest of your code...doesn't appear to be a problem with the header.

alorentz

And, also try this:

Response.clear '<-------------ADD
FileName = "sampleReportName.txt"
Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "filename=" & FileName & ";"

alorentz

Or, could try other ways:
https://www.experts-exchange.com/questions/21288109/creating-a-csv-file-in-asp.html

dirklance

ASKER

here is the page that outputs the CSV file:
------------------------------------------
<%response.buffer = true%>



<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<head>
      <title></title>
<META NAME="Author" CONTENT="?">
<meta http-equiv="Expires" content="Sun, 1 Jan 1995 08:00:00 CST">
<META NAME="Keywords" CONTENT="?">
<META NAME="Description" CONTENT="?">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="Includes/style.css" rel="stylesheet" type="text/css">

</head>
<body>

<%

FileName = "sampleReportName.txt"
SQL = replace(replace(request("q"),"?","'"),"-P-","%")

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={MySQL ODBC 3.51 Driver}; Server=XX; Port=XX; Database=XX; User=bdavidge; Password=XXX; Option=3;"

Set RS = Server.CreateObject("ADODB.RecordSet")
RS.Open SQL, objConn

Response.Clear
Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "filename=" & FileName & ";"

      For fnum = 0 To RS.Fields.Count-1

       Response.Write """" & RS.Fields(fnum).Name & ""","
      Next
      RS.movefirst

      Do Until RS.EOF

      For fnum = 0 To RS.Fields.Count-1
      ' again, one of the ADODB.Field properties is Value, so...
      Response.Write """" & RS.Fields(fnum).Value & ""","
      Next
      ' response.write "<br />"
      RS.MoveNext
      Loop

RS.Close
objConn.Close

%>

</body>
</html>

------------------------------------------

page csv.asp is wrapped in:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Frameset//EN"
"http://www.w3.org/TR/html4/frameset.dtd">
<head>
      <title></title>
<META NAME="Author" CONTENT="?">
<meta http-equiv="Expires" content="Sun, 1 Jan 1995 08:00:00 CST">
<META NAME="Keywords" CONTENT="?">
<META NAME="Description" CONTENT="?">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script type="text/javascript">
            var enablepersist="off" //Enable saving state of content structure? (on/off)
            if (document.getElementById){
            document.write('<style type="text/css">')
            document.write('.switchcontent{display:none;}')
            document.write('</style>')
            }
            function getElementbyClass(classname){
            ccollect=new Array()
            var inc=0
            var alltags=document.all? document.all : document.getElementsByTagName("*")
            for (i=0; i<alltags.length; i++){
            if (alltags[i].className==classname)
            ccollect[inc++]=alltags[i]
            }
            }
            function contractcontent(omit){
            var inc=0
            while (ccollect[inc]){
            if (ccollect[inc].id!=omit)
            ccollect[inc].style.display="none"
            inc++
            }
            }
            function expandcontent(cid){
            if (typeof ccollect!="undefined"){
            contractcontent(cid)
            document.getElementById(cid).style.display=(document.getElementById(cid).style.display!="block")? "block" : "none"
            selectedItem=cid+"|"+document.getElementById(cid).style.display
            }
            }
            function revivecontent(){
            selectedItem=getselectedItem()
            selectedComponents=selectedItem.split("|")
            contractcontent(selectedComponents[0])
            document.getElementById(selectedComponents[0]).style.display=selectedComponents[1]
            }
            function get_cookie(Name) {
            var search = Name + "="
            var returnvalue = "";
            if (document.cookie.length > 0) {
            offset = document.cookie.indexOf(search)
            if (offset != -1) {
            offset += search.length
            end = document.cookie.indexOf(";", offset);
            if (end == -1) end = document.cookie.length;
            returnvalue=unescape(document.cookie.substring(offset, end))
            }
            }
            return returnvalue;
            }
            function getselectedItem(){
            if (get_cookie(window.location.pathname) != ""){
            selectedItem=get_cookie(window.location.pathname)
            return selectedItem
            }
            else
            return ""
            }
            function saveswitchstate(){
            if (typeof selectedItem!="undefined")
            document.cookie=window.location.pathname+"="+selectedItem
            }
            function do_onload(){
            getElementbyClass("switchcontent")
            if (enablepersist=="on" && getselectedItem()!="")
            revivecontent()
            }
            if (window.addEventListener)
            window.addEventListener("load", do_onload, false)
            else if (window.attachEvent)
            window.attachEvent("onload", do_onload)
            else if (document.getElementById)
            window.onload=do_onload
            if (enablepersist=="on" && document.getElementById)
            window.onunload=saveswitchstate

</script>
<link href="Includes/backend_style.css" rel="stylesheet" type="text/css">

</head>
<body class="bodyWrapper">
<table cellpadding="0" cellspacing="0" style="width:100%;height:100%;">
      <tr>
            <td colspan="4" style="background-color:#4a7385;width:100%;height:63px;border-bottom:2px solid #999999;"><div style="padding-left:15px;"><img src="Images/logo_topper.jpg" /></div></td>
      </tr>
      <tr valign="top">
            <td colspan="3" style="height:25px;"> </td>
            <td style="background-color:#f6f6f6;border-left:1px solid #e7e7e7;"> </td>

      </tr>
      <tr valign="top">
            <td style="width:5px;"><img src="Images/1x1_trans.gif" width="12" height="1" /></td>
            <td style="width:220px">
            
                        <table cellpadding="0" cellspacing="0">
                              <tr valign="middle">
                                    <td style="background:url('Images/rep.jpg');background-repeat:repeat-y;"><div class="expBoxesWrapper" onClick="expandcontent('sc1')"><img src="Images/arrow_4a7385.jpg" /> <b>CONTENT</b></div></td>
                              </tr>
                              <tr>
                                    <td style="background:url('Images/rep.jpg');background-repeat:repeat-y;"><div id="sc1" class="switchcontent" style="padding-left:10px;"><img src="Images/dottedLine.jpg" /><br /><br />
                                    <span><span class="lNavHeader">MAIN*PAC</span><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="pages.asp?org=MAINPAC&mode=list" class="lNav" target="content">Main Pages</a><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="custom.asp?mode=list&org=MAINPAC" class="lNav" target="content">Custom Pages</a><br /><br /><span class="lNavHeader">MAIN*stream</span><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="pages.asp?org=MAINSTREAM&mode=list" class="lNav" target="content">Main Pages</a><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="custom.asp?mode=list&org=MAINSTREAM" class="lNav" target="content">Custom Pages</a><br /><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="custom.asp?org=SHARED&mode=list" class="lNav" target="content">Shared Custom Pages</a><br /><br /></span></div></td>
                              </tr>
                              <tr>
                                    <td><div style="background:url('Images/btm.jpg');background-repeat:no-repeat;width:200px;height:26px;padding-left:10px;padding-top:10px;"> </div></td>
                              </tr>
                        </table><br />
                  
                        <table cellpadding="0" cellspacing="0">
                              <tr>
                                    <td style="background:url('Images/rep.jpg');background-repeat:repeat-y;"><div class="expBoxesWrapper" onClick="expandcontent('sc2')"><img src="Images/arrow_4a7385.jpg" /> <b>REPORTS</b></div></td>
                              </tr>
                              <tr>
                                    <td style="background:url('Images/rep.jpg');background-repeat:repeat-y;"><div id="sc2" class="switchcontent" style="background:url('Images/rep.jpg');background-repeat:repeat-y;width:200px;padding-left:10px;"><img src="Images/dottedLine.jpg" /><br /><br />
                                    <span><span class="lNavHeader">Members</span><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="qMaster_members.asp" class="lNav" target="content">Run QueryMaster</a><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="members.asp" class="lNav" target="content">View All</a><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="q.asp?t=members&mode=LIST" class="lNav" target="content">Saved Queries</a><br /><br />

                                    <span class="lNavHeader">Donations</span><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="qMaster_donations.asp" class="lNav" target="content">Run QueryMaster</a><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="donations.asp" class="lNav" target="content">View All</a><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="donate_chooseOrg.asp" class="lNav" target="content">Add a Donation</a><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="q.asp?t=donations&mode=LIST" class="lNav" target="content">Saved Queries</a><br /><br />

                                    <span class="lNavHeader">Site Stats</span><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="stats.asp" class="lNav" target="content">View Stats</a><br /></span></div></td>
                              </tr>
                              <tr>
                                    <td><div style="background:url('Images/btm.jpg');background-repeat:no-repeat;width:200px;height:26px;padding-left:10px;padding-top:10px;"> </div></td>
                              </tr>
                        </table><br />

                  
                        <table cellpadding="0" cellspacing="0">
                              <tr>
                                    <td style="background:url('Images/rep.jpg');background-repeat:repeat-y;"><div class="expBoxesWrapper" onClick="expandcontent('sc3')"><img src="Images/arrow_4a7385.jpg" /> <b>MEMBERSHIP MAINT.</b></div></td>
                              </tr>
                              <tr>
                                    <td style="background:url('Images/rep.jpg');background-repeat:repeat-y;"><div id="sc3" class="switchcontent" style="background:url('Images/rep.jpg');background-repeat:repeat-y;width:200px;padding-left:10px;"><img src="Images/dottedLine.jpg" /><br /><br />
                                    <span> <img src="Images/arrow_small_gray.jpg" /> <a href="qMaster_members.asp?fMode=1&q=SELECT%20*%20FROM%20members%20WHERE%20memberStatus%20=%20?inactive?&m=Choose_the_member_you_would_like_to_activate_and_click_the_activate_button" class="lNav" target="content">Inactive Members</a><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="addMember.asp" class="lNav" target="content">Add a Member</a><br /></span></div></td>
                              </tr>
                              <tr>
                                    <td><div style="background:url('Images/btm.jpg');background-repeat:no-repeat;width:200px;height:26px;padding-left:10px;padding-top:10px;"> </div></td>
                              </tr>
                        </table><br />

                  
                        <table cellpadding="0" cellspacing="0">
                              <tr>
                                    <td style="background:url('Images/rep.jpg');background-repeat:repeat-y;"><div class="expBoxesWrapper" onClick="expandcontent('sc4')"><img src="Images/arrow_4a7385.jpg" /> <b>SETTINGS</b></div></td>
                              </tr>
                              <tr>
                                    <td style="background:url('Images/rep.jpg');background-repeat:repeat-y;"><div id="sc4" class="switchcontent" style="background:url('Images/rep.jpg');background-repeat:repeat-y;width:200px;padding-left:10px;"><img src="Images/dottedLine.jpg" /><br /><br />
                                    <span><span class="lNavHeader">Merchant Acct.</span><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="merchantAcct.asp" class="lNav" target="content">Merchant Acct Login</a><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="http://linkpoint.com/platformstatus/plat_main.htm" class="lNav" target="content">Linkpoint Status</a></span></div></td>
                              </tr>
                              <tr>
                                    <td><div style="background:url('Images/btm.jpg');background-repeat:no-repeat;width:200px;height:26px;padding-left:10px;padding-top:10px;"> </div></td>
                              </tr>
                        </table><br />
                  
                        <table cellpadding="0" cellspacing="0">
                                    <tr valign="middle">
                                          <td style="background:url('Images/rep.jpg');background-repeat:repeat-y;"><div class="expBoxesWrapper" onClick="expandcontent('sc5')"><img src="Images/arrow_4a7385.jpg" /> <b>HELP</b></div></td>
                                    </tr>
                                    <tr>
                                          <td style="background:url('Images/rep.jpg');background-repeat:repeat-y;"><div id="sc5" class="switchcontent" style="padding-left:10px;"><img src="Images/dottedLine.jpg" /><br /><br />
                                          <span> <img src="Images/arrow_small_gray.jpg" /> <a href="faq.asp" class="lNav" target="content">FAQ</a><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="help.asp?mode=editor" class="lNav" target="content">Editor Help</a><br /> <img src="Images/arrow_small_gray.jpg" /> <a href="http://luminopolis.com/supportTicket" class="lNav" target="content">Submit a support ticket</a></span></div></td>
                                    </tr>
                                    <tr>
                                          <td><div style="background:url('Images/btm.jpg');background-repeat:no-repeat;width:200px;height:26px;padding-left:10px;padding-top:10px;"> </div></td>
                                    </tr>
                        </table>

                  </td>

                  <td style="width:585px;">
                        <table cellpadding="0" cellspacing="0">
                              <tr>
                                    <td style="background:url('Images/bigRep.jpg');background-repeat:repeat-y;"><div style="background:url('Images/bigTop.jpg');background-repeat:no-repeat;width:574px;height:13px;padding-left:10px;padding-top:10px;"> </div></td>
                              </tr>
                              <tr>
                                    <td style="background:url('Images/bigRep.jpg');background-repeat:repeat-y;text-align:center;height:99%;"><div style="width:569px;padding-left:3px;"><iframe frameborder="0" name="content" id="content" style="width:98%;height:650px;background-color:#ffffff;" src="welcome.htm">Your browser needs to support frames to use this application</iframe></div></td>
                              </tr>
                              <tr>
                                    <td><div style="background:url('Images/bigBtm.jpg');background-repeat:no-repeat;width:574px;height:39px;padding-left:10px;padding-top:10px;"> </div></td>
                              </tr>
                        </table>
                  </td>
                  <td style="background-color:#f6f6f6;border-left:1px solid #e7e7e7;height:100%;width:100%;"> </td>
            </tr>
            <tr valign="top">
                  <td colspan="3" style="height:45px;"> </td>
                  <td style="background-color:#f6f6f6;border-left:1px solid #e7e7e7;"> </td>

            </tr>
      </table>

</body>
</html>

______________________________--

points raised.
thanks,

alorentz

There shouldn't be any HTML in a CSV file...you should only have VBScript t create the page.

dirklance

ASKER

originally i had no html in the csv with the same results.

jitganguly

Go to Windows Explorer -->Tools Menu-->Folder Options-->File Types tab-->Choose CSV from the list and highlight-->click Advanced button -->Highlight Open-->Check the check box that says Confirm Open after download

dirklance

ASKER

My settings are already as you describe.

thanks,

jitganguly

And still does not ask for Open, Save ?

dirklance

ASKER

it sure doesn't in ie.

it does in FF though.

jitganguly

Take off Response.buffer and try

dirklance

ASKER

in order to use response.clear i have to set the buffer to true.

when i take out the buffer and response.clear it just writes the csv info to the page.

http://mainstreamcoalition.org/ADMIN/expand.asp
click on reports
click on query master
run a query like WHERE memberID is > 300

jitganguly

Boss,
I am getting a RS error. See this

ADODB.Recordset error '800a0bcd'

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

/ADMIN/csv.asp, line 21

You are passing wrong/incorrect value and RS can't find anything

When you click on download CSV

jitganguly

Sorry hit submit before full post

I said When you click on download CSV R Click to Open in a new window. Your SQL parsing is wrong

SELECT * FROM `donations` WHERE `memberID` > 100 ORDER BY `dID` asc

Why single quotes around donations, member id, dID etc ?

dirklance

ASKER

those are to escape keywords.

i'm not sure what the symbol is called but they aren't single quotes.

when i open in a new window i get all the csv info formatted correctly but now download dialogue.

jitganguly

Why can't I get it ? Any other query criteria ?

dirklance

ASKER

have you run through it in FF?

" i love technology "

jitganguly

FF ?

dirklance

ASKER

firefox

jitganguly

Nope. IE

dirklance

ASKER

thats the problem. its working as intended in Firefox.

however IE is not working as intended.

jitganguly

No, I was asking whats the criteria to return values. I choose Member ID > 300 and it says 0 records

dirklance

ASKER

are you positive you we're in the members qmaster and not the donations?

i get 15 records when i run > 300 query

jitganguly

OK Now got it. Could you use post to open the CSV. I can see you are passing long string in URL to open the CSV

jitganguly

This is wrong

Your code is : Response.ContentType = "text/csv"
Should be Response.ContentType = "application/csv"

dirklance

ASKER

tried using post instead.
no luck in ie still

tried changing text to application
still no luck in ie.

However it still works great in Firefox.

Did i mention i LOVE IE?

alorentz

Actually, I use exactly this, and works fine in IE: (no buffer)

FileName="somefile.csv" 'default file name
<%
Response.Clear
Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "filename=" & FileName & ";"

Set DBConn = Server.CreateObject("ADODB.Connection")
      DBConn.Open Session("conn")

      sql = "SELECT * from TABLENAME"
      set rs = dbconn.Execute(sql)
      if not rs.eof then
       do until rs.eof
                         Response.Write rs("field") & "," & vbcrlf
              rs.movenext
       loop
      end if
%>

It prompts for Save As and Saves as csv.

dirklance

ASKER

it just writes out recordset in csv format.

i'll try rebooting after this post.

thanks,

alorentz

Actually:

<%
FileName="somefile.csv" 'default file name
Response.Clear
Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "filename=" & FileName & ";"
Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn.Open Session("conn")

sql = "SELECT * from TABLENAME"
set rs = dbconn.Execute(sql)
if not rs.eof then
do until rs.eof
Response.Write rs("field") & "," & rs("field2") & vbcrlf
rs.movenext
loop
end if
%>

Minus the SQL changes, that's all that is in the page, and works fine. Over 150,000 records.

alorentz

And yes, application/csv should work as well.

But I think your issue is a browser setting.

alorentz

Nother example here:

http://www.devx.com/getHelpOn/10MinuteSolution/20482

dirklance

ASKER

ok so restarting did the trick.

i'll have to see if it's working for the client. i'll post back.

THANKS everyone!

jitganguly

Boy !!!

alorentz

>>ok so restarting did the trick.

Of course it did <grin>...this issue sounded to hokie to be code related!

Glad to help!

jitganguly