Solved

Passing a form value (text) from a coldfusion web form into an Coldfusion export to excel.

Posted on 2008-06-26
12
239 Views
Last Modified: 2013-12-24
I can successfully do an export to excel in coldfusion with a straight forward sql query from the database. I run into errors when I try to run the export to excel with a query that reads the form value that was inputted on the coldfusion web form by a user. The error I normally get is "Form value is not defined".
0
Comment
Question by:jslaught
  • 6
  • 5
12 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 21876397
When it says form value is not defined, look at the name of the variable it's referring to.
You either have a typ-o or something in the <INPUT tag so the form variable you're using doesn't match the INPUT tag's NAME field... or it's a checkbox.  Form variables are not passed on an unchecked checkbox.   In this case you need to do a cfparam to default it

Assuming the undefined form variable is AddExtraKetchup and the value could be Yes (if checked) or No (if not checked) ... put this at the top of your page so it has a no value if not checked.
<cfparam name="form.AddExtraKetchup" default="no">
0
 

Author Comment

by:jslaught
ID: 21878390
Hello, Thanks for the reply but it's not quite doing what I need it to do. Let me try and explain it like this: (1) I have a coldfusion form with a list box. When a user selects a value from the list box and clicks a search button, a coldfusion result screen is then displayed with records associated with the value that was selected in the list box. (2) On that coldfusion result screen with the records displayed I would like to have a button(link) that then exports those results to excel. When I try to do this now I am getting the "element is undefined in form" message. I've been successful at export results to excel with a regular sql query but not when I try to export results to excel with a query based off of the user's value selection from a list box.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 21878620
> On that coldfusion result screen with the records displayed I would like to have a button(link) that then exports those results to excel

The key is what is on the other side of that link and what does it expect to see?
Let's say you're calling a file called "exportData.cfm"  

If you look into this file it is expecting certain values, perhaps just an ID for a database query or perhaps a set of form variables (sounds like at least one form variable is needed).

If it's expecting FORM (scope) variables then you can't link to it via a URL, you need to do a form submit to it.    The form must have all the variables needed by "exportdata" - even if they are hidden INPUT tags.

So, if that's the case, you have to put a form on your second page and populate it with the variables needed by export

0
 
LVL 4

Expert Comment

by:msfletch
ID: 21878632
Posting code samples would probably help. A page of code is worth a thousand explanations. =)
0
 

Author Comment

by:jslaught
ID: 21884972
Here is a code snippet (example) of what i currently have and what i'm trying to do.
-- 1st coldfusion form -- 

I have a initial coldfusion form with a list box of values where the user selects a value:

--example--

<form action="listselectvalue.cfm" method="post">

<select name="listselect" multiple>

<Option value ="Value 1">Value 1</option>

<Option value ="Value 2">Value 2</option>

<Option value ="Value 3">Value 3</option>

</select>
 

-- User selects a value from the list box on 1st form and then clicks a Submit button (example:<input name="submit" type="submit" class="heading" style="height:2em; width:8em" value="SEARCH" />) that takes that value selected and places it in the query that runs the 2nd form (example: <form action="listselectvalue.cfm" method="post">) 
 
 

--- 2nd form---

(The 2nd form is "listselectvalue.cfm")---

The 2nd form displays results in a table based off the query that runs with the value that was selected from the 1st form.  

example query in 2nd form:

<CFIF '#form.listselect#' is "Value 1">

<CFQUERY NAME="test" DATASOURCE="datasource">

select * from table where lower(Field) like lower('Value 1')

</CFQUERY>
 

---EVERYTHING ABOVE I AM GOOD WITH AND HAVE NO PROBLEMS WITH IT--
 

When 2nd form displays the results there also is a link at the top of that page to allows the user to click on to export the results into excel. Below is the "export to excel" code that i'm using:
 

<!--- use cfsetting to block output to HTML outside of cfoutput tags --->

<cfsetting enablecfoutputonly="Yes">
 

!!!NOTE TO EXPERT: BELOW IS WHAT I NEED HELP WITH!!!

<!-- get info --->

<CFQUERY NAME="test" DATASOURCE="datasource">

select * from table where lower(field) like lower('#form.Value 1#')

</CFQUERY>
 

!!!NOTE TO EXPERT: ABOVE IS WHAT I NEED HELP WITH!!!
 

<!--- set vars for special chars --->

<cfset TabChar = Chr(9)>

<cfset NewLine = Chr(13) & Chr(10)>

<!--- set content type to invoke Excel --->

<cfcontent type="application/x-msexcel">
 

<!--- suggest default name for XLS file --->

<!--- use "Content-Disposition" in cfheader for Internet Explorer --->

