Creating CSV - filesystemobject 2

[doorbreaker]

Hi,

have some code in a vbs file that should drag all records out of a db and then spit them out into a csv file.

I run the file and no errors are thrown up but no csv file is made. Any ideas - code below....

I have checked the permissions on the folder.

Any help appreciated.

Chris

on error resume next
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.cursorlocation=3
oconn.Open "driver={sql server};server=xxx.xxx.xxx.xxx;database=mydb;uid=myuid;pwd=mypwd"



strSQL = "select * from productmaster"
			
				
Set oRS = objconn.Execute(strSQL)

dim oFSO, oFile,oField
dim strOutput


set oFSO = server.createobject ("scripting.filesystemobject")
set oFile = oFSO.createtextfile("c:\csv\kelkoo.csv",true)
do until oRS.eof
    for each oField in oRS.fields
        stroutput = stroutput & ofield.value & ","
    next
    
    stroutput = left(stroutput,len(stroutput)-1)
    ofile.writeline stroutput
    ors.movenext
loop

ofile.close
set oFile = nothing
set oFSO = nothing

 

[steven290]

first try this

on error resume next
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.cursorlocation=3
oconn.Open "driver={sql server};server=xxx.xxx.xxx.xxx;database=mydb;uid=myuid;pwd=mypwd"



strSQL = "select * from productmaster"
            
                
Set oRS = objconn.Execute(strSQL)

dim oFSO, oFile,oField
dim strOutput


'set oFSO = server.createobject ("scripting.filesystemobject")
'set oFile = oFSO.createtextfile("c:\csv\kelkoo.csv",true)
do until oRS.eof
    for each oField in oRS.fields
        stroutput = stroutput & "'" & ofield.value & "',"
    next
    
    stroutput = left(stroutput,len(stroutput)-1)
    'ofile.writeline stroutput
    ors.movenext
loop
response.write stroutput

'ofile.close
'set oFile = nothing
'set oFSO = nothing'

see if it displays anything first

 

[steven290]

you say this is a vbs file but it looks like asp form

server.creatobject

vs

createobject

and when your ready to create this csv, i would do it like this

do until oRS.eof
    for each oField in oRS.fields
        stroutput = stroutput & chr(34) & ofield.value & chr(34)& ","
    next
    
    stroutput = left(stroutput,len(stroutput)-1)
    stroutput = stroutput & vbCrLf
    ofile.writeline stroutput
    ors.movenext
loop

each row has to be separated by a vbCrLf

 

[doorbreaker]

I am stil learning the ropes here.....

I renamed it .asp and now it works. Will I still be able to call this from a scheduled task?

 

[steven290]

not sure. But i'm glad it worked out for you.

 

[Sheco]

I run the file and no errors are thrown up but no csv file is made

In order to see the errors you need to remove this line:
on error resume next

 

[steven290]

but its working now, anyway

 

[doorbreaker]

thanks people

just gotta work out how to run the page now from a scheduled task...but i think thats possibly a different forum.

 

[Sheco]

I bet if you look under Scheduled Tasks in the Control Panel then you can figure it out... it is fairly straightforward.

 

[doorbreaker]

I found a solution:

Apparently save the following code as whatevername.vbs and just set the URL to whatever page you want. Then in scheduled tasks just point to the vbs file.

 Sub MyASPJob()
  
    Dim oXMLHttp
    Dim sURL
                                
       on error resume next

       Set oXMLHttp = CreateObject("MSXML2.XMLHTTP.3.0")
       sURL = "[URL unfurl="true"]http://usr:pwd@www.mysite.com/job.asp[/URL]
              
       oXMLHttp.open "GET", sURL, false
       oXMLHttp.send()                  

          if oXMLHttp.status = 200 Then  
             ' Retrieve enter HTML response
             MsgBox oXMLHttp.ResponseText
          else
     ' Failed
  end if
    
Set oXMLHttp = nothing

End Sub

Call MyASPJob()

Cheers

Chris