>exists & then, if not, insert a new one, eh?
Yes. exactly.
>Do you know what fields are required and what is optional. You probably
>need to run your query against only the mandatory fields to determine what
>constitutes a 'unique' record (some would call this the definition of a
>primary key).
No fields are required and though I realize that's a big *^&&*^&, that is the request from my boss.
>Anyhow, you may be having issues with NULLS which are different than ''
>(empty). So you may need to try something like
>
><cfif Trim(Form.address) EQ "">
> /* write qry statement here */
> ... WHERE form.address EQ '' OR form.address EQ NULL (this would
>cover both types of 'null-ness')
I tried something similar and it didn't work (same response as before). btw, I think instead of form.address it should just be address cause that's the field name. Here's what I did:
<cfquery name="dup_check" datasource="dpch">
SELECT firstname,middlename,lastname,address FROM alumni_contacts
<cfif Trim(Form.address) EQ "">
WHERE firstname = '#Form.firstname#' AND middlename = '#Form.middlename#' AND lastname = '#Form.lastname#' AND address='' OR address=NULL
<cfelse>
WHERE firstname = '#Form.firstname#' AND middlename = '#Form.middlename#' AND lastname = '#Form.lastname#' AND address='#Form.address#'
</cfif>
Unfortunately, I received the same response from the query - 0 records returns when I KNOW that the data is exactly the same.
>btw, Oracle supports a sql command known as UPSERT (combo of insert &
>update) which may be of use if that is your environment.
>
>good luck, -mike
Yeah, I'm in Oracle and that's good to know, though seems a little weird, thanks.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
