Yay!!!
For reference, my SQL now reads:
<cfquery dbtype="query" name="GetTaggedPhotos">
SELECT * from request.photolist WHERE
(
PhotoTag IS NOT NULL
AND
PhotoTag LIKE '%#trim(variables.thistag)#%'
);
</cfquery>
Thanks y'all!!!!
T
-----Original message-----
From: "Ben Nadel" [EMAIL PROTECTED]
Date: Tue, 7 Nov 2006 13:30:39 +0000
To: CF-Talk [email protected]
Subject: RE: Head Banging Access LIKE error in QoQ
> Tom,
>
> I am pretty sure that Query of queries using short circuit evaluation.
> This should allow you to test for NULL before actually performing the
> LIKE evaluation:
>
>
> WHERE
> ....
> AND
> (
> A IS NOT NULL
> AND
> A LIKE '%foo%'
> )
>
>
> If A IS NULL then the first part of the AND statement should fail and
> the rest should not be fired. The LIKE clause should only every be
> evaluated if the first part is also true (A IS NOT NULL).
>
>
> ......................
> Ben Nadel
> Certified Advanced ColdFusion MX7 Developer
> www.bennadel.com
>
> Need ColdFusion Help?
> www.bennadel.com/ask-ben/
>
> -----Original Message-----
> From: Tom King [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 07, 2006 8:25 AM
> To: CF-Talk
> Subject: RE: Head Banging Access LIKE error in QoQ
>
> Agh!
>
> that's it in a nutshell..
>
> :(
>
> Oh for a solution....*sigh*
>
> T
>
> -----Original message-----
> From: "Ben Nadel" [EMAIL PROTECTED]
> Date: Tue, 7 Nov 2006 13:21:22 +0000
> To: CF-Talk [email protected]
> Subject: RE: Head Banging Access LIKE error in QoQ
>
> > Tom,
> >
> > I think that is definitely the case. Check out this blog post:
> >
> > http://www.bennadel.com/index.cfm?dax=blog:144.view
> >
> > I have found that ColdFusion query of queries throws errors on NULL
> > fields when using LIKE, UPPER(), and LOWER().
> >
> >
> > ......................
> > Ben Nadel
> > Certified Advanced ColdFusion MX7 Developer www.bennadel.com
> >
> > Need ColdFusion Help?
> > www.bennadel.com/ask-ben/
> >
> > -----Original Message-----
> > From: Tom King [mailto:[EMAIL PROTECTED]
> > Sent: Tuesday, November 07, 2006 8:07 AM
> > To: CF-Talk
> > Subject: RE: Head Banging Access LIKE error in QoQ
> >
> > That's closer I think: I DO have empty fields interspersed with
> > populated fields in the column..
> > Just need to get the syntax right now..
> >
> > T
> >
> > -----Original message-----
> > From: "Katz, Dov B \(IT\)" [EMAIL PROTECTED]
> > Date: Tue, 7 Nov 2006 12:44:19 +0000
> > To: CF-Talk [email protected]
> > Subject: RE: Head Banging Access LIKE error in QoQ
> >
> > > Just a guess:
> > >
> > > You might have an empty phototag value somewhere
> > >
> > > Try
> > >
> > > WHERE ("" & phototag) LIKE "%#trim(variables.thistag)#%'
> > >
> > > Or, try ""+phototag (not sure of syntax)
> > >
> > > Does this fix the issue?
> > >
> > > Dov
> > >
> > >
> > > -----Original Message-----
> > > From: Tom King [mailto:[EMAIL PROTECTED]
> > > Sent: Tuesday, November 07, 2006 7:30 AM
> > > To: CF-Talk
> > > Subject: Head Banging Access LIKE error in QoQ
> > >
> > > OK, This have been driving me nuts...
> > >
> > > request.photolist exists, and has phototag etc fields.
> > > variables.thistag is also fine:
> > > BUT, this code:
> > >
> > > <cfquery dbtype="query" name="GetTaggedPhotos">
> > > SELECT * from request.photolist WHERE phototag LIKE
> > > '%#trim(variables.thistag)#%';
> > > </cfquery>
> > >
> > > Throws this very unhelpful error:
> > >
> > > The system has attempted to use an undefined value, which usually
> > > indicates a programming error, either in your code or some system
> > code.
> > >
> > > Null Pointers are another name for undefined values.
> > >
> > > java.lang.NullPointerException
> > >
> > > Using this works, but obvious doesn't return the same recordset, as
> > > it's looking for an exact match:
> > > <cfquery dbtype="query" name="GetTaggedPhotos">
> > > SELECT * from request.photolist WHERE phototag =
> > > '#variables.thistag#';
> > > </cfquery>
> > >
> > > Help???????
> > >
> > > Ta
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
> >
> >
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259425
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
