>
> SELECT *
> FROM tblCheckout
> WHERE
> videoID = <cfqueryparam value="#URL.videoID#"
> cfsqltype="cf_sql_integer"> AND
> actual_returndate = <cfqueryparam value=""
> cfsqltype="cf_sql_date" null="yes">
>
> i'm getting 0 records returned, where I know there is 1
> record that matches the criteria.
>
> I'm getting hung up on the actual_returndate value, where
> i'm searching for a NULL.
>
> it works without <cfqueryparam>:
>
> SELECT *
> FROM tblCheckout
> WHERE
> videoID = <cfqueryparam value="#URL.videoID#"
> cfsqltype="cf_sql_integer"> AND
> actual_returndate IS NULL
>
> returns the record i'm looking for.
>
> Am I using <cfqueryparam> wrong here?
If you know that you want to retrieve only records where actual_returndate
is NULL, there's no need to use CFQUERYPARAM there - just use
"actual_returndate IS NULL". The NULL attribute of CFQUERYPARAM is useful
when you don't know whether the value should be NULL until runtime, in which
case you can do something like this:
WHERE field = <cfqueryparam value="#Form.foo#" cfsqltype="cf_sql_whatever"
null="#YesNoFormat(NOT Len(Trim(Form.foo))#">
However, not all databases allow you to use NULL in an equality comparison;
in those cases you need to use the IS operator instead, which would lead to
something like this:
<cfset bFooNotNull = Len(Trim(Form.foo))>
...
WHERE field <cfif bFooNotNull> = <cfqueryparam ...> <cfelse> IS NULL </cfif>
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
