<%@ Language=VBScript %> <% if Request.Form("type")<>"" then set dbConnection = server.CreateObject ("ADODB.Connection") dbConnection.ConnectionString = strConnection dbConnection.Open strSQL = Request.Form("SQL") Set rs = server.CreateObject ("ADODB.Recordset") rs.Open strSQL, dbConnection if Request.Form("records")="page" then ' Pagination: if NOT rs.EOF then mypage = Request.Form("mypage") rs.PageSize = Request.Form("pagesize") maxRecords = cdbl(rs.RecordCount) maxPages=cdbl(rs.PageCount) if cdbl(mypage) > cdbl(maxPages) then mypage = maxpages End IF rs.AbsolutePage = mypage maxrecs=cdbl(rs.pagesize) End IF end if Response.Expires=0 ' prevent caching Response.Buffer=True Server.ScriptTimeout = 120 if Request.Form("type")="excel" then Call ExportToExcel elseif Request.Form("type")="word" then Call ExportToWord elseif Request.Form("type")="xml" then Call ExportToXML elseif Request.Form("type")="csv" then Call ExportToCSV end if rs.Close set rs=Nothing dbConnection.Close set dbConnection = Nothing else %>

"> > >

Data range Output format All records Current page only Excel Word CSV (comma separated values) XML

<% Response.End end if Sub ExportToExcel Response.ContentType = "application/vnd.ms-excel" Response.AddHeader "Content-Disposition", "attachment;Filename=Results.xls" Response.Write "" Response.Write "" Call WriteTableData Response.Write "" Response.Write "" Response.Write "" End Sub Sub ExportToWord Response.ContentType = "application/vnd.ms-word" Response.AddHeader "Content-Disposition", "attachment;Filename=Results.doc" Response.Write "" Response.Write "" Call WriteTableData Response.Write "" Response.Write "" Response.Write "" End Sub Sub ExportToXML Response.ContentType = "text/xml" Response.addheader "Content-Disposition", "attachment;Filename=Results.xml" 'Const adPersistXML = 1 'rs.Save Response, adPersistXML if rs.eof then exit sub set xmlDocument = Server.CreateObject("MSXML.DOMDocument") set xmlRoot = xmlDocument.createElement("rs") iNumberOfRows = 0 DO UNTIL rs.eof OR ( iNumberOfRows>=CInt(Request.Form("pagesize")) and _ Request.Form("records")="page" ) Set xmlRow = xmlDocument.createElement("row") call xmlRoot.appendChild(xmlRow) for i=0 to rs.Fields.Count-1 Set xmlField = xmlDocument.createElement(XMLNameEncode(Label(rs.Fields(i).Name))) str = GetData(rs.Fields(i), Format(rs.Fields(i).Name)) if str = "" or isNull(str) then str="" xmlField.Text = Replace(str, chr(149), "") Call xmlRow.AppendChild(xmlField) next rs.MoveNext iNumberOfRows = iNumberOfRows + 1 loop Response.Write "" & vbcrlf & xmlRoot.xml End Sub Sub ExportToCSV Response.ContentType = "application/csv" Response.addheader "Content-Disposition", "attachment;Filename=Results.csv" if rs.eof then exit sub ' write header for i=0 to rs.Fields.Count-1 Response.Write """" & Label(rs.Fields(i).Name) & """" if i<>rs.Fields.Count-1 then Response.Write ", " next Response.Write vbCRLF ' write data rows iNumberOfRows = 0 DO UNTIL rs.eof OR ( iNumberOfRows>=CInt(Request.Form("pagesize")) and _ Request.Form("records")="page" ) for i=0 to rs.Fields.Count-1 Response.Write """" & GetData(rs.Fields(i), Format(rs.Fields(i).Name)) & """" if i<>rs.Fields.Count-1 then Response.Write ", " next Response.Write vbCRLF rs.MoveNext iNumberOfRows = iNumberOfRows + 1 loop End Sub Sub WriteTableData if rs.eof then exit sub ' write header Response.Write "" for i=0 to rs.Fields.Count-1 Response.Write "" & Label(rs.Fields(i).Name) & "" next Response.Write "" ' write data rows iNumberOfRows = 0 DO UNTIL rs.eof OR ( iNumberOfRows>=CInt(Request.Form("pagesize")) _ and Request.Form("records")="page") Response.Write "" for i=0 to rs.Fields.Count-1 Response.Write "" & GetData(rs.Fields(i), Format(rs.Fields(i).Name)) & "" next Response.Write "" rs.MoveNext iNumberOfRows = iNumberOfRows + 1 loop End Sub Function XMLNameEncode(strValue) XMLNameEncode = Replace(strValue, " ", "") XMLNameEncode = Replace(XMLNameEncode, "#", "") XMLNameEncode = Replace(XMLNameEncode, "'", "") XMLNameEncode = Replace(XMLNameEncode, "/", "") XMLNameEncode = Replace(XMLNameEncode, "\", "") XMLNameEncode = Replace(XMLNameEncode, "(", "") XMLNameEncode = Replace(XMLNameEncode, ")", "") XMLNameEncode = Replace(XMLNameEncode, ",", "") XMLNameEncode = Replace(XMLNameEncode, "[", "") XMLNameEncode = Replace(XMLNameEncode, "]", "") XMLNameEncode = Replace(XMLNameEncode, "+", "") XMLNameEncode = Replace(XMLNameEncode, """", "") XMLNameEncode = Replace(XMLNameEncode, "-", "") XMLNameEncode = Replace(XMLNameEncode, "_", "") XMLNameEncode = Replace(XMLNameEncode, "|", "") XMLNameEncode = Replace(XMLNameEncode, "}", "") XMLNameEncode = Replace(XMLNameEncode, "{", "") End Function %>