Link to home
Start Free TrialLog in
Avatar of Jerome Slaughter
Jerome SlaughterFlag for United States of America

asked on

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

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".
Avatar of gdemaria
gdemaria
Flag of United States of America image

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">
Avatar of Jerome Slaughter

ASKER

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.
> 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

Posting code samples would probably help. A page of code is worth a thousand explanations. =)
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

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?
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"

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.
> 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?

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>
ASKER CERTIFIED SOLUTION
Avatar of gdemaria
gdemaria
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.