I am using CF8 and a MS Access database to do a routine query. On my form page I have a several form fields that allow the user to query the database based on certain parameters (last name, email, city, state, etc.).
After I do my query I need to put whatever search parameters are entered into the form in a session object for use later in the application. The problem that comes up is, if the user leaves fields blank, when I put them into a session object they are being seen as empty strings and I need them to be seen as null. Here is the relevant code on the results page:
<!--- Create session objects --->
<!--- Last Name Session Variable --->
<cfif isDefined("form.Last")>
<cfset session.Last="#form.Last#"
>
</cfif>
<!--- Email Session Variable --->
<cfif isDefined("form.Email")>
<cfset session.Email="#form.Email
#">
</cfif>
<!--- City Session Variable --->
<cfif isDefined("form.City")>
<cfset session.City="#form.City#"
>
</cfif>
<!--- State Session Variable --->
<cfif isDefined("form.State")>
<cfset session.State="#form.State
#">
</cfif>
<!--- Zip Session Variable --->
<cfif isDefined("form.Zip")>
<cfset session.Zip="#form.Zip#">
</cfif>
<!--- Gender Session Variable --->
<cfif isDefined("form.Gender")>
<cfset session.Gender="#form.Gend
er#">
</cfif>
<!--- Year Session Variable --->
<cfif isDefined("form.YearGrad")
>
<cfset session.YearGrad="#form.Ye
arGrad#">
</cfif>
<!--- Program Session Variable --->
<cfif isDefined("form.Program")>
<cfset session.Program="#form.Pro
gram#">
</cfif>
<!--- Teacher Session Variable --->
<cfif isDefined("form.Teacher")>
<cfset session.Teacher="#form.Tea
cher#">
</cfif>
<!--- School Session Variable --->
<cfif isDefined("form.School")>
<cfset session.School="#form.Scho
ol#">
</cfif>
<!--- SpecServ Session Variable --->
<cfif isDefined("form.Spec_Servi
ces")>
<cfset session.Spec_Services='#fo
rm.Spec_Se
rvices#'>
</cfif>
<!--- Survey1 Session Variable --->
<cfif isDefined("form.Survey1")>
<cfset session.Survey1='#form.Sur
vey1#'>
</cfif>
<!--- Survey2 Session Variable --->
<cfif isDefined("form.Survey2")>
<cfset session.Survey2='#form.Sur
vey2#'>
</cfif>
<!--- Survey3 Session Variable --->
<cfif isDefined("form.Survey3")>
<cfset session.Survey3='#form.Sur
vey3#'>
</cfif>
<!--- Survey4 Session Variable --->
<cfif isDefined("form.Survey4")>
<cfset session.Survey4='#form.Sur
vey4#'>
</cfif>
<!--- Run query --->
<cfquery name="survey" datasource="ctesurvey_dsn"
>
SELECT *
FROM data
WHERE
<cfif isDefined("session.Last")>
Last LIKE '#session.Last#'
AND
</cfif>
<cfif isDefined("session.Email")
>
Email LIKE '#session.Email#'
AND
</cfif>
<cfif isDefined("session.City")>
City LIKE '#session.City#'
AND
</cfif>
<cfif isDefined("session.State")
>
State LIKE '#session.State#'
AND
</cfif>
<cfif isDefined("session.Zip")>
Zip LIKE '#session.Zip#'
AND
</cfif>
<cfif isDefined("session.Gender"
)>
Gender LIKE '#session.Gender#'
AND
</cfif>
<cfif isDefined("session.YearGra
d")>
YearGrad LIKE '#session.YearGrad#'
AND
</cfif>
<cfif isDefined("session.Program
")>
Program LIKE '#session.Program#'
AND
</cfif>
<cfif isDefined("session.Teacher
")>
Teacher LIKE '#session.Teacher#'
AND
</cfif>
<cfif isDefined("session.School"
)>
School LIKE '#session.School#'
AND
</cfif>
<cfif isDefined(session.Spec_Ser
vices)>
Spec_Services = #session.Spec_Services#
AND
</cfif>
<cfif isDefined(session.Survey1)
>
Survey1 = #session.Survey1#
AND
</cfif>
<cfif isDefined(session.Survey2)
>
Survey2 = #session.Survey2#
AND
</cfif>
<cfif isDefined(session.Survey3)
>
Survey3 = #session.Survey3#
AND
</cfif>
<cfif isDefined(session.Survey4)
>
Survey4 = #session.Survey4#
AND
</cfif>
Email IS NOT NULL
ORDER BY Last ASC
</cfquery>
**************************
********
When I do a dump of the session objects, the ones that the user left blank are displayed as empty strings.
This is causing my query to return 0 results. How can I get these session objects to be seen as null values?
Ken
Start Free Trial