Mike, I'll take a shot, but first I want to admit I'm not sure I'm
appreciating all the nuances of what you've put forth. Are you saying
basically that your insert/update fails because it expects either a date or
null for this dateapproved column? It's not clear, because you never refer
to a variable being the input for the date. You show either now() or empty
string, but perhaps that was just for making a point about their being a
date or not.
If the date is indeed a variable (let's call it mydate), could you not then
just add some CFML to test for the empty string or not:
dateapproved = <cfif mydate is "">NULL<cfelse><cfqueryparam,
value="#mydate#" cfsqltype="cf_sql_datetime"/></cfif>
Sorry if that's obvious and therefore not the issue. It's just that I could
just see someone concluding for some reason that they couldn't do a
CFQueryparam inside an IF, when indeed you can. (And for anyone who would
prefer the IIF over CFIF in this kind of statement, that doesn't work here
because it can't take tags, like the CFQueryParam, in its arguments, at
least as far as I know).
/Charlie
http://www.carehart.org/blog/ _____ From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Mike Kear Sent: Sunday, March 04, 2007 1:01 AM To: cfaussie Subject: [cfaussie] Another CFQUERYPARAM peculiarity Something that's been bugging me for a while but i never got around to finding the reason/fix for the problem. I figured out a workaround and never got back to the fix. Here's the problem .. i have a date field in a table thats (for example) specced as follows: DateApproved (datetime) Null If i insert into that field, I can use this in my cfquery insert statement no problem: <cfqueryparam, value="#now()#" cfsqltype="cf_sql_datetime"/> But if there's no value yet, (e.g if this record isnt approved yet) I get an error. I'd have thought if the value for DateApproved (in this example) is 'no value' then just having the following in the insert statement would work. No? <cfqueryparam, value="" cfsqltype="cf_sql_datetime"/> The workaround I've been using isnt really satisfactory because it defeats the purpose of <cfqueryparam. What i do is to drop the CFQUERYPARAM and use the following in the insert: #createODBCDateTime(now())# or nothing if there isn't a date yet. I suppose i could ensure there is always a date, and (to use this example) where the recod isnt approved, set the date to 1/1/1901 or something but that would seem to defeat the purpose too. Anyone see what i i'm doing wrong? Cheers Mike Kear --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~----------~----~----~----~------~----~------~--~---
<<attachment: att4e9a6.jpg>>
