[X] My Assistant

[iamjesus1342027]

sorry i changed the code without thinking. try it again.

[sansclue]

sorry i changed the code without thinking. try it again.

No, it is still timing out.

[iamjesus1342027]

alright i attached the results because its too large to fit.

[sansclue]

alright i attached the results because its too large to fit.

Much better. It is a problem with your underlying data. The issue is some of the values from:

DictionaryField.LookupTable,
DictionaryField.LookupType

Are literally blank. Not all of them, just some. Unfortunately the ones in the first row of the getTables query are blank. When you use this syntax in the Lookup query:

#getTable.LookupType#

It is shorthand for "give me the value in the first row of the getTable query". Since that value is blank, that is why you are getting a syntax error.

[iamjesus1342027]

well is there anything i can do to fix this? I thought using that if statement would keep it from trying to use a empty field but i guess not.

[sansclue]

well is there anything i can do to fix this? I thought using that if statement would keep it from trying to use a empty field but i guess not.

Yes, you could use a separate query to only pull non-empty LookUpTable values. But I think there is a logic problem in the query/code. Mind you I do not understand the relationships, but a few things strike me as odd

1) Why are the lookup values empty in the first place
2) Even if all of the values were non-empty, they are not all the same:
ApplicationLookUp
ExploitationIEDLookup

Your "Lookup" query can only use one table, so how do you know which one is the right one to use for your query?

[iamjesus1342027]

alright i have narrowed things down a bit. i have this code where i am receiving an error.

CODE

<cfquery name="Lookup" datasource="#session.sysdb#">
                            SELECT LookupType, DisplayOrder, LookupValue
                            FROM #getTable.LookupTable#
                            WHERE LookupType = '#getTable.LookupType#'
                            ORDER BY DisplayOrder, LookupValue
                        </cfquery>                    
                        <ss:Cell ss:Index="1" ss:StyleID="BottomLeft"><ss:Data ss:Type="String">Lookup Values:</ss:Data></ss:Cell>
                        <ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="BottomRight"><ss:Data ss:Type="String"><cfset commaStopper = ""><cfloop query="Lookup">#commaStopper##LookupValue#<cfset commaStopper = ","></cfloop></ss:Data></ss:Cell>
                        </ss:Row>
                        <ss:Row></ss:Row>

i get the error XML parse error: missing end tag.

i have looked over this code a hundred times but cant find anything. can anyone else see what my problem is???

[sansclue]

I don't see anything obvious from that snippet other than the <ss:Row> tags are imbalanced.

[iamjesus1342027]

well i have figured out exactly what the problem is. for some reason excel doesnt like the fact that i have a defined a query in it. i am wondering if there is a namespace or something that i can define at the beginning of my code that will allow excel to deal with the query. this is my code that should work.

CODE

<cfsetting showdebugoutput="false">

<!--- Gets the report key from the url that was passed from the previous page (ExportToExcel.cfm) --->
<cfparam name="url.reportkey" default="None">

<!--- Get the name of the report to be used in the file name --->
<cfquery name="getReportName" datasource="#session.sysdb#">
    SELECT ReportName, ReportKey
    FROM DictionaryReport
    WHERE ReportKey = '#url.reportkey#'
</cfquery>
<cfoutput query="getReportName">
    <cfset ReportName=#ReportName#>
</cfoutput>

<!--- Tells coldfusion what type of file will be created (excel spreadsheet) and names the file --->
<cfheader name="content-disposition" value="inline;filename=#ReportName#ReportData.xls">
<cfcontent type="application/msexcel">

<!--- The queries to get all of the data for the spreadsheets --->
<cfquery name="getReport" datasource="#session.sysdb#">
    SELECT *
    FROM DictionaryReport
    WHERE ReportKey = '#url.reportkey#'
</cfquery>
<cfquery name="getTableName" datasource="#session.sysdb#">
    SELECT *
    FROM DictionaryTable
    WHERE DictionaryTable.ReportKey = '#url.reportkey#'
    ORDER BY DisplayOrder
</cfquery>
<cfquery name="getTable" datasource="#session.sysdb#">
    SELECT DictionaryTable.TableShortName, DictionaryTable.TableKey, DictionaryTable.TableDescription, DictionaryTable.DisplayOrder, DictionaryTable.ReportKey, DictionaryField.ReportKey, DictionaryField.TableKey, DictionaryField.FieldName, DictionaryField.FieldDataType, DictionaryField.IsPickList, DictionaryField.FieldLength, DictionaryField.DisplayOrder, DictionaryField.LookupTable, DictionaryField.LookupType, DictionaryField.FieldRequired, DictionaryField.FieldDescription
    FROM DictionaryTable
    INNER JOIN DictionaryField ON DictionaryTable.TableKey = DictionaryField.TableKey
    WHERE DictionaryTable.ReportKey = '#url.reportkey#'
    AND DictionaryField.ReportKey = '#url.reportkey#'
    ORDER BY DictionaryTable.DisplayOrder, DictionaryField.DisplayOrder
