CFIF: skipping over a null parameter value 1

RikForgo · Feb 14, 2003

I've got a CF page where the form page and the action page are the same page. I'm using a dynamic form list that allows the user to pick from a list of systems. When the user chooses a system from the list, the form displays the system and sends the system ID in a Form variable.

Basically, it's a page where the user can choose a system and see related systems from other tables all on the same page.

My problem is that the system ID (kept in the "Form.SearchCriteria" variable) is needed to run the query further down the page, which searches for related systems in another table. The first problem I ran into was that if the Form.SearchCriteria variable was null, there was nothing for the query to search against and it failed.

I tried to write a <CFIF #FORM.SearchCriteria# NEQ ""> statement to allow it to skip over that query if no value was present, but I keep getting a syntax error. I'm not sure what I'm doing wrong.

My code and the error message follows. Any help is appreciated.

-------------------------------------------------
in_crossref_wsh.cfm

<cfparam name="wsh_year" default="WSH_YEAR">
<cfparam name="id" default="id">
<cfparam name="FORM.SearchCriteria" default="">

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="../externals/w3csheet.css" rel="stylesheet" type="text/css">

<CFQUERY NAME="get_ws" DATASOURCE="attro"
CACHEDWITHIN="#CreateTimeSpan(0,0,15,0)#">
SELECT system, wsh_year, id
FROM wsh
WHERE wsh_year = '2002'
ORDER BY system, wsh_year
</CFQUERY>

<CFIF #FORM.SearchCriteria# NEQ "">

<cfelse>
<CFQUERY DATASOURCE="attro" name="q_tid_search">
select w.system, w.acronym, t.project_name, t.tid_year, t.tids_id
from wsh w, ie_wsh_tid i, tids t
where w.id = #FORM.SearchCriteria#
and w.id = i.wsh_id
and i.tids_id = t.tids_id;
</cfquery>
</cfif>

<CFQUERY NAME="GetWS" DATASOURCE="attro">
SELECT id
FROM wsh
</CFQUERY>


