genreation csv file in asp giving problem

Discussion in 'ASP General' started by Navin, Sep 9, 2003.

  1. Navin

    Navin Guest

    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
    %>
    Navin, Sep 9, 2003
    #1
    1. Advertising

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

    ' did you forget oRs.MoveNext ???
    > loop
    Aaron Bertrand - MVP, Sep 9, 2003
    #2
    1. Advertising

  3. Is an error produced when you comment out the "On Error Resume Next"
    lline?
    Chris Hohmann, Sep 9, 2003
    #3
  4. "Aaron Bertrand - MVP" <> wrote in message
    news:OC%...
    > > Do While Not oRs.EOF
    > > fcreateFile.Write(oRs.GetString(,1000,",",vbcrlf,"")) 'Write Login
    > > Info

    > ' did you forget oRs.MoveNext ???
    > > loop


    There an implicit move associated with the GetString method.
    Chris Hohmann, Sep 9, 2003
    #4
  5. Navin

    Navin Guest

    "Chris Hohmann" <hohmannATyahooDOTcom> wrote in message news:<>...
    > 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
    Navin, Sep 10, 2003
    #5
  6. "Navin" <> wrote in message
    news:...
    > "Chris Hohmann" <hohmannATyahooDOTcom> wrote in message

    news:<>...
    > > 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


    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 = fso_OpenTextFile(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
    Chris Hohmann, Sep 10, 2003
    #6
  7. Navin

    Navin Guest

    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" <hohmannATyahooDOTcom> wrote in message news:<>...
    > "Navin" <> wrote in message
    > news:...
    > > "Chris Hohmann" <hohmannATyahooDOTcom> wrote in message

    > news:<>...
    > > > 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

    >
    > 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 = fso_OpenTextFile(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, Sep 11, 2003
    #7
  8. "Navin" <> wrote in message
    news:...
    > 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
    Chris Hohmann, Sep 11, 2003
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Michal Mikolajczyk
    Replies:
    0
    Views:
    630
    Michal Mikolajczyk
    Feb 13, 2004
  2. Tintin92
    Replies:
    1
    Views:
    1,672
    Andrew Thompson
    Feb 14, 2007
  3. jliu66
    Replies:
    0
    Views:
    492
    jliu66
    Oct 19, 2007
  4. sixteenmillion

    The giving that keeps on giving

    sixteenmillion, Nov 19, 2007, in forum: C Programming
    Replies:
    0
    Views:
    413
    sixteenmillion
    Nov 19, 2007
  5. Li Chen
    Replies:
    18
    Views:
    643
    Azmi Farih
    Mar 23, 2010
Loading...

Share This Page