> From: Daniel Kessler
> <cfquery name="dup_check" datasource="ch">
> SELECT firstname,middlename,lastname,address FROM alumni_contacts
> WHERE firstname = '#Form.firstname#' AND middlename =
> '#Form.middlename#' AND lastname = '#Form.lastname#' AND
> address='#the_address#'
> </cfquery>
>
> If any of the form variables are left blank, the WHERE in the query
> fails to find the record that already exists.
>
> So, I thought well if it doesn't like blank fields, then check for
> that and maybe set it to empty quotes ''. But that doesn't seem to
> help.
You're generally on the right track. However, instead of using an empty string if the field is blank, don't do that part of the clause at all.
Here's the query re-written:
<cfquery name="dup_check" datasource="ch">
SELECT firstname,middlename,lastname,address FROM alumni_contacts
WHERE
1 = 1
<CFIF LEN(TRIM(form.firstname))>
AND firstname = '#Form.firstname#'
</cfif>
<CFIF LEN(TRIM(form.middlename))>
AND middlename = '#Form.middlename#'
</cfif>
<CFIF LEN(TRIM(form.lastname))>
AND lastname = '#Form.lastname#'
</cfif>
</cfquery>
Scott
---------------------------
Scott Brady
http://www.scottbrady.net/
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