<CFIF IsDefined("Form.SearchCriteria&quot

>
<CFSET CLIENT.LastSearch = Form.SearchCriteria>

<CFELSEIF IsDefined("CLIENT.LastSearch&quot

>
<CFSET SearchCriteria = CLIENT.LastSearch>

<CFELSE>
<CFSET SearchCriteria = "">
</CFIF>

<CFSET PageStatus = "Search">

<script language="JavaScript" type="text/JavaScript">
<!--
function MM_jumpMenu(targ,selObj,restore){ //v3.0
eval(targ+".location='"+selObj.options[selObj.selectedIndex].value+"'&quot

;
if (restore) selObj.selectedIndex=0;
}
//-->
</script>
<CFFORM ACTION="#CGI.SCRIPT_NAME#" METHOD="Post" class="maintext">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="2" valign="bottom" class="maintext">The core capability of
ATTRO is the connections its facilitates between Army Transformation documents.
To view systems, projects or objectives that specifically relate to one
another, choose an ATTRO Cross Reference entry point, then select a title
from the drop-list. A corresponding list of related topics will be displayed.
</td>
</tr>
<tr>
<td valign="bottom"> </td>
<td align="right" valign="bottom"> </td>
</tr>
<tr>
<td valign="bottom" class="maintext2"><form name="form1" class="button2">
<p>
<select name="cr_entry" class="button2" onChange="MM_jumpMenu('parent',this,1)">
<option>- Select a Cross Reference Entry Point - </option>
<option value="crossref_astmp_2001.cfm">Army S&T Master Plan,
2001</option>
<option value="crossref_wsh_2002.cfm">Weapons Systems Handbook,
2002</option>
<option value="crossref_wsh_2001.cfm">Weapons Systems Handbook,
2001</option>
<option value="crossref_tid_2001.cfm">Test Investment Database,
2001</option>
<option value="crossref_tid_2000.cfm">Test Investment Database,
2000</option>
<option value="crossref_tid_1999.cfm">Test Investment Database,
1999</option>
<option value="crossref_tid_1998.cfm">Test Investment Database,
1998</option>
<option value="crossref_tid_1997.cfm">Test Investment Database,
1997</option>
<option value="crossref_tid_1996.cfm">Test Investment Database,
1996</option>
</select>
</p>
</form></td>
<td align="right" valign="bottom"> </td>
</tr>
<tr>
<td valign="bottom" class="maintext2"> </td>
<td align="right" valign="bottom"> </td>
</tr>
<tr>
<td width="28%" valign="middle">

<span class="maintext2">Select a Weapons System to cross reference:</span><span class="maintext2bold"><br>
</span> <cfselect name="SearchCriteria" message="Your query is blank - please enter a word or phrase." query="get_ws" value="id" display="system" selected="#FORM.SearchCriteria#" class="button2">
<option value="">Select a system</option>
</cfselect>
</p> </td>
<td width="34%" align="right" valign="middle">

<input name="Submit" type="Submit" class="button" value="Search"></td>
</tr>
<tr>
<td colspan="4"><hr noshade></td>
</tr>
</table>
</CFFORM>



<CFIF SearchCriteria NEQ "">


<CFQUERY NAME="GetWSHMatches" DATASOURCE="attro">
SELECT id, system
FROM wsh
WHERE system LIKE '%#SearchCriteria#%'
ORDER BY system DESC
</CFQUERY>

<cfif GetWSHMatches.RecordCount IS 0>


<span class="maintext2">No records match this request. Please enter another term or phrase.</span><br><br>

<cfelse>

<CFSET RowsPerPage = 15>


<CFSET RowsPerPage = 15>

<CFPARAM NAME="URL.StartRow" DEFAULT="1" TYPE="numeric">

<CFPARAM NAME="URL.ShowAll" TYPE="boolean" DEFAULT="No">




<CFSET TotalRows = q_tid_search.RecordCount>

<CFIF URL.ShowAll>
<CFSET RowsPerPage = TotalRows>
</CFIF>


<CFSET EndRow = Min(URL.StartRow + RowsPerPage - 1, TotalRows)>

<CFSET StartRowNext = EndRow + 1>

<CFSET StartRowBack = URL.StartRow - RowsPerPage>

<head>
<title>Cross Referencer</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="../externals/w3csheet.css" rel="stylesheet" type="text/css">
</head>

<body>



<STYLE>
TH {font-family:sans-serif;font-size:smaller;
background:navy;color:white}
TD {font-family:sans-serif;font-size:smaller}
TD.DataA {background:silver;color:black}
TD.DataB {background:lightgrey;color:black}
</STYLE>

<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="1">

<TR>
<TD COLSPAN="2">


<br><br>
<span class="maintextbold16">Related Investments</span><br>
<CFOUTPUT><span class="maintext2">
Displaying <B>#URL.StartRow#</B> to <B>#EndRow#</B> of <B>#TotalRows#</B> TID Solution(s).<BR>
</CFOUTPUT> </span></TD>
<TD WIDTH="140" ALIGN="right"> <CFIF NOT URL.ShowAll>

<CFINCLUDE TEMPLATE="../tid/in_find_NextNIncludeBackNext.cfm">
</CFIF> </TD>
</TR>

<TR>
<TH colspan="3" > <div align="left"> Project Name</div></TH>
</TR>

<td><CFLOOP QUERY="q_tid_search" StartRow="#URL.StartRow#" ENDROW="#EndRow#">

<CFSET Class = IIF(q_tid_search.CurrentRow MOD 2 EQ 0, "'DataA'", "'DataB'&quot

>
<CFOUTPUT>

<TR VALIGN="baseline">
<TD colspan="3" CLASS="#Class#" > <span class="maintext2"><a href="../wsh/s_wsh_detail_01.cfm?id=#id#&wsh_year=#wsh_year#"><font color="##000099">#system# (#wsh_year#)</font></a></span> <div align="left"></div>
<div align="left"></div></TD>
</TR></CFOUTPUT>

<CFIF URL.ShowAll><CFIF q_tid_search.CurrentRow MOD 5 EQ 0>

</TABLE>

<CFFLUSH>

<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="1">

<CFSET InitialTime = Now()>
<CFLOOP CONDITION="DateDiff('s', InitialTime, Now()) LT 1"></CFLOOP>
</CFIF>
</CFIF>

</CFLOOP>


<TR>
<TD WIDTH="500" COLSPAN="2">
<CFIF NOT URL.ShowAll>

<span class="maintext2">Page</span>
<CFINCLUDE TEMPLATE="../tid/in_find_NextNIncludePageLinks.cfm">

<CFOUTPUT> <span class="maintext2"><A HREF="#CGI.SCRIPT_NAME#?ShowAll=Yes"><font color="##000099">Show
All</font></A> </span></CFOUTPUT>
</CFIF>
</TD>
<TD WIDTH="140" ALIGN="right">
<CFIF NOT URL.ShowAll>

<CFINCLUDE TEMPLATE="../tid/in_find_NextNIncludeBackNext.cfm">
</CFIF>

</TD>
</TR>
</TABLE></cfif>
<br><br>



<CFSET RowsPerPage = 15>


<CFSET RowsPerPage = 15>

<CFPARAM NAME="URL.StartRow" DEFAULT="1" TYPE="numeric">

<CFPARAM NAME="URL.ShowAll" TYPE="boolean" DEFAULT="No">




<CFSET TotalRows = GetWSHMatches.RecordCount>

<CFIF URL.ShowAll>
<CFSET RowsPerPage = TotalRows>
</CFIF>


<CFSET EndRow = Min(URL.StartRow + RowsPerPage - 1, TotalRows)>

<CFSET StartRowNext = EndRow + 1>

<CFSET StartRowBack = URL.StartRow - RowsPerPage>





<STYLE>
TH {font-family:sans-serif;font-size:smaller;
background:navy;color:white}
TD {font-family:sans-serif;font-size:smaller}
TD.DataA {background:silver;color:black}
TD.DataB {background:lightgrey;color:black}
</STYLE>

<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="1">

<TR>
<TD COLSPAN="2">

<span class="maintextbold16">Related STOs</span><br>
<span class="maintext2"><CFOUTPUT>
Displaying <B>#URL.StartRow#</B> to <B>#EndRow#</B> of <B>#TotalRows#</B> S&T Objective(s).<BR>
</CFOUTPUT> </span></TD>
<TD WIDTH="140" ALIGN="right"> <CFIF NOT URL.ShowAll>

<CFINCLUDE TEMPLATE="../tid/in_find_NextNIncludeBackNext.cfm">
</CFIF> </TD>
</TR>

<TR>
<TH colspan="3" > <div align="left"> S&T Objective</div></TH>
</TR>

<td><CFLOOP QUERY="GetWSHMatches" StartRow="#URL.StartRow#" ENDROW="#EndRow#">

<CFSET Class = IIF(GetWSHMatches.CurrentRow MOD 2 EQ 0, "'DataA'", "'DataB'&quot

>
<CFOUTPUT>

<TR VALIGN="baseline">
<TD colspan="3" CLASS="#Class#" > <span class="maintext2"><a href="../wsh/s_wsh_detail_01.cfm?id=#id#&wsh_year=#wsh_year#"><font color="##000099">#system# (#wsh_year#)</font></a></span> <div align="left"></div>
<div align="left"></div></TD>
</TR></CFOUTPUT>

<CFIF URL.ShowAll><CFIF GetWSHMatches.CurrentRow MOD 5 EQ 0>

</TABLE>

<CFFLUSH>

<TABLE WIDTH="100%" BORDER="0" CELLSPACING="0" CELLPADDING="1">

<CFSET InitialTime = Now()>
<CFLOOP CONDITION="DateDiff('s', InitialTime, Now()) LT 1"></CFLOOP>
</CFIF>
</CFIF>

</CFLOOP>


<TR>
<TD WIDTH="500" COLSPAN="2">
<CFIF NOT URL.ShowAll>

<span class="maintext2">Page</span>
<CFINCLUDE TEMPLATE="../tid/in_find_NextNIncludePageLinks.cfm">

<CFOUTPUT> <span class="maintext2"><A HREF="#CGI.SCRIPT_NAME#?ShowAll=Yes"><font color="##000099">Show
All</font></A> </span></CFOUTPUT>
</CFIF>
</TD>
<TD WIDTH="140" ALIGN="right">
<CFIF NOT URL.ShowAll>

<CFINCLUDE TEMPLATE="../tid/in_find_NextNIncludeBackNext.cfm">
</CFIF>

</TD>
</TR>
</TABLE></cfif>
<br><br>
</body></html>

-------------------------------------------------
ERROR I RECEIVE:

Error Diagnostic Information
ODBC Error Code = 42000 (Syntax error or access violation)

[MySQL][ODBC 3.51 Driver][mysqld-3.23.47-max-nt]You have an error in your SQL syntax near 'and w.id = i.wsh_id and i.tids_id = t.tids_id' at line 4

SQL = "select w.system, w.acronym, t.project_name, t.tid_year, t.tids_id from wsh w, ie_wsh_tid i, tids t where w.id = and w.id = i.wsh_id and i.tids_id = t.tids_id;"

Data Source = "ATTRO"

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (19:6) to (19:53) in the template file c:\inetpub\

http://wwwroot\attro-mysql\cross_ref\in_crossref_wsh_action.cfm.

webmigit · Feb 20, 2003

Code:

<CFIF #FORM.SearchCriteria# NEQ &quot;&quot;> 

<cfelse>

should be

Code:

<CFIF #FORM.SearchCriteria# NEQ &quot;&quot;>

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

csteinhilber · Feb 24, 2003

Took me a minute to figure out what the difference was, there, Webmigit... til I looked in detail at RikForgo's code ;-)

Yep... get rid of that CFELSE.

Also, Macromedia recommends that your CFIF be one of the following:

Code:

<CFIF Compare(FORM.SearchCriteria,&quot;&quot;) NEQ 0>

or

Code:

<CFIF Len(Trim(FORM.SearchCriteria)) GT 0>

Both would apparently perform better than a straight string compare.

-Carl

RikForgo · Feb 25, 2003

Thanks Webmigit, that did the trick. And thanks to you also Carl. I went back and added the LenTrim method you suggested.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

CFIF: skipping over a null parameter value 1

RikForgo

Programmer

webmigit

Programmer

csteinhilber

Programmer

RikForgo

Programmer

Similar threads

Part and Inventory Search

Sponsor