</cfquery>


<!--- Creates the excel file --->
<?xml version="1.0"?>
<ss:Workbook
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:x="urn:schemas-microsoft-com:office:excel">
    
    <!--- Creates the various styles/formats to be used on data --->
    <ss:Styles>
        <ss:Style ss:ID="Header">
            <ss:Font ss:FontName="Calibri" ss:Bold="1" ss:Size="15"/>
        </ss:Style>
        <ss:Style ss:ID="Description">
            <ss:Alignment ss:WrapText="1" ss:Vertical="Top"/>
            <ss:Borders>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Top"/>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Left"/>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Right"/>
            </ss:Borders>
        </ss:Style>
        <ss:Style ss:ID="TableDescription">
            <ss:Alignment ss:WrapText="1" ss:Vertical="Top"/>
            <ss:Borders>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Top"/>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Left"/>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Bottom"/>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Right"/>
            </ss:Borders>
        </ss:Style>
        <ss:Style ss:ID="Sides">
            <ss:Borders>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Left"/>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Right"/>
            </ss:Borders>
        </ss:Style>
        <ss:Style ss:ID="Ordinality">
            <ss:Alignment ss:Horizontal="Right"/>
        </ss:Style>
        <ss:Style ss:ID="Subheader">
            <ss:Font ss:FontName="Calibri" ss:Bold="1" ss:Size="13"/>
            <ss:Alignment ss:Horizontal="Right"/>
            <ss:Borders>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="2" ss:Position="Bottom"/>
            </ss:Borders>
        </ss:Style>
        <ss:Style ss:ID="TopLeft">
            <ss:Alignment ss:Horizontal="Right"/>
            <ss:Interior ss:Color="#CCCCCC" ss:Pattern="Solid"/>
            <ss:Borders>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Left"/>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Top"/>
            </ss:Borders>
        </ss:Style>
        <ss:Style ss:ID="Top">
            <ss:Alignment ss:Horizontal="Right"/>
            <ss:Interior ss:Color="#CCCCCC" ss:Pattern="Solid"/>
            <ss:Borders>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Top"/>
            </ss:Borders>
        </ss:Style>
        <ss:Style ss:ID="TopRight">
            <ss:Alignment ss:Horizontal="Right"/>
            <ss:Interior ss:Color="#CCCCCC" ss:Pattern="Solid"/>
            <ss:Borders>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Right"/>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Top"/>
            </ss:Borders>
        </ss:Style>
        <ss:Style ss:ID="DescriptionRight">
            <ss:Alignment ss:WrapText="1" ss:Vertical="Top"/>
            <ss:Borders>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Right"/>
            </ss:Borders>
        </ss:Style>
        <ss:Style ss:ID="Right">
            <ss:Borders>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Right"/>
            </ss:Borders>
        </ss:Style>
        <ss:Style ss:ID="Left">
            <ss:Alignment ss:Horizontal="Right"/>
            <ss:Borders>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Left"/>
            </ss:Borders>
        </ss:Style>
        <ss:Style ss:ID="BottomLeft">
            <ss:Alignment ss:Horizontal="Right"/>
            <ss:Borders>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Left"/>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Bottom"/>
            </ss:Borders>
        </ss:Style>
        <ss:Style ss:ID="BottomRight">
            <ss:Alignment ss:WrapText="1" ss:Vertical="Top"/>
            <ss:Borders>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Bottom"/>
                <ss:Border ss:LineStyle="Continuous" ss:Color="Black" ss:Weight="1" ss:Position="Right"/>
            </ss:Borders>
        </ss:Style>
    </ss:Styles>
    
    <!--- Creates the Worksheet and sets the page layout to landscape --->
    <ss:Worksheet ss:Name="Report Data">
        <x:WorksheetOptions>
            <x:PageSetup>
                <x:Layout x:Orientation="Landscape"/>
            </x:PageSetup>
        </x:WorksheetOptions>
        
        <!--- Sets up the table which will contain all of the data. Loops the queries to get
             all of the data and put it in the specific cells and rows. Also formats the
             cells, rows, and columns. --->
        <ss:Table>
            <ss:Column ss:Index="1" ss:Width="200"/>
            <ss:Column ss:Index="2" ss:Width="150"/>
            <ss:Column ss:Index="3" ss:Width="87"/>
            <ss:Column ss:Index="4" ss:Width="47"/>
            <ss:Column ss:Index="5" ss:Width="98"/>
            <cfoutput query="getReport">
                <ss:Row>
                    <ss:Cell ss:Index="1" ss:StyleID="Header"><ss:Data ss:Type="String">Report Name: #Module# #ReportType#</ss:Data></ss:Cell>
                </ss:Row>
            </cfoutput>
            <cfoutput query="getReport">
                <ss:Row>
                    <ss:Cell ss:Index="1" ss:StyleID="Ordinality"><ss:Data ss:Type="String">Description:</ss:Data></ss:Cell>
                    <cfif #ReportDescription# eq "">
                        <ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="TableDescription"><ss:Data ss:Type="String">No Description Entered</ss:Data></ss:Cell>
                        </ss:Row>
                        <ss:Row></ss:Row>
                    <cfelse>
                        <ss:Cell ss:Index="2" ss:MergeAcross="3" ss:MergeDown="5" ss:StyleID="TableDescription"><ss:Data ss:Type="String">#ReportDescription#</ss:Data></ss:Cell>
                        </ss:Row>
                        <ss:Row></ss:Row>
                        <ss:Row></ss:Row>
                        <ss:Row></ss:Row>
                        <ss:Row></ss:Row>
                        <ss:Row></ss:Row>
                        <ss:Row></ss:Row>
                    </cfif>
            </cfoutput>
            <ss:Row>
                <ss:Cell ss:Index="1" ss:StyleID="Subheader"><ss:Data ss:Type="String">Tables</ss:Data></ss:Cell>
                <ss:Cell ss:Index="2" ss:StyleID="Subheader"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
                <ss:Cell ss:Index="3" ss:StyleID="Subheader"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
                <ss:Cell ss:Index="4" ss:StyleID="Subheader"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
                <ss:Cell ss:Index="5" ss:StyleID="Subheader"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
            </ss:Row>
            <cfoutput query="getTableName">
                <ss:Row>
                    <ss:Cell ss:Index="1" ss:StyleID="Ordinality"><ss:Data ss:Type="String">#TableShortName#</ss:Data></ss:Cell>
                </ss:Row>
                <ss:Row>
                    <ss:Cell ss:Index="1" ss:StyleID="Ordinality"><ss:Data ss:Type="String">Description:</ss:Data></ss:Cell>
                    <cfif #TableDescription# eq "">
                        <ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="TableDescription"><ss:Data ss:Type="String">No Description Entered</ss:Data></ss:Cell>
                        </ss:Row>
                        <ss:Row></ss:Row>
                    <cfelse>
                        <ss:Cell ss:Index="2" ss:MergeAcross="3" ss:MergeDown="7" ss:StyleID="TableDescription"><ss:Data ss:Type="String">#TableDescription#</ss:Data></ss:Cell>
                        </ss:Row>
                        <ss:Row></ss:Row>
                        <ss:Row></ss:Row>
                        <ss:Row></ss:Row>
                        <ss:Row></ss:Row>
                        <ss:Row></ss:Row>
                        <ss:Row></ss:Row>
                        <ss:Row></ss:Row>
                        <ss:Row></ss:Row>    
                    </cfif>
            </cfoutput>
            <ss:Row></ss:Row>
            <ss:Row>
                <ss:Cell ss:Index="1" ss:StyleID="Subheader"><ss:Data ss:Type="String">Fields</ss:Data></ss:Cell>
                <ss:Cell ss:Index="2" ss:StyleID="Subheader"><ss:Data ss:Type="String">Table Name</ss:Data></ss:Cell>
                <ss:Cell ss:Index="3" ss:StyleID="Subheader"><ss:Data ss:Type="String">Data Type</ss:Data></ss:Cell>
                <ss:Cell ss:Index="4" ss:StyleID="Subheader"><ss:Data ss:Type="String">Length</ss:Data></ss:Cell>
                <ss:Cell ss:Index="5" ss:StyleID="Subheader"><ss:Data ss:Type="String">Field Required?</ss:Data></ss:Cell>
            </ss:Row>
            <cfoutput query="getTable">
                <ss:Row>
                    <ss:Cell ss:Index="1" ss:StyleID="TopLeft"><ss:Data ss:Type="String">#FieldName#</ss:Data></ss:Cell>
                    <ss:Cell ss:Index="2" ss:StyleID="Top"><ss:Data ss:Type="String">#TableShortName#</ss:Data></ss:Cell>
                    <ss:Cell ss:Index="3" ss:StyleID="Top"><ss:Data ss:Type="String">#FieldDataType#</ss:Data></ss:Cell>
                    <ss:Cell ss:Index="4" ss:StyleID="Top"><ss:Data ss:Type="Number">#FieldLength#</ss:Data></ss:Cell>
                    <cfif #FieldRequired# eq "0">
                        <ss:Cell ss:Index="5" ss:StyleID="TopRight"><ss:Data ss:Type="String">No</ss:Data></ss:Cell>
                    <cfelse>
                        <ss:Cell ss:Index="5" ss:StyleID="TopRight"><ss:Data ss:Type="String">Yes</ss:Data></ss:Cell>
                    </cfif>
                </ss:Row>
                <ss:Row>
                    <ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String">Description:</ss:Data></ss:Cell>
                    <cfif #FieldDescription# eq "">
                        <ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="DescriptionRight"><ss:Data ss:Type="String">No Description Entered</ss:Data></ss:Cell>
                        </ss:Row>
                        <ss:Row>
                            <ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
                            <ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="Right"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
                        </ss:Row>
                    <cfelse>
                        <ss:Cell ss:Index="2" ss:MergeAcross="3" ss:MergeDown="10" ss:StyleID="DescriptionRight"><ss:Data ss:Type="String">#FieldDescription#</ss:Data></ss:Cell>
                        </ss:Row>
                        <ss:Row>
                            <ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
                        </ss:Row>
                        <ss:Row>
                            <ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
                        </ss:Row>
                        <ss:Row>
                            <ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
                        </ss:Row>
                        <ss:Row>
                            <ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
                        </ss:Row>
                        <ss:Row>
                            <ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
                        </ss:Row>
                        <ss:Row>
                            <ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
                        </ss:Row>
                        <ss:Row>
                            <ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
                        </ss:Row>
                        <ss:Row>
                            <ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
                        </ss:Row>
                        <ss:Row>
                            <ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
                        </ss:Row>
                        <ss:Row>
                            <ss:Cell ss:Index="1" ss:StyleID="Left"><ss:Data ss:Type="String"></ss:Data></ss:Cell>
                        </ss:Row>
                    </cfif>
                <ss:Row>
                    <cfif #IsPickList# eq "0" OR #IsPickList# eq "">
                        <ss:Cell ss:Index="1" ss:StyleID="BottomLeft"><ss:Data ss:Type="String">Lookup Values:</ss:Data></ss:Cell>
                        <ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="BottomRight"><ss:Data ss:Type="String">This field is not a picklist</ss:Data></ss:Cell>
                        
                    <cfif #LookupTable# eq "" OR #LookupType# eq "">
                        <ss:Cell ss:Index="1" ss:StyleID="BottomLeft"><ss:Data ss:Type="String">Lookup Values:</ss:Data></ss:Cell>
                        <ss:Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="BottomRight"><ss:Data ss:Type="String">The lookup table and type are not defined</ss:Data></ss:Cell>
                    <cfelse>
                        <cfquery name="Lookup" datasource="#session.sysdb#">
                            SELECT LookupType, DisplayOrder, LookupValue
                            FROM #getTable.LookupTable#
                            WHERE LookupType = '#getTable.LookupType#'
                            ORDER BY DisplayOrder, LookupValue
                        </cfquery>                    
                        <ss:Cell ss:Index="1" ss:StyleID="BottomLeft"><ss:Data ss:Type="String">Lookup Values:</ss:Data></ss:Cell>
                        <ss:Cell ss:Index="2" ss:MergeAcross="3" ss:MergeDown="5" ss:StyleID="BottomRight"><ss:Data ss:Type="String"><cfset commaStopper = ""><cfloop query="Lookup">#commaStopper# #LookupValue#<cfset commaStopper = ","></cfloop></ss:Data></ss:Cell>
                    </cfif>
                </ss:Row>
                <ss:Row></ss:Row>
                <ss:Row></ss:Row>
                <ss:Row></ss:Row>
                <ss:Row></ss:Row>
                <ss:Row></ss:Row>
                <ss:Row></ss:Row>
            </cfoutput>
        </ss:Table>
    </ss:Worksheet>
