search form returns all with no term specified

I have a search form for my table that contains a few text input boxes. If the user does not type in anything but just hits the submit button right away then all records are returned. How can I prevent this? I would like to have something that says "you must specify a search item" or something like that.

thanks for any help!
melissa

<cfif not len( trim( form.searchText ) )>
...show error message or use cflocation to send them to a page explaining that you must enter something...
</cfif>

I'm sorry but I'm not sure I follow. This is my search action page:



so I have fields for job number, start and end dates, and two input boxes for strings. All of them can be empty, but it would be nice to specify that at least one of them contain something?

<cfif len( trim ( form.job_number ) ) or len( trim ( form.startdate ) ) or len( trim ( form.querystring1 ) ) len( trim ( form.querystring2 ) )>

<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 ('%#trim(FORM.Job_Number)#%')
</CFIF>

<CFIF #FORM.startDate# IS NOT "">
AND Date_Assigned BETWEEN #CreateODBCDate(FORM.startDate)# AND #CreateODBCDate(FORM.endDate)#
</CFIF>

<CFIF #FORM.querystring1# IS NOT "">
AND Fac_Name LIKE ('%#trim(FORM.querystring1)#%') OR Requestor LIKE ('%#trim(FORM.querystring1)#%') OR Requestor_Office LIKE ('%#trim(FORM.querystring1)#%') OR Project_Type LIKE ('%#trim(FORM.querystring1)#%') OR Map_Type LIKE ('%#trim(FORM.querystring1)#%') OR Rq_Description LIKE ('%#trim(FORM.querystring1)#%') OR Assigned_To LIKE ('%#trim(FORM.querystring1)#%')
</CFIF>

<CFIF #FORM.querystring2# IS NOT "">
#FORM.querytype# Fac_Name LIKE ('%#trim(FORM.querystring2)#%') OR Requestor LIKE ('%#trim(FORM.querystring2)#%') OR Requestor_Office LIKE ('%#trim(FORM.querystring2)#%') OR Project_Type LIKE ('%#trim(FORM.querystring2)#%') OR Map_Type LIKE ('%#trim(FORM.querystring2)#%') OR Rq_Description LIKE ('%#trim(FORM.querystring2)#%') OR Assigned_To LIKE ('%#trim(FORM.querystring2)#%')
</CFIF>

ORDER BY Date_Assigned DESC, Job_Number DESC

</cfquery>

<cfelse>
you didn't enter anything into any of the form fields.
</cfif>

Hello,

Sorry to bring this thread back to life but believe it or not I am finally getting back to this problem (got sidetracked by LOTS of other projects).

I am having trouble with the "<cfelse>you didn't enter anything into any of the form fields" part of the statement. I have some html stuff beneath the code and when I run everything as is (with kiteless' code above the html stuff) I get the following error message:
-----------------------
Attribute validation error for tag cfoutput.
The value of the attribute query, which is currently "q_queryResults", is invalid.
-----------------------

I'm not sure what that means. Can someone shed some light on that for me?

Thank you!
melissa

Somewhere you have a <cfoutput query="q_queryresults">.

Well yes, because I need to output the results from the query?

If I remove the if statement above the cfquery line then when I hit the submit button (without specifying a search term) it returns all rows. But if I put that line of code in and repeat the process I get the error message.

Isn't there any way of coding this beast so that the user has to submit at least one search term?

I have attached my code for the entire page if it helps.

Thanks again!

Well it's giving you an error because the query only runs if the if statement is true. Otherwise there is no query with that name for CF to output.

You could do something like check the length of the search term form field, and display an error or redirect them back to the form with a message that says "you must enter a search term".

I thought I could do something simple like this:

------------------------------
<cfparam name="sort" default="1">
<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 ('%#trim(FORM.Job_Number)#%')

<CFELSEIF #FORM.startDate# IS NOT "">
AND Date_Assigned BETWEEN #CreateODBCDate(FORM.startDate)# AND #CreateODBCDate(FORM.endDate)#

<CFELSEIF #FORM.querystring1# IS NOT "">
AND Fac_Name LIKE ('%#trim(FORM.querystring1)#%') OR Requestor LIKE ('%#trim(FORM.querystring1)#%') OR Requestor_Office LIKE ('%#trim(FORM.querystring1)#%') OR Project_Type LIKE ('%#trim(FORM.querystring1)#%') OR Map_Type LIKE ('%#trim(FORM.querystring1)#%') OR Rq_Description LIKE ('%#trim(FORM.querystring1)#%') OR Assigned_To LIKE ('%#trim(FORM.querystring1)#%')

<CFELSEIF #FORM.querystring2# IS NOT "">
#FORM.querytype# Fac_Name LIKE ('%#trim(FORM.querystring2)#%') OR Requestor LIKE ('%#trim(FORM.querystring2)#%') OR Requestor_Office LIKE ('%#trim(FORM.querystring2)#%') OR Project_Type LIKE ('%#trim(FORM.querystring2)#%') OR Map_Type LIKE ('%#trim(FORM.querystring2)#%') OR Rq_Description LIKE ('%#trim(FORM.querystring2)#%') OR Assigned_To LIKE ('%#trim(FORM.querystring2)#%')

<CFELSE>
<cfoutput> No search term specified. Please hit the Back button and enter a search term. </cfoutput>
</CFIF>
------------------------------

but it isn't working.

How else can I check to see if at least one field has something in it?

Thanks for any help,
melissa

First, try adding trim() to the conditionals:

<CFIF len( trim( FORM.Job_Number ) )>
...form.job_number has something in it...

But other than that, what isn't working? At least one of the fields would have to have something in it...

You mean add that to what I have listed above? When I try to run what I posted above I get "error reading len" type of message.

len() is a function, so you must be calling it wrong. I mean something like this...

<CFIF len( trim( FORM.Job_Number) )>
AND Job_Number LIKE ('%#trim(FORM.Job_Number)#%')

<CFELSEIF len( trim( FORM.startDate ) )>
AND Date_Assigned BETWEEN #CreateODBCDate(FORM.startDate)# AND #CreateODBCDate(FORM.endDate)#

etc...

Still getting an error message:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 7: Incorrect syntax near 'No'.

Which refers to my cfoutput statement within the cfelese?

Thanks,
melissa

Can you post the altered code, because I don't see a NO anywhere...

Yeah sure! Here it is, thanks for taking a look

The "No" term I get the error message on is the first word in my cfelse statement. Ignore the "you bozo" comment (in the code) - I was getting pretty frustrated yesterday!

melissa