lunatic
June 23rd, 2004, 02:24 PM
Howdy all,
I am stumbling my way through learning coldfusion by building a project log for our group at work. I am stuck on building a search page. My search page seems really simple. I have one textbox that allows users to enter a job number, and then two text boxes that allow users to enter a word to search ALL the fields in the big table by. Between the two search fields are two radio buttons (and/or) so the user can specify keyword 1 AND/OR keyword 2.
I should mention now that the query and queryResults pages I am building are adopted from a set given me by an acquaintance (who actually got his to work but is unavailable to me at the moment).
The query page has the following code:
<html>
<head>
<title>Query the Project Log</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<div align="center">
<p><font size="4"><strong>Query the Project Log</strong></font><br>
(all fields optional)
</p>
</div>
<table align="center" cellpadding=10>
<form action="queryResults.cfm" method="post">
<tr><td width="79">Job Number:</td>
<td width="350" colspan="2"><input name="Job_Number" type="text" maxlength="10"></td></tr>
<tr><td>Query string:</td>
<td colspan="2"><input name="querystring1" type="text" size="50" maxlength="50"></td></tr>
<tr><td colspan="3" align="center">
<INPUT TYPE="radio" NAME="querytype" VALUE="and" checked>AND
<input type="radio" name="querytype" value="or">OR
</td></tr>
<tr><td>Query string:</td>
<td colspan="2"><input name="querystring2" type="text" size="50" maxlength="50"></td></tr>
<tr><td div align="center" colspan="2"><input type="submit" value="Search"></td></tr>
</form>
</table>
</body>
</html>
And the queryResults page has the following code:
<cfquery name="q_queryResults" datasource="PLOG">
SELECT *
FROM data_admin.Main_log
WHERE del_job = 0
AND 0=0
<CFIF #FORM.Job_Number# IS NOT "">
AND Job_Number LIKE (#FORM.Job_Number#)
</CFIF>
<CFIF #FORM.querystring1# IS NOT "">
AND ( upper(trim(Refuge_Literal)||' '||trim(Requestor)||' '||trim(Requestor_Office)||' '||trim(Project_Type)||' '||trim(Map_Type)||' '||trim(Rq_Description) ||' '||trim(Assigned_To)) LIKE upper('%#FORM.querystring1#%')
</CFIF>
<CFIF #FORM.querystring2# IS "" AND #FORM.querystring1# IS NOT "">
)
</CFIF>
<CFIF #FORM.querystring2# IS NOT "">
#FORM.querytype# upper(trim(Refuge_Literal)||' '||trim(Requestor)||' '||trim(Requestor_Office)||' '||trim(Project_Type)||' '||trim(Map_Type)||' '||trim(Rq_Description) ||' '||trim(Assigned_To)) LIKE upper('%#FORM.querystring2#%') )
</CFIF>
ORDER BY Date_Assigned DESC, Job_Number DESC
</cfquery>
and then a table to display the data.
But it's not working. If I put in a job number, or even a partial job number then I get the table to display but no data is returned (only the column headings show). If I put something into the text fields then I get an error message that says
[Macromedia][SQLServer JDBC Driver][SQLServer]'trim' is not a recognized function name.
Sorry to go on and on but I really need this working. Anyone have a clue what is wrong or maybe a better way to search all fields using more than one keyword?
any help GREATLY appreciated!
:hr:
I am stumbling my way through learning coldfusion by building a project log for our group at work. I am stuck on building a search page. My search page seems really simple. I have one textbox that allows users to enter a job number, and then two text boxes that allow users to enter a word to search ALL the fields in the big table by. Between the two search fields are two radio buttons (and/or) so the user can specify keyword 1 AND/OR keyword 2.
I should mention now that the query and queryResults pages I am building are adopted from a set given me by an acquaintance (who actually got his to work but is unavailable to me at the moment).
The query page has the following code:
<html>
<head>
<title>Query the Project Log</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<div align="center">
<p><font size="4"><strong>Query the Project Log</strong></font><br>
(all fields optional)
</p>
</div>
<table align="center" cellpadding=10>
<form action="queryResults.cfm" method="post">
<tr><td width="79">Job Number:</td>
<td width="350" colspan="2"><input name="Job_Number" type="text" maxlength="10"></td></tr>
<tr><td>Query string:</td>
<td colspan="2"><input name="querystring1" type="text" size="50" maxlength="50"></td></tr>
<tr><td colspan="3" align="center">
<INPUT TYPE="radio" NAME="querytype" VALUE="and" checked>AND
<input type="radio" name="querytype" value="or">OR
</td></tr>
<tr><td>Query string:</td>
<td colspan="2"><input name="querystring2" type="text" size="50" maxlength="50"></td></tr>
<tr><td div align="center" colspan="2"><input type="submit" value="Search"></td></tr>
</form>
</table>
</body>
</html>
And the queryResults page has the following code:
<cfquery name="q_queryResults" datasource="PLOG">
SELECT *
FROM data_admin.Main_log
WHERE del_job = 0
AND 0=0
<CFIF #FORM.Job_Number# IS NOT "">
AND Job_Number LIKE (#FORM.Job_Number#)
</CFIF>
<CFIF #FORM.querystring1# IS NOT "">
AND ( upper(trim(Refuge_Literal)||' '||trim(Requestor)||' '||trim(Requestor_Office)||' '||trim(Project_Type)||' '||trim(Map_Type)||' '||trim(Rq_Description) ||' '||trim(Assigned_To)) LIKE upper('%#FORM.querystring1#%')
</CFIF>
<CFIF #FORM.querystring2# IS "" AND #FORM.querystring1# IS NOT "">
)
</CFIF>
<CFIF #FORM.querystring2# IS NOT "">
#FORM.querytype# upper(trim(Refuge_Literal)||' '||trim(Requestor)||' '||trim(Requestor_Office)||' '||trim(Project_Type)||' '||trim(Map_Type)||' '||trim(Rq_Description) ||' '||trim(Assigned_To)) LIKE upper('%#FORM.querystring2#%') )
</CFIF>
ORDER BY Date_Assigned DESC, Job_Number DESC
</cfquery>
and then a table to display the data.
But it's not working. If I put in a job number, or even a partial job number then I get the table to display but no data is returned (only the column headings show). If I put something into the text fields then I get an error message that says
[Macromedia][SQLServer JDBC Driver][SQLServer]'trim' is not a recognized function name.
Sorry to go on and on but I really need this working. Anyone have a clue what is wrong or maybe a better way to search all fields using more than one keyword?
any help GREATLY appreciated!
:hr: