campagnolo1
Technical User
Greetings everyone!
I have been banging my head against the wall for a while now trying to figure out how to solve this particular problem.
I have an asp page that displays a record set in a table. I want to export that table to either a csv and xml format. I have benn reading post and blogs but can for the life of me not get it right. The table shows a list of routes flown by a pilot. Here is the original page with the table:
I know it's quite a mouthfull. So I have tried to export this to a csv file, and below is what I came up with. Problem is, that it shows everything in the csv file, meaning it shows all the page code as well as all the routes for the hub, not just the ones flown by a particular pilot.
Any and every assistance in helping me solve this would be greatly appreciated!
Cheers,
Chris
I have been banging my head against the wall for a while now trying to figure out how to solve this particular problem.
I have an asp page that displays a record set in a table. I want to export that table to either a csv and xml format. I have benn reading post and blogs but can for the life of me not get it right. The table shows a list of routes flown by a pilot. Here is the original page with the table:
Code:
<% Option Explicit %>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] lang="en">
<!-- #BeginTemplate "../westwind.dwt" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252" />
<meta name="keywords" content="" />
<!-- #BeginEditable "doctitle" -->
<title>View Hub Slam</title>
<!-- #EndEditable -->
<link rel="stylesheet" type="text/css" href="../westwind.css" />
<!-- #BeginEditable "style" -->
<style type="text/css">
.even_row {
background: lightgrey;
}
.alert1 {
color: black;
background: yellow;
}
.alert2 {
color: white;
background: red;
}
</style>
<script language="javascript" type="text/javascript"><!--//--><![CDATA[//><!--
sfHover = function() {
var sfEls = document.getElementById("nav4").getElementsByTagName("LI");
for (var i=0; i<sfEls.length; i++) {
sfEls[i].onmouseover=function() {
this.className+=" sfhover";
}
sfEls[i].onmouseout=function() {
this.className=this.className.replace(new RegExp(" sfhover\\b"), "");
}
}
}
if (window.attachEvent) window.attachEvent("onload", sfHover);
//--><!]]></script>
<!-- #EndEditable -->
</head>
<body>
<!--#include virtual="/page_header.asp"-->
<!-- #BeginEditable "content" -->
<!--#include virtual="/Roster/check_roles.asp"-->
<!--#include virtual="/pagelinks.asp"-->
<%
Dim SlamIncomplete
SlamIncomplete = FALSE
Function DateString(vDateTime)
DateString=Year(vDateTime)& "-" & _
Month(vDateTime) & "-" & _
Day(vDateTime) & " " & _
Trim(FormatDateTime(vDateTime,vbShortTime)) & ":00"
End Function
Function AirportName(AirportICAO)
Dim RSAirportName, cmdAirportName
Set cmdAirportName=Server.CreateObject("ADODB.Command")
Set RSAirportName=Server.CreateObject("ADODB.Recordset")
cmdAirportName.ActiveConnection=objConn
cmdAirportName.CommandType=1
cmdAirportName.CommandText="SELECT * FROM tbl_AirportCodes WHERE AirportCode=?"
cmdAirportName.Parameters.Append cmdAirportName.CreateParameter("AirportCode", 200, 1, 4, AirportICAO)
cmdAirportName.ActiveConnection=objConn
RSAirportName.Open cmdAirportName,,3
AirportName=""
IF not (isnull(RSAirportName("Name")) OR LEN(RSAirportName("Name")) = 0) Then
AirportName = AirportName & RSAirportName("Name") & ", "
END IF
IF not (isnull(RSAirportName("City")) OR LEN(RSAirportName("City")) = 0) Then
AirportName = AirportName & RSAirportName("City") & ", "
END IF
IF not (isnull(RSAirportName("State")) OR LEN(RSAirportName("State")) = 0) Then
AirportName = AirportName & RSAirportName("State") & ", "
END IF
IF not (isnull(RSAirportName("Country")) OR LEN(RSAirportName("Country")) = 0) Then
AirportName = AirportName & RSAirportName("Country") & " "
END IF
AirportName=AirportName & "(" & RSAirportName("AirportCode") & ")"
RSAirportName.Close
Set cmdAirportName=Nothing
End Function
Function CheckRoute(From_ICAO, To_ICAO, Pilot, CircularRoute)
Dim RSCheckRoute, cmdCheckRoute, strSQL
IF CircularRoute = FALSE THEN
Set cmdCheckRoute=Server.CreateObject("ADODB.Command")
Set RSCheckRoute=Server.CreateObject("ADODB.Recordset")
cmdCheckRoute.ActiveConnection=objConn
cmdCheckRoute.CommandType=1
cmdCheckRoute.CommandText="SELECT COUNT(*) as N FROM FlightLog WHERE PilotNumber=? AND Completed=1 AND Origin=? AND Destination=?"
cmdCheckRoute.Parameters.Append cmdCheckRoute.CreateParameter("PilotID", 200, 1, 7, PilotID)
cmdCheckRoute.Parameters.Append cmdCheckRoute.CreateParameter("Origin", 200, 1, 4, From_ICAO)
cmdCheckRoute.Parameters.Append cmdCheckRoute.CreateParameter("Destination", 200, 1, 4, To_ICAO)
cmdCheckRoute.ActiveConnection=objConn
RSCheckRoute.Open cmdCheckRoute,,3
IF RSCheckRoute("N") > "0" THEN
CheckRoute = "Flown"
ELSE
CheckRoute = "Not Flown"
SlamIncomplete = TRUE
LegsNotFlown = LegsNotFlown + 1
END IF
Legs = Legs + 1
ELSE
CheckRoute = "N/A"
END IF
END Function
' This page requires pilot login
IF Session("PilotAuthenticated") = False Then
Set RS=Nothing
objConn.Close
Set objConn=Nothing
Response.Redirect "/LogIn/pilot_login.asp?LOGIN=REQUIRED&URL=" & Request.ServerVariables("URL")
End If
Dim PilotID, strPage, strLeg, Rows
Dim strPilotName, strPilotHub, Even_row, LegsNotFlown, Legs
If Request.QueryString("PilotID")="" Then
PilotID=Session("PilotNumber")
Else
PilotID=UCase(Left(Trim(Request.Querystring("PilotID")),7))
End If
If Left(PilotID,3) <> "WWA" Or Not IsNumeric(Mid(PilotID,4)) Then
Set RS=Nothing
objConn.Close
Set objConn=Nothing
Response.Write "Invalid PilotID"
Response.End
End If
' Get pilot name, rating, hub
Set cmd=Server.CreateObject("ADODB.Command")
cmd.ActiveConnection=objConn
cmd.CommandText="SELECT Name,Hub_ICAO AS Hub FROM Roster WHERE Number=?"
cmd.CommandType=1
cmd.Parameters.Append cmd.CreateParameter("PilotID", 200, 1, 7, PilotID)
RS.Open cmd
If RS.BOF And RS.EOF Then
' Invalid pilot number
Response.Write "<p>Invalid Pilot ID</p>"
RS.Close
Set RS=Nothing
Set cmd=Nothing
objConn.Close
Set objConn=Nothing
End If
strPilotName=RS("Name")
strPilotHub=RS("Hub")
RS.Close
' Get all of the routes in the hub
Set cmd=Server.CreateObject("ADODB.Command")
cmd.ActiveConnection=objConn
strSQL="SELECT * FROM RoutesPax WHERE Origin_ICAO = '" & strPilotHub & "' ORDER BY RouteID"
cmd.CommandText=strSQL
cmd.CommandType=1
RS.Open cmd
' Display All routes
%>
<div id="container">
<h1>Hub Routes</h1>
<h5><%=strPilotName%> - <%=strPilotHub%></h5>
<p><a href="download_hub_slam_csv.asp?PilotID=<%=PilotID%>">Download hub slam (CSV format)</a></p>
<table>
<thead>
<tr>
<td>Route #</td>
<td># of Legs</td>
<td>Leg Details</td>
<td>Outbound Flown</td>
<td>Inbound Flown</td>
</tr>
</thead>
<tbody>
<%
If Not (RS.BOF And RS.EOF) Then
Do While Not RS.EOF
%>
<tr <% If even_row=1 Then
Response.Write "class=""even_row"""
End If
Rows = RS("Stops") + 1
%>
>
<td rowspan=<%=Rows%>><%=RS("RouteID")%></td>
<td rowspan=<%=Rows%>><%=RS("Stops") + 1 %></td>
<%
dim Code1, Code2
IF RS("Stops") = 0 THEN
Code1 = RS("Origin_ICAO")
Code2 = RS("Destination_ICAO")
strLeg = AirportName(Code1) & " to " & AirportName(Code2)
%><td><%=strLeg%></td>
<td><%=CheckRoute(Code1, Code2, PilotID, FALSE)%></td>
<td><%=CheckRoute(Code2, Code1, PilotID, FALSE)%></td>
<%
ELSE
dim iCount, CircularRoute
CircularRoute = (RS("origin_ICAO") = RS("Destination_ICAO"))
Code1 = RS("Origin_ICAO")
Code2 = RS("Stop1_ICAO")
strLeg = AirportName(Code1) & " to " & AirportName(Code2)
%><td><%=strLeg%></td>
<td><%=CheckRoute(Code1, Code2, PilotID, FALSE)%></td>
<td><%=CheckRoute(Code2, Code1, PilotID,CircularRoute)
%></td>
</tr>
<%
For iCount = 1 to RS("Stops") - 1
Code1 = RS("Stop" & iCount & "_ICAO")
Code2 = RS("Stop" & iCount + 1 & "_ICAO")
strLeg = AirportName(Code1) & " to " & AirportName(Code2)
%>
<tr
<%
If even_row=1 Then
Response.Write "class=""even_row"""
End If
%>
>
<td><%=strLeg%></td>
<td><%=CheckRoute(Code1, Code2, PilotID, FALSE)%></td>
<td><%=CheckRoute(Code2, Code1, PilotID, CircularRoute)%></td>
</tr>
<%
Next
Code1 = RS("Stop" & RS("Stops") & "_ICAO")
Code2 = RS("Destination_ICAO")
strLeg = AirportName(Code1) & " to " & AirportName(Code2)
%><tr <%
If even_row=1 Then
Response.Write "class=""even_row"""
End If
%>
>
<td><%=strLeg%></td>
<td><%=CheckRoute(Code1, Code2, PilotID, FALSE)%></td>
<td><%=CheckRoute(Code2, Code1, PilotID, CircularRoute)%></td>
</tr>
<%
END IF
If even_row=0 Then
even_row=1
Else
even_row=0
End If
RS.MoveNext
Loop
RS.Close
Set cmd=Nothing
END IF
%>
</tbody>
</table>
</div>
<%
IF SlamIncomplete THEN
%>
<p>Slam Incomplete (<%=LegsNotFlown %> of <%=Legs %> legs not flown)</p>
<%
ELSE
%>
<p>Slam Complete</p>
<%
END IF
%>
</div>
<!-- #EndEditable -->
<!--#include virtual="/page_footer.asp"-->
</body>
<!-- #EndTemplate -->
</html>I know it's quite a mouthfull. So I have tried to export this to a csv file, and below is what I came up with. Problem is, that it shows everything in the csv file, meaning it shows all the page code as well as all the routes for the hub, not just the ones flown by a particular pilot.
Code:
<% Option Explicit %>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] lang="en">
<!-- #BeginTemplate "../westwind.dwt" -->
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252" />
<meta name="keywords" content="" />
<!-- #BeginEditable "doctitle" -->
<title>Download Hub Slam CSV</title>
<!-- #EndEditable -->
<link rel="stylesheet" type="text/css" href="../westwind.css" />
<!-- #BeginEditable "style" -->
<script language="javascript" type="text/javascript"><!-- //--><![CDATA[//><!--
sfHover = function () {
var sfEls = document.getElementById("nav4").getElementsByTagName("LI");
for (var i = 0; i < sfEls.length; i++) {
sfEls[i].onmouseover = function () {
this.className += " sfhover";
}
sfEls[i].onmouseout = function () {
this.className = this.className.replace(new RegExp(" sfhover\\b"), "");
}
}
}
if (window.attachEvent) window.attachEvent("onload", sfHover);
//--><!]]></script>
<!-- #EndEditable -->
</head>
<body>
<!--#include virtual="/page_header.asp"-->
<!-- #BeginEditable "content" -->
<%
Function DateString(vDateTime)
DateString=Year(vDateTime)& "-" & _
Month(vDateTime) & "-" & _
Day(vDateTime) & " " & _
Trim(FormatDateTime(vDateTime,vbShortTime)) & ":00"
End Function
Function AirportName(AirportICAO)
Dim RSAirportName, cmdAirportName
Set cmdAirportName=Server.CreateObject("ADODB.Command")
Set RSAirportName=Server.CreateObject("ADODB.Recordset")
cmdAirportName.ActiveConnection=objConn
cmdAirportName.CommandType=1
cmdAirportName.CommandText="SELECT * FROM tbl_AirportCodes WHERE AirportCode=?"
cmdAirportName.Parameters.Append cmdAirportName.CreateParameter("AirportCode", 200, 1, 4, AirportICAO)
cmdAirportName.ActiveConnection=objConn
RSAirportName.Open cmdAirportName,,3
AirportName=""
IF not (isnull(RSAirportName("Name")) OR LEN(RSAirportName("Name")) = 0) Then
AirportName = AirportName & RSAirportName("Name") & ", "
END IF
IF not (isnull(RSAirportName("City")) OR LEN(RSAirportName("City")) = 0) Then
AirportName = AirportName & RSAirportName("City") & ", "
END IF
IF not (isnull(RSAirportName("State")) OR LEN(RSAirportName("State")) = 0) Then
AirportName = AirportName & RSAirportName("State") & ", "
END IF
IF not (isnull(RSAirportName("Country")) OR LEN(RSAirportName("Country")) = 0) Then
AirportName = AirportName & RSAirportName("Country") & " "
END IF
AirportName=AirportName & "(" & RSAirportName("AirportCode") & ")"
RSAirportName.Close
Set cmdAirportName=Nothing
End Function
Function CheckRoute(From_ICAO, To_ICAO, Pilot, CircularRoute)
Dim RSCheckRoute, cmdCheckRoute, strSQL
IF CircularRoute = FALSE THEN
Set cmdCheckRoute=Server.CreateObject("ADODB.Command")
Set RSCheckRoute=Server.CreateObject("ADODB.Recordset")
cmdCheckRoute.ActiveConnection=objConn
cmdCheckRoute.CommandType=1
cmdCheckRoute.CommandText="SELECT COUNT(*) as N FROM FlightLog WHERE PilotNumber=? AND Completed=1 AND Origin=? AND Destination=?"
cmdCheckRoute.Parameters.Append cmdCheckRoute.CreateParameter("PilotID", 200, 1, 7, PilotID)
cmdCheckRoute.Parameters.Append cmdCheckRoute.CreateParameter("Origin", 200, 1, 4, From_ICAO)
cmdCheckRoute.Parameters.Append cmdCheckRoute.CreateParameter("Destination", 200, 1, 4, To_ICAO)
cmdCheckRoute.ActiveConnection=objConn
RSCheckRoute.Open cmdCheckRoute,,3
IF RSCheckRoute("N") > "0" THEN
CheckRoute = "Flown"
ELSE
CheckRoute = "Not Flown"
SlamIncomplete = TRUE
LegsNotFlown = LegsNotFlown + 1
END IF
Legs = Legs + 1
ELSE
CheckRoute = "N/A"
END IF
END Function
' This page requires pilot login
IF Session("PilotAuthenticated") = False Then
Set RS=Nothing
objConn.Close
Set objConn=Nothing
Response.Redirect "/LogIn/pilot_login.asp?LOGIN=REQUIRED&URL=" & Request.ServerVariables("URL")
End If
Dim PilotID, strPage, strLeg, Rows
Dim strPilotName, strPilotHub, Even_row, LegsNotFlown, Legs
If Request.QueryString("PilotID")="" Then
PilotID=Session("PilotNumber")
Else
PilotID=UCase(Left(Trim(Request.Querystring("PilotID")),7))
End If
If Left(PilotID,3) <> "WWA" Or Not IsNumeric(Mid(PilotID,4)) Then
Set RS=Nothing
objConn.Close
Set objConn=Nothing
Response.Write "Invalid PilotID"
Response.End
End If
' Get pilot name, rating, hub
Set cmd=Server.CreateObject("ADODB.Command")
cmd.ActiveConnection=objConn
cmd.CommandText="SELECT Name,Hub_ICAO AS Hub FROM Roster WHERE Number=?"
cmd.CommandType=1
cmd.Parameters.Append cmd.CreateParameter("PilotID", 200, 1, 7, PilotID)
RS.Open cmd
If RS.BOF And RS.EOF Then
' Invalid pilot number
Response.Write "<p>Invalid Pilot ID</p>"
RS.Close
Set RS=Nothing
Set cmd=Nothing
objConn.Close
Set objConn=Nothing
End If
strPilotName=RS("Name")
strPilotHub=RS("Hub")
RS.Close
' Path to file DSN
sDSNDir=Server.MapPath("\_dsn")
DIM RS1
Set RS1=Server.CreateObject("ADODB.RECORDSET")
RS1.Open "SELECT * FROM RoutesPax WHERE Origin_ICAO = '" & strPilotHub & "' ORDER BY RouteID", "filedsn=" & sDSNDir & "\" & "ww_mysql.dsn", 0, 1
Dim strFileName
strFileName=Trim(PilotID) & "_Hub_Slam.csv"
Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "attachment;filename=" & strFileName
Write_CSV_From_Recordset RS1
sub Write_CSV_From_Recordset( RS )
if RS.EOF then
'
' There is no data to be written
'
exit sub
end if
dim RX
set RX = new RegExp
RX.Pattern = "\r|\n|,|"""
dim i
dim Field
dim Separator
'
' Writing the header row (header row contains field names)
'
Separator = ""
for i = 0 to RS.Fields.Count - 1
Field = RS.Fields( i ).Name
if RX.Test( Field ) then
'
' According to recommendations:
' - Fields that contain CR/LF, Comma or Double-quote should be enclosed in double-quotes
' - Double-quote itself must be escaped by preceeding with another double-quote
'
Field = """" & Replace( Field, """", """""" ) & """"
end if
Response.Write Separator & Field
Separator = ","
next
Response.Write vbNewLine
'
' Writing the data rows
'
do until RS.EOF
Separator = ""
for i = 0 to RS.Fields.Count - 1
'
' Note the concatenation with empty string below
' This assures that NULL values are converted to empty string
'
Field = RS.Fields( i ).Value & ""
if RX.Test( Field ) then
Field = """" & Replace( Field, """", """""" ) & """"
end if
Response.Write Separator & Field
Separator = ","
next
Response.Write vbNewLine
RS.MoveNext
loop
end sub
%>
</body>
</html>Any and every assistance in helping me solve this would be greatly appreciated!
Cheers,
Chris