Mike We use a version between the previous suggestions:
For strings we use : <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(stringHere)#" null="#NOT YesNoFormat(len(trim(stringHere)))#"> you can use the same principle to enter a null for a date. HTH Duncan On 3/5/07, Maximilian Nyman <[EMAIL PROTECTED]> wrote: > > How about > <cfqueryparam value="#myDate#" cfsqltype="cf_sql_datetime" null="#not > isDate(myDate)#" /> > > /Max > > > > On 3/5/07, Charlie Arehart <[EMAIL PROTECTED]> wrote: > > > > 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 > > > > > > > > > -- > > > Mobile: 0424 77 57 67 > Home: 02 9968 3924 > Skype: maxnyman > MSN Messenger: [EMAIL PROTECTED] > > > -- Duncan I Loxton [EMAIL PROTECTED] --~--~---------~--~----~------------~-------~--~----~ 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>>
