[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Csv with Classic ASP

Posted on 2010-01-04
4
Medium Priority
?
754 Views
Last Modified: 2012-05-08
I am trying to export a csv file with classic asp from a database. The wierd thing about it is the database. I am trying to do a for loop which will get account numbers from a column in the database. the column has child accounts and is comma delimited in the database, not my design by the way. So, i am doing a for loop to get the accounts and parseing out the commas and puitting the accounts into a SQL string to try to get the data for each account. The problem is, sometimes it works well and sometimes it doesn't. I have the code here and I am not sure what to do to fix it. I can provide more info as needed to help understand better. But here's the code.

<%
   'Response.ContentType = "application/vnd.ms-excel"
   Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "attachment;filename=""exportByDateResults.csv"""
 %>

<!--#INCLUDE FILE="../includes/config.asp"-->
<!--#INCLUDE FILE="../includes/functions.asp"-->
<!--#INCLUDE FILE="../includes/adovbs.inc"-->
<!-- #include file="../includes/ReportClass.asp" -->



 
<%
if request.form("allchilds")<>"" then

CONST QT = """"

                    Dim axn: axn = TRIM(request("axn"))
             
             
                FromDate = cDate(Request("FromDate"))
                If strComp(FromDate, "Click here to select date...", 0) = 0 Then
                  alert_goback "Please select a valid Starting Date to continue.", "-1"
                End IF
               
                ToDate = cDate(Request("ToDate"))
                If strComp(ToDate, "Click here to select date...", 0) = 0 Then
                  alert_goback "Please select a valid Ending Date to continue.", "-1"
                End IF
               
               
               
                If FromDate > ToDate Then
                  alert_goback "Your ending date can not be greater than your starting date.\nPlease select valid dates to continue.", "-1"
                End If
               
               
                 ' SQL1 = "SELECT CL.CardDescriptn, TC.CardNumber, convert(varchar, TC.TransDate, 101) as date, TC.TransTime as Time, TC.Odometer, PC.LongDesc, rtrim(round(convert(decimal(10,1), TC.TransQuantity), 1)) as Units, rtrim(round(convert(decimal(10,2), TC.UnitPriceInv), 2)) as price, rtrim(round(convert(decimal(10,2), TC.TransQuantity * TC.UnitPriceInv), 2)) AS TotalCost, TC.VehicleNumber, TC.KeyboardInput as MiscKbd, TC.sitenumber, TC.ForeignSiteCode, TC.AccountNumber from TransCurrent AS TC left outer join CardList AS CL on TC.CardNumber = CL.CardNumber inner join ProductCodes as PC on TC.productcode = PC.productcode"
 
  ' Set RS1 = Server.CreateObject("ADODB.RecordSet")
 ' RS1.Open SQL1, dsnconn
 ' For f = 0 To RS1.Fields.Count-1
                'hdr = hdr & "," & QT & RS1.Fields(f).Name & QT
            'Next
'Response.Write Mid(hdr,2) & vbNewLine ' lop off leading comma

'rs1.close


                 childAccountsList = GetSingleTableValue("customerLogin", "childAccounts", dsnconn, "where customerNumber = '"& mainAccount &"'")
                      CAL = Split(childAccountsList, ",")

                 For i = 0 to UBOUND(CAL)
          

               ' where = " TC.AccountNumber = '"& CustomerNumber &"' AND TC.TransDate between '"& cDate(request("FromDate")) &"' AND '"& cDate(request("ToDate")) &"'"
              where = " TC.AccountNumber = '"& CAL(i) &"' AND TC.TransDate between '"& cDate(request("FromDate")) &"' AND '"& cDate(request("ToDate")) &"'"
             

                                           

             
               
              Set RS = Server.CreateObject("ADODB.RecordSet")
                           
              'SQL = "SELECT CL.CardDescriptn, TC.CardNumber, TC.TransDate, TC.ForeignSiteCode, TC.KeyboardInput, TC.TransTime, TC.Odometer, TC.productCode, TC.TransQuantity, TC.UnitPriceInv, TC.TransQuantity * TC.UnitPriceInv AS LineCost, TC.VehicleNumber from TransCurrent AS TC left outer join CardList AS CL on TC.CardNumber = CL.CardNumber where "
             
              SQL = "SELECT CL.CardDescriptn, TC.CardNumber, convert(varchar, TC.TransDate, 101) as date, TC.TransTime as Time, TC.Odometer, PC.LongDesc, rtrim(round(convert(decimal(10,1), TC.TransQuantity), 1)) as Units, rtrim(round(convert(decimal(10,2), TC.UnitPriceInv), 2)) as price, rtrim(round(convert(decimal(10,2), TC.TransQuantity * TC.UnitPriceInv), 2)) AS TotalCost, TC.VehicleNumber, TC.KeyboardInput as MiscKbd, TC.sitenumber, TC.ForeignSiteCode, TC.AccountNumber from TransCurrent AS TC left outer join CardList AS CL on TC.CardNumber = CL.CardNumber inner join ProductCodes as PC on TC.productcode = PC.productcode where "

              SQL = SQL & where
              SQL = SQL & " order by TC.CardNumber, TC.TransDate"
             
           
                    RS.Open SQL, dsnconn
                    
           
%>

    <%
   
            'For f = 0 To RS.Fields.Count-1
                'hdr = hdr & "," & QT & RS.Fields(f).Name & QT
            'Next
'Response.Write Mid(hdr,2) & vbNewLine ' lop off leading comma

prefix = QT
midfix = QT & "," & QT
suffix = QT & vbNewLine
rows = RS.getString( , , midfix, suffix & prefix )
Response.Write prefix & Left(rows, Len(rows) - Len(prefix) )
RS.Close
next

'else

''''''''''''''''Here
'CONST QT4 = """"

  'axn = TRIM(request("axn"))
             
             
                'FromDate = cDate(Request("FromDate"))
               ' If strComp(FromDate, "Click here to select date...", 0) = 0 Then
                 ' alert_goback "Please select a valid Starting Date to continue.", "-1"
               ' End IF
               
               ' ToDate = cDate(Request("ToDate"))
              '  If strComp(ToDate, "Click here to select date...", 0) = 0 Then
                 ' alert_goback "Please select a valid Ending Date to continue.", "-1"
              '  End IF
               
               ' If FromDate > ToDate Then
                 ' alert_goback "Your ending date can not be greater than your starting date.\nPlease select valid dates to continue.", "-1"
               ' End If
               
               ' where = " TC.AccountNumber = '"& CustomerNumber &"' AND TC.TransDate between '"& cDate(request("FromDate")) &"' AND '"& cDate(request("ToDate")) &"'"
             
                     
                       

             
               
             ' Set RS4 = Server.CreateObject("ADODB.RecordSet")
                           
               
              'SQL4 = "SELECT CL.CardDescriptn, TC.CardNumber, convert(varchar, TC.TransDate, 101) as date, TC.TransTime as Time, TC.Odometer, PC.LongDesc, rtrim(round(convert(decimal(10,1), TC.TransQuantity), 1)) as Units, rtrim(round(convert(decimal(10,2), TC.UnitPriceInv), 2)) as price, rtrim(round(convert(decimal(10,2), TC.TransQuantity * TC.UnitPriceInv), 2)) AS TotalCost, TC.VehicleNumber, TC.KeyboardInput as MiscKbd, TC.sitenumber, TC.ForeignSiteCode, TC.AccountNumber from TransCurrent AS TC left outer join CardList AS CL on TC.CardNumber = CL.CardNumber inner join ProductCodes as PC on TC.productcode = PC.productcode where "

              'SQL4 = SQL4 & where
             ' SQL4 = SQL4 & " order by TC.CardNumber, TC.TransDate"
             
                    'RS4.Open SQL4, dsnconn
                    
             


'For f = 0 To RS4.Fields.Count-1
                'hdr = hdr & "," & QT1 & RS4.Fields(f).Name & QT1
            'Next
'Response.Write Mid(hdr,2) & vbNewLine ' lop off leading comma

'prefix = QT4
'midfix = QT4 & "," & QT4
'suffix = QT4 & vbNewLine
'rows = RS4.getString( , , midfix, suffix & prefix )
'Response.Write prefix & Left(rows, Len(rows) - Len(prefix) )
'RS4.Close
end if

      %>
   

 
0
Comment
Question by:jstew1232003
  • 2
  • 2
4 Comments
 
LVL 22

Expert Comment

by:neeraj523
ID: 26177445
Hello

What issue you face when it dosnt work ? provide details of the output coming and the desired one.

It is tough to debug long code without knowing the db structure etc..
0
 

Author Comment

by:jstew1232003
ID: 26177565
OK, I'll try. WHen it doesn't work, it gives me page cannot be displayed, 404 error. the structure of the database is weird. There is one column in the database that holds child accounts comma delimted. So... the code that I have above gets this column in a loop and parses the account numbers and attempts to create the csv file. I can get screen shots of the DB structure if u need it. It's a weird design, i did not design it, just trying to write the code for it. I hope this makes sense, but if you need the DB structure, I will provide that.
0
 
LVL 22

Expert Comment

by:neeraj523
ID: 26178618
yes, better if you can provide me db structure in sql format so that i replicate this db locally and write the code to generate desired csv.. also provide desired csv structure..
0
 

Accepted Solution

by:
jstew1232003 earned 0 total points
ID: 26178810
Heres all the database tables.  And an example of the csv file that i need to generate cause it worked a couple of times but stopped for some reason. Let me know if you need anything else.
Pac.zip
exportByDateResults.csv
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
Strategic internal linking is often considered an SEO power technique, especially for content marketing. Do you need to hire an SEO agency to optimize you internal linking? No, this article will help you understand the basics of internal linking and…
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses
Course of the Month19 days, 21 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question