Advertisement

05.23.2008 at 07:47AM PDT, ID: 23427772
[x]
Attachment Details

Problem with empty strings and null values

Asked by kenjpete in Cold Fusion Markup Language, ColdFusion Application Server

Tags:

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.Gender#">
                  </cfif>
                  <!--- Year Session Variable --->
                  <cfif isDefined("form.YearGrad")>
                    <cfset session.YearGrad="#form.YearGrad#">
                  </cfif>
                  <!--- Program Session Variable --->
                  <cfif isDefined("form.Program")>
                    <cfset session.Program="#form.Program#">
                  </cfif>
                  <!--- Teacher Session Variable --->
                  <cfif isDefined("form.Teacher")>
                    <cfset session.Teacher="#form.Teacher#">
                  </cfif>
                  <!--- School Session Variable --->
                  <cfif isDefined("form.School")>
                    <cfset session.School="#form.School#">
                  </cfif>
                  <!--- SpecServ Session Variable --->
                  <cfif isDefined("form.Spec_Services")>
                    <cfset session.Spec_Services='#form.Spec_Services#'>
                  </cfif>
                  <!--- Survey1 Session Variable --->
                  <cfif isDefined("form.Survey1")>
                    <cfset session.Survey1='#form.Survey1#'>
                  </cfif>
                  <!--- Survey2 Session Variable --->
                  <cfif isDefined("form.Survey2")>
                    <cfset session.Survey2='#form.Survey2#'>
                  </cfif>
                  <!--- Survey3 Session Variable --->
                  <cfif isDefined("form.Survey3")>
                    <cfset session.Survey3='#form.Survey3#'>
                  </cfif>
                  <!--- Survey4 Session Variable --->
                  <cfif isDefined("form.Survey4")>
                    <cfset session.Survey4='#form.Survey4#'>
                  </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.YearGrad")>
                          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_Services)>
                          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?

KenStart Free Trial
 
Loading Advertisement...
 
[+][-]05.24.2008 at 07:16PM PDT, ID: 21640973

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Cold Fusion Markup Language, ColdFusion Application Server
Tags: Coldfusion
Sign Up Now!
Solution Provided By: gdemaria
Participating Experts: 1
Solution Grade: A
 
 
[+][-]05.30.2008 at 12:24PM PDT, ID: 21680690

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628