<cfheader name="Content-Disposition" value="exporttest.xls">
 

<!--- output data using cfloop & cfoutput --->

<cfloop query="test">

<cfoutput>#field1##TabChar##Field2#</cfoutput>

</cfloop>
 

I get the following error message on the CFQUERY above: "ELEMENT Value 1 is not defined"

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 21885038
In this line, you're mixing up the variable with the variable's value...

select * from table where lower(field) like lower('#form.Value 1#')


It should be either this...

select * from table where lower(field) like lower('Value 1')  --- the value

or this... (using the variable)

select * from table where lower(field) like lower('#form.listselect#')

I suspect it should be this last version..

By the way, what database are you using that is case sensitive?  Oracle?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 39

Expert Comment

by:gdemaria
ID: 21885069
By the way, did you mean to allow the user to select more than one item?

<select name="listselect" multiple>  <=== multiple?

If so, you will get a comma delimited list for a value in form.listSelect  and this CFIF is no longer work.

<CFIF '#form.listselect#' is "Value 1">

as form.listSelect could contain "Value 1,Value2"

0
 

Author Comment

by:jslaught
ID: 21886329
I have tried the select statement in the "export to excel" cfquery with the "value" and then with the "variable" in the past and again when you suggested it before posting this question to you and I still get the coldfusion error message "Element listselect is undefined in form."

Any other suggestions I can try?

FYI... The database I am using is Oracle.

FYI.. The list box should be single select.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 21886416
> When 2nd form displays the results there also is a link at the top of that page to allows the user to click on to export the results into excel. Below is the "export to excel" code that i'm using:

Is the export to excel code in a third file or is it in the same file (listselectvalue.cfm) ?

If it's in a third file, how are you passing the value of form.listselect  to this file?

In your file listselectvalue.cfm, you clearly have the value of form.listselect because you use it  here...

<CFIF '#form.listselect#' is "Value 1">

In your export area, you are using the value here...

<CFQUERY NAME="test" DATASOURCE="datasource">
select * from table where lower(field) like lower('#form.listselect#')
</CFQUERY>

But you're not showing how you get from listselectvalue.cfm to the export routine.
However you are doing that, you must pass the value of form.listselect in order to use it in that select statement.

Can you show the code to go from listselectvalue.cfm to your export routine?

0
 

Author Comment

by:jslaught
ID: 21887081
Apologize for not explaining this before but the link to the "export to excel" on the listselectvalue.cfm form does go to a third file called exptoexcel.cfm. In the extoexcel.cfm file the following is the code that i've been having trouble with getting to run properly (same code as mentioned earlier):

<!--- use cfsetting to block output to HTML outside of cfoutput tags --->
<cfsetting enablecfoutputonly="Yes">
 
!!!NOTE TO EXPERT: BELOW IS WHAT I NEED HELP WITH!!!
<!-- get info --->
<CFQUERY NAME="test" DATASOURCE="datasource">
select * from table where lower(field) like lower('#form.listselect#')
</CFQUERY>
 
!!!NOTE TO EXPERT: ABOVE IS WHAT I NEED HELP WITH!!!
 
<!--- set vars for special chars --->
<cfset TabChar = Chr(9)>
<cfset NewLine = Chr(13) & Chr(10)>
<!--- set content type to invoke Excel --->
<cfcontent type="application/x-msexcel">
 
<!--- suggest default name for XLS file --->
<!--- use "Content-Disposition" in cfheader for Internet Explorer --->
<cfheader name="Content-Disposition" value="exporttest.xls">
 
<!--- output data using cfloop & cfoutput --->
<cfloop query="test">
<cfoutput>#field1##TabChar##Field2#</cfoutput>
</cfloop>
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 21887318
Ok, so you need to pass the variable "listselect" to extoexcel.cfm

Are you???

If you are using a link ( <a href="xxx"> )  then you need to include the listSelect on the URL   extoexcel.cfm?listSelect=xxx  

in extoexcel.cfm  you will receive this variable in URL.listSelect

Otherwise, you need to create a form on listselectvalue.cfm
and so a SUBMIT to extoexcel.cfm with listSelect as an <INPUT>  tag (either text or hidden)

One way or another you need to pass the variable from  listselectvalue.cfm  to extoexcel.cfm.   You're still not showing how you pass the variable, so I suspect you're not doing it.   But it has to get there somehow, right?  It's not magic.. :)

0
 

Author Comment

by:jslaught
ID: 21903218
Passing a variable via a form and submit button worked as you suggested. Thanks for your patience in helping me. It's appreciated! I guess I should've known to pass the variable but now I know.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now