ADODB.Stream from variable rather than from file?

What about writing the data to a text file on the server using FSO, stream it the way you know how, and then when you're done, clean up the file by deleting it using fso?

I know this is a hack / work around, but what the heck...?

peh803

I have it in a project I did 2 years ago... looking now

ASKER

>>What about writing the data to a text file on the server using FSO, stream it the way you know how, and then when you're done, clean up the file by deleting it using fso? <<

That I can do and will do if I have no choice. However, it seems much more efficient just to stream the variable rather than writing a text file from the variable, and then stream the text file, and then finally have to delete the file. This is especially true as I will have to create a separate page to take care of the latter--once you start streaming, you can't do anything else with that page....

FtB

ASKER

>>I have it in a project I did 2 years ago... looking now<<

THE BIRD IS BACK!!!!!!!!!!!!!!!!!!!!

FtB

>>it seems much more efficient just to stream the variable rather than writing a text file from the variable

Yessir, I would tend to agree...let's hope the bird can find his solution from a while back...

peh803

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

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

Start Free Trial

I just hop on every month to get my 3000 points :)

ASKER

Okay, I am trying this, but all it does is write out the text to the page...

strSQL = "SELECT intVORid, strContactName, strDistName, datReport, strCustName, strModel, strRegNo, strChassisNo, strVORReason, datDTReceived, datRepStarted, datPartsOrdered, strPartNumbers, strPriorityCRef, strDelayCode, intDaysOffRoad, datBackOnRoad FROM tblVOR"
objRS.Open strSQL, objConnection
strOutPut = objRS.GetString(2, ,"," , , "Null")
'response.write(strOutPut)

'Dim objStream,objFilesystem,objFilestream
'Response.Buffer = True
'Response.Clear

' create stream
'Set objStream = Server.CreateObject("ADODB.Stream")
'objStream.Open

' set as binary
'objStream.Type = 1

Response.AddHeader "Content-Disposition", "attachment; filename=VOR_Data.txt"
Response.Charset = "UTF-8"
Response.ContentType = ContentType
Response.BinaryWrite strOutPut
Response.Flush
Response.Clear

objRS.Close
set objRS = Nothing
objConnection.close
set objConnection = Nothing

FtB

What happens if you change the content type to something else, like excel, or more importantly a csv ?

If it's streaming to the page (showing up the correct info on the browser), then you're in great shape.

I remember we had this problem, just trying to dust off the cob webs off that part of my brain :)

Try something like

ContentType = "application/octet-stream"

(Shot in the dark)

Fritz -- you've maybe seen this, but here's an example from GaryC123 of doing what I suggested earlier...I know you *don't* want to do this and I don't want to shove it down your throat, but here ya go anyways...

from: https://www.experts-exchange.com/questions/20790618/Force-Download-of-txt-File.html#9701974

<%
Response.Buffer = True

strFileName="ee.txt" ' Set file name

strFilePath=server.mappath(strFilename) ' Set path of file
set fso=createobject("scripting.filesystemobject")
set f=fso.getfile(strfilepath)
strFileSize = f.size
set f=nothing: set fso=nothing
Const adTypeBinary = 1
Response.Clear
Set objStream = Server.CreateObject("ADODB.Stream")
objStream.Open
objStream.Type = adTypeBinary
objStream.LoadFromFile strFilePath
strFileType = "application/text" ' change to the correct content type for your file
Response.AddHeader "Content-Disposition", "attachment; filename=" & strFileName
Response.AddHeader "Content-Length", strFileSize
Response.Charset = "UTF-8"
Response.ContentType = strFileType
Response.BinaryWrite objStream.Read
Response.Flush
objStream.Close
Set objStream = Nothing
%>

>>"I know how to do this with a text file, but how do I modify the code below to stream the variable instead?"

After re-reading your original post, I now realize that I should probably just "step aside" and stop suggesting what you already know how to do....

*blush*

ASKER

Okay, we have a winner!!!


<%
Dim objConnection, objRS, strSQL
set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open strConnectionString
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT intVORid, strContactName, strDistName, datReport, strCustName, strModel, strRegNo, strChassisNo, strVORReason, datDTReceived, datRepStarted, datPartsOrdered, strPartNumbers, strPriorityCRef, strDelayCode, intDaysOffRoad, datBackOnRoad FROM tblVOR"
objRS.Open strSQL, objConnection
strOutPut = objRS.GetString(2, ,"," , , "Null")
objRS.Close
set objRS = Nothing
objConnection.close
set objConnection = Nothing

Response.AddHeader "Content-Disposition", "attachment; filename=Vor.txt"
Response.Charset = "UTF-8"
Response.ContentType = "application/octet-stream"
Response.BinaryWrite strOutPut
Response.Flush
Response.Clear
%>

ASKER

@peh803--

Thank you very much for the code. As you can see, though, the code I posted above already does that.

I have built my suggestions on Thunderchicken's posts. If everyone is okay with it, I would like to award him the points.

FtB

Great, nice work thunderchicken! I guess you only need 1000 more points to get up to your 3000 points for the month...

:)

peh803

Yes, fine with me :)

I can go over my 3000 and go back into hiding

ASKER

Thanks again to all!

FtB