Your expression might be missing an ending "#" & SQL Query

[gatetec]

I have a sql query (SQL Server 2000) for CFMX:

SELECT @Col_with_No_Pound = @Col_with_No_Pound + CASE @Col_with_No_Pound WHEN '' THEN '' ELSE ',' END + COLUMN_NAME,
@Col_with_Pound = @Col_with_Pound + CASE @Col_with_Pound WHEN '' THEN '#' ELSE '#,#' END + COLUMN_NAME
....

Obviously, # is causing the error whereas I need to # sign there.

======= Error Message ======

The CFML compiler was processing:

An expression that began on line 27, column 99.
Your expression might be missing an ending "#" (it might look like #expr ).
The body of a cfquery tag beginning on line 20, column 2.

27 : @Col_with_Pound = @Col_with_Pound + CASE @Col_with_Pound WHEN '' THEN '#' ELSE '#,#' END + COLUMN_NAME


How do you get around this error? I need to use a single # in the query for the output.

thx much

 

[gatetec]

I tried:

SELECT @Col_with_No_Pound = @Col_with_No_Pound + CASE @Col_with_No_Pound WHEN '' THEN '' ELSE ',' END + COLUMN_NAME,
@Col_with_Pound = @Col_with_Pound + CASE @Col_with_Pound WHEN '' THEN '%' ELSE '%,%' END + COLUMN_NAME

<cfset demo_Col_with_Pound = replace(#DemoCol.Col_with_Pound#, "%", "#", "ALL")>

But, I get another error.

======= Error Message ======

The CFML compiler was processing:

an expression beginning with "replace", on line 37, column 30.This message is usually caused by a problem in the expressions structure.
a cfset tag beginning on line 37, column 2.


36 : <cfset demo_Col_with_No_Pound = DemoCol.Col_with_No_Pound>
37 : <cfset demo_Col_with_Pound = replace(#DemoCol.Col_with_Pound#, "%", "#", "ALL")>


thx much

 

[r937]

i saw your thread in the sql server forum

what you are doing here is even more unnecessary

could you take a few moments and explain what you are actually trying to accomplish?

have you heard of coldfusion's query_name.ColumnList query variable?

r937.com | rudy.ca

 

[gatetec]

Wat I am trying to is to create tables into Excel files under D:\Inetpub\

http://WWWROOT\Gkeeper\

But, I had no success.

I am able to create CSV files from the tables, but it requires these scripts:

<cfset expandpath = "D:\Inetpub\

http://WWWROOT\Gkeeper\">

<cfset HEADER= "ClientNo,ClientFName,ClientLName,ClientMI,SSN,AssessmentDate,....">
<cffile action="Write" file="#expandpath("Demo.csv")#" output="#HEADER#" addnewline="Yes">
<cfoutput query="Demo">
<cfset CONTENT="#ClientNo#,#ClientFName#,#ClientLName#,#ClientMI#,#SSN#,#dateformat("#AssessmentDate#", "mm/dd/yyyy")#,#dateformat("#DOB#", "mm/dd/yyyy")#,....">
<cffile action="APPEND" file="#expandpath("Demo.csv")#" addnewline="Yes" output="#CONTENT#">
</cfoutput>

There are so many column names, so I am trying to eliminate the manual typing for all colnumns.

For this portion (CONTENT="#ClientNo#,#ClientFName#,#ClientLName#..), I need to # sign for CF.

That is where I am getting the error.

Assuming alll these are working fine, I still need to modify the date columns into dateformat i.e. #dateformat("#DOB#", "mm/dd/yyyy")#

Ideally, if I can download each table into Excel with the right date formats under the server directory, it will be perfect.

If Excel solution is not available, I think I need to use CSV solution. As long as I have one table download with Excel working, I can duplicate it for other tables.

Any ideas?

thx so much

 

[r937]

change this --

#dateformat("#DOB#", "mm/dd/yyyy")#

to this --

#dateformat(DOB, "mm/dd/yyyy")#


r937.com | rudy.ca

 

[gatetec]

I tried one more thing here.

Is there a way to write the below output to Excel file with cffile? If it works, that will be greeat..

<cfset expandpath = "D:\Inetpub\

http://WWWROOT\Gkeeper\">

<cfcontent type="application/vnd.ms-excel">

<table><tr><th>ClientNo</th><th>ClientFName</th> <th>ClientLName</th> <th>ClientMI</th> <th>SSN</th> <th>AssessmentDate</th> <th>DOB</th> <th>Sex</th> <th>Address</th> <th>ClientCommunity</th> <th>State</th> <th>Zip</th> <th>MaritalStatus</th> <th>Race</th> <th>SpeeksEnglish</th> <th>ReferralDate</th> <th>LivingSituation</th> <th>LivingArrangements</th> <th>InfoObtained1</th> <th>InfoObtained2</th> <th>SWGHospitalClient</th> <th>ClientPhone</th> <th>ClientOpen</th> <th>Disposition</th> <th>ConnectedAfter</th> <th>DateConnected</th> <th>DateDischarged</th> <th>ClientCount</th> <th>AssessmentComplete</th> <th>IncomePerMonth</th> <th>AssetLevel</th> <th>LiquidAsset1</th> <th>LiquidAsset2</th> <th>LiquidAsset3</th> <th>Religion</th> <th>MilitaryService</th> <th>EmpHistory</th> <th>EmpComment</th> <th>VADependent</th> <th>ReOpen</th>
</tr>
<cfoutput query="Demo">
<tr>
<td>#ClientNo#<td><td>#ClientFName#<td><td>#ClientLName#<td><td>#ClientMI#<td><td>#SSN#<td><td>#AssessmentDate#<td><td>#DOB#<td><td>#Sex#<td><td>#Address#<td><td>#ClientCommunity#<td><td>#State#<td><td>#Zip#<td><td>#MaritalStatus#<td><td>#Race#<td><td>#SpeeksEnglish#<td><td>#ReferralDate#<td><td>#LivingSituation#<td><td>#LivingArrangements#<td><td>#InfoObtained1#<td><td>#InfoObtained2#<td><td>#SWGHospitalClient#<td><td>#ClientPhone#<td><td>#ClientOpen#<td><td>#Disposition#<td><td>#ConnectedAfter#<td><td>#DateConnected#<td><td>#DateDischarged#<td><td>#ClientCount#<td><td>#AssessmentComplete#<td><td>#IncomePerMonth#<td><td>#AssetLevel#<td><td>#LiquidAsset1#<td><td>#LiquidAsset2#<td>
<td>#LiquidAsset3#<td>
<td>#Religion#<td>
<td>#MilitaryService#<td>
<td>#EmpHistory#<td>
<td>#EmpComment#<td>
<td>#VADependent#<td>
<td>#ReOpen#</td>
</tr>
</cfoutput>
</table>

thx much

 

[TruthInSatire]

I'm not sure why you're using cfcontent here. If you're only saving a file to the server you don't need to change the mime type of the current html page that will display a "done" message.

to use cffile use cfsavecontent to place the contents of your loop results to a variable and use something like

<cffile output = "#mySavecontentVariable#" file = "D:\Inetpub\[URL unfurl="true"]WWWROOT\Gkeeper\yourfile.xls"[/URL] >

cfsavecontent:

http://livedocs.adobe.com/coldfusion/8/htmldocs/Tags_r-s_10.html#1844410