</ss:Workbook>

the lookup query is within the cfoutput tags for the get table query which allows it to properly reference the values. also i have the if statement which prevents the query from being read with an empty field. the reason i know this code should work is because i tested it like this.

CODE

<cfsetting showdebugoutput="false">

<!--- Gets the report key from the url that was passed from the previous page (ExportToExcel.cfm) --->
<cfparam name="url.reportkey" default="None">

<!--- Get the name of the report to be used in the file name --->
<cfquery name="getReportName" datasource="#session.sysdb#">
    SELECT ReportName, ReportKey
    FROM DictionaryReport
    WHERE ReportKey = '#url.reportkey#'
</cfquery>
<cfoutput query="getReportName">
    <cfset ReportName=#ReportName#>
</cfoutput>


<!--- The queries to get all of the data for the spreadsheets --->
<cfquery name="getReport" datasource="#session.sysdb#">
    SELECT *
    FROM DictionaryReport
    WHERE ReportKey = '#url.reportkey#'
</cfquery>
<cfquery name="getTableName" datasource="#session.sysdb#">
    SELECT *
    FROM DictionaryTable
    WHERE DictionaryTable.ReportKey = '#url.reportkey#'
    ORDER BY DisplayOrder
</cfquery>
<cfquery name="getTable" datasource="#session.sysdb#">
    SELECT DictionaryTable.TableShortName, DictionaryTable.TableKey, DictionaryTable.TableDescription, DictionaryTable.DisplayOrder, DictionaryTable.ReportKey, DictionaryField.ReportKey, DictionaryField.TableKey, DictionaryField.FieldName, DictionaryField.FieldDataType, DictionaryField.IsPickList, DictionaryField.FieldLength, DictionaryField.DisplayOrder, DictionaryField.LookupTable, DictionaryField.LookupType, DictionaryField.FieldRequired, DictionaryField.FieldDescription
    FROM DictionaryTable
    INNER JOIN DictionaryField ON DictionaryTable.TableKey = DictionaryField.TableKey
    WHERE DictionaryTable.ReportKey = '#url.reportkey#'
    AND DictionaryField.ReportKey = '#url.reportkey#'
    ORDER BY DictionaryTable.DisplayOrder, DictionaryField.DisplayOrder
