genreation csv file in asp giving problem

[Navin]

hi,
i am using the follwoing code to write a recordset to a csv file

'fcreateFile -file system object

Do While Not oRs.EOF
fcreateFile.Write(oRs.GetString(,1000,",",vbcrlf,"")) 'Write Login
Info
loop

to write the data to csv fle
the sql retuns the data in few seconds but at asp file whehn i cma
writing data to the csv file using the getsring method to generate a
comma soeperated file
then it just hangs if the no of records is more than 10,000
i tried alot of things ultimate this piece of code was giving hassles
if the data is returned in 5 sec it takes around 10 minutes for
writing to the csv file
can you can help me ou how to correct this piece code
for yrt ref my whole code of file genearion is given below





'Download base Summary-----------------------------

<%'-------------------------Title------------------------
On Error Resume Next

'----------------File System
Object----------------------------------------------
Set fs = Server.CreateObject("Scripting.FileSystemObject")
if fs.FileExists(server.MapPath(".") & "\Download\" & strfilename) =
true then
'set fcreateFile = fs.OpenTextFile(server.MapPath(".") &
"\Download\" & strfilename , 2,true)
sFileExists=true
else
Set fcreateFile = fs.CreateTextFile(server.MapPath(".") &
"\Download\" & strfilename ,True)

end if
'-----------RecordSet-----------------------------------
If Err.number <> 0 Then%>
<script>
alert("File-"+'<%=strfilename%>'+"Already Opened."+"\n"+"Please
Close the File and then Click on the Link")
</script>
<%Set fcreateFile=nothing
Response.End
end if

'----------------------Com Object------------------------
set objUtility = Server.CreateObject(objUtilityName)
'-------------------RecordSet---------------------------
Set oRs = Server.CreateObject("ADODB.RecordSet")

'-----------------------Login
Info----------------------------------------------
sLOginInfo="," & "DOWNLOAD BASE SUMMARY REPORT" & vbcrlf & vbCrLf &
_
"Period:" & "," & nProcessMonth & " (" & sMonthName & ")" &
vbcrlf & _
sPersonName & vbcrlf & _
"CCAT ID:" &"," & CStr(nCcatId) & vbcrlf & vbcrlf
fcreateFile.Write(sLOginInfo) 'Write Login Info
'-----------------------------
ssql="some sql query"
Set oRs = objUtility.fncRunSQLReturnRS(sSql)

IF oRs.EOF=false then
sHeader="Manager"& "," & "Person" & "," & "Agent" & "," & "SAC
Code"& "," & _
"SAC Name"&","& "From Period" &"," &"To Period"&"," & _
"Revenue Stream"&","& "Current YTD" &"," & _
"Last YTD " &","& "YTD Target" & "," & _
"% Ach YTD" & "," &_
"Last Yr Full" &"," & _
"Full Target" &"," & "% Ach Full"& "," & _
"Growth %"& vbcrlf
fcreateFile.Write(sHeader) 'Header String
fcreateFile.Write(oRs.GetString(,,",",vbcrlf,"") ) 'Manager Sac
Data
Do While Not oRs.EOF
fcreateFile.Write(oRs.GetString(,1000,",",vbcrlf,"")) 'Write Login
Info
Loop

fcreateFile.Write(vbCrLf )
end if
oRs.Close
set oRs=nothing
set objUtility=nothing
fcreateFile.Close
Set fs=Nothing
%>

 

[Aaron Bertrand - MVP]

Do While Not oRs.EOF

fcreateFile.Write(oRs.GetString(,1000,",",vbcrlf,"")) 'Write Login
Info

' did you forget oRs.MoveNext ???

 

[Chris Hohmann]

Is an error produced when you comment out the "On Error Resume Next"
lline?

 

[Chris Hohmann]

' did you forget oRs.MoveNext ???

There an implicit move associated with the GetString method.

 

[Navin]

Is an error produced when you comment out the "On Error Resume Next"
lline?

hi,
guys
the codes works fine if there are say 10,000 records but above that it
just starts dragging... man if takes 25 seconds to fetch the data in recordset
egL: 50,000
when use this piece code to write to file it just drags man.....
guys please tell me the fastest wasy to write to the file......


Do While Not oRs.EOF
fcreateFile.Write(oRs.GetString(,1000,",",vbcrlf,"")) 'Write Login Info
Loop

 

[Chris Hohmann]

"Chris Hohmann" <hohmannATyahooDOTcom> wrote in message

hi,
guys
the codes works fine if there are say 10,000 records but above that it
just starts dragging... man if takes 25 seconds to fetch the data in recordset
egL: 50,000
when use this piece code to write to file it just drags man.....
guys please tell me the fastest wasy to write to the file......


Do While Not oRs.EOF
fcreateFile.Write(oRs.GetString(,1000,",",vbcrlf,"")) 'Write Login Info
Loop

The short answer is that the following line is retrieving and writing
ALL the records in the recordset at one time so the Do..Loop is never
entered:

fcreateFile.Write(oRs.GetString(,,",",vbcrlf,"") ) 'Manager Sac Data

Having said that, here are a few more questions
1. What the Database? Version? Table Structure?
2. What are the specifics of objUtilityName and fncRunSQLReturnRS. Why
do you need a custom object to open a recordset?

How about something like this:

<%
Dim sFile,fso,f,cn,rs
sFile = Server.MapPath(".") & "\Download\Foo.txt"
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fspenTextFile(sFile,2,True)
f.WriteLine "Manager,Person,Agent,SAC Code,SAC Name,From Period,To
Period,Revenue Stream,Current YTD,Last YTD,YTD Target,Ach YTD,Last Yr
Full,Full Target,Ach Full,Growth %"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "FILE NAME=C:\SomePathOutsideAppRoot\MyDB.UDL"
cn.spLoginInfo rs
Do While Not rs.EOF
f.Write rs.GetString(2,100,",",vbCRLF,"")
Loop
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
f.Write vbCRLF
f.Close : Set f = Nothing
Set fso = Nothing
%>

HTH
-Chris

 

[Navin]

1>Database-oracle
2>objUtilityName-com object
fncRunSQLReturnRS-method in my com object
i have dll registered under mts
i create the com object using the server.createobject .

here the code which i use in my com object.

and the point is my sql runs in 10 sec on oracle server.
but when i execute the sql using ado using the code given below it takes
around a minute to get back the results.
where is the problem lies...
1>is that due to network speed that oracle is running on diff server
and my webserver is running on diff server.
the transportaion of data takes a lot of time.
2>is the code given n=belwo for executing the sql is slow.


Function FncRunSQLReturnRS(ByVal strsql As String) As ADODB.Recordset
On Error GoTo ErrorHandler
'Set up Command and Connection objects
Dim rs As ADODB.Recordset, cmd As ADODB.Command, conn As ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
'fnLogEvents "logs.txt", "strSql", strSql
'Run the procedure

cmd.ActiveConnection = getconnectionstring("connection.inc")
cmd.CommandText = strsql
cmd.CommandType = adCmdText
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenForwardOnly, adLockReadOnly
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
Set rs.ActiveConnection = Nothing

Set FncRunSQLReturnRS = rs
Exit Function

ErrorHandler:
Set rs = Nothing
Set cmd = Nothing
If Err.Number <> 0 Then
fnLogEvents "logs.txt", "error", Err.Description
End If
End Function

 

[Chris Hohmann]

1>Database-oracle
2>objUtilityName-com object
fncRunSQLReturnRS-method in my com object
i have dll registered under mts
i create the com object using the server.createobject .

here the code which i use in my com object.

and the point is my sql runs in 10 sec on oracle server.
but when i execute the sql using ado using the code given below it takes
around a minute to get back the results.
where is the problem lies...
1>is that due to network speed that oracle is running on diff server
and my webserver is running on diff server.
the transportaion of data takes a lot of time.
2>is the code given n=belwo for executing the sql is slow.

<<Rest of message omitted for brevity>>

Please reread my prior post. The following line is the cause of your
performance problem, please comment it out:

fcreateFile.Write(oRs.GetString(,,",",vbcrlf,"") ) 'Manager Sac Data