Hey There,

I am a new to cold fusion. I am trying to set up a search form. I have 3 fields by which the rows can be fetched from the sql server database.

Field1 Field2 Field3

I have set it up such that

<CFQUERY datasource="dsn" name="results">

SELECT * FROM vwname
WHERE
<CFIF '#Form.Field1#' IS NOT ""> AND column_name1 LIKE '%#Form.Field1#%' </cfif>
<CFIF '#Form.Field2#' IS NOT "">AND column_name2 LIKE '%#Form.Field2#%'</cfif>
<CFIF '#Form.Field3#' IS NOT "">AND column_name3 = '#Form.Field3#'</cfif>

</CFQUERY>

This is the error I get:

ODBC Error Code = 37000 (Syntax error or access violation)



[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AND'.

The error occurred while processing an element with a general identifier of (CFQUERY)

Can someone help me identify this problem?

Thanks in advance.

Gasa

Looks like your WHERE clause always begins with the word AND. A little more logic can take care of that, though:

My ColdFusion is a bit rusty, but something like this:

SELECT * FROM vwname
WHERE
<CFIF '#Form.Field1#' IS NOT "">column_name1 LIKE '%#Form.Field1#%' </cfif>
<CFIF '#Form.Field2#' IS NOT "" AND '#Form.Field1#' IS NOT ""> AND column_name2 LIKE '%#Form.Field2#%'
<CFELSE> column_name2 = '#Form.Field2#' </cfif>
<CFIF '#Form.Field3#' IS NOT "" AND ('#Form.Field2#' IS NOT "" OR '#Form.Field1#' IS NOT "")> AND column_name3 LIKE '%#Form.Field3#%'
<CFELSE>column_name3 LIKE '%#Form.Field3#%'
</cfif>

Hope that helps


Just Another Random Thought

greykher is correct about your incorrect SQL syntax, however, the easier coding is to use the following:

WHERE 0=0
<CFIF '#Form.Field1#' IS NOT ""> AND column_name1 LIKE '%#Form.Field1#%' </cfif>
<CFIF '#Form.Field2#' IS NOT "">AND column_name2 LIKE '%#Form.Field2#%'</cfif>
<CFIF '#Form.Field3#' IS NOT "">AND column_name3 = '#Form.Field3#'</cfif>
========================================
Buh Bye!

Cheers,
Me

Thanks guys! for replying, the trick 'where 0=0' works.