</cfquery>

<cfoutput query="getTable">
<cfif #IsPickList# eq "0" OR #IsPickList# eq "">
    
    its not a picklist
<cfelseif #LookupType# eq "" OR #LookupTable# eq "">
none

<cfelse>
<cfquery name="Lookup" datasource="#session.sysdb#">
        SELECT *
        FROM #getTable.LookupTable#
        WHERE LookupType = '#getTable.LookupType#'
        ORDER BY DisplayOrder, LookupValue
    </cfquery>
    <cfset commaStopper = "">
    <cfloop query="Lookup">
    #commaStopper# #LookupValue#
    <cfset commaStopper = ",">
    </cfloop>
</cfif>
<br>
</cfoutput>

this code properly loops both queries and doesnt have a problem. this second code just simply displays the output on a webpage. so i have determined that excel is having the problem running the code. if there are any experts on exporting to excel please help! thanks!

[sansclue]

i am wondering if there is a namespace or something that i can define at the beginning of my code that will allow excel to deal with the query.

No. There isn't.

If your code results in malformed output (from Excel's perspective) it will not be able to parse it. It is not a good idea to mix queries and output code as your doing for exactly this reason: it generate extra output. In some cases using cfsetting, cfsilent, etc.. helps suppress extraneous output, but it all depends on your code.

If bad output is the problem, you have to reformat the code to generate valid output. One option is use cfsavecontent to save the output to a string. Then output the string after your header and cfcontent. But if you don't find out what part of your code is producing the bad/extra output, that still won't help.

<cfheader name="content-disposition" value="inline;filename=#ReportName#ReportData.xls">
<cfcontent type="application/msexcel">
<!--- output the string here --->

Good Luck

[iamjesus1342027]

alright its done. i just wanted to update everyone and anyone who may be reading this topic. my code actually works. the problem was that the lookup query was trying to reference a table that didnt exist. one of the table names that was looped in actually referenced a table that was in a different database than the one i was using. so all i had to do was write a simple if statement saying if it was this table then use this other database and voila! i just want to thank sansclue for all youre help. and im sure ill be back on here soon with more questions. lol. but for now this one is done!

[iamjesus1342027]

well i thought i was done but was wrong. . i added my page onto our server and am having a strange problem. when i run the page in firefox it works just fine. but running the page in IE doesnt produce the excel document. it just produces a document that has all of my xml in it. all of the coldfusion code is executing and i have the data but the xml code is not read properly and it doesnt create an excel file. this only happens in IE. any ideas?!?