lesson 2: setting NULL values ----------------------------------------------------------------------------------
consider this INSERT statement within a CFC (just one field out of many). the db is SQLServer and the field a datetime**: CASE WHEN <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.IT_Request_Date#"> <> '' THEN CONVERT(datetime,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.IT_Request_Date#">,103) ELSE NULL END, this says: if the value is NOT '' (empty string) then convert the string to a datetime and insert else insert NULL regarding inserting/updating with NULL: there is an attribute within CFQUERYPARAM called "null" that, when is true, inserts a NULL into that field (regardless of the value in the "value" attribute) How do you automatically know when your data needs this NULL for the insert? check the length of the data for length = 0. <cfqueryparam cfsqltype="cf_sql_timestamp" value="#FORM.IT_Request_Date#" null="#YesNoFormat(not(len(FORM.IT_Request_Date)))#" /> what's all this mess? ... #YesNoFormat(not(len(FORM.IT_Request_Date)))# the YesNoFormat() (which is optional) will convert any 1/0 to true/ false (or Yes/No) len(FORM.IT_Request_Date) will return 0 if it's an empty string. But for us, "0" means "true - give us a NULL instead". That's what the NOT() does -reverses the 0 to 1 and therefore "length=0" to "TRUE" more info from someone else to confirm this: http://www.chapter31.com/2007/02/04/cfqueryparam-and-conditional-handling-of-nulls/ ==================================================== lesson 3: converting string dates to real database dates. ---------------------------------------------------------------------------------- Dates coming from forms are strings - not true datetime objects (at least not like the datetime objects expected by databases like SQLServer). Most of the time ColdFusion can happily convert it but sometimes an ommision either throws an error, inserts an impossible date or simply confuses AU format (dd/mm/yyyy) with US (mm/dd/yyyy). so... instead of converting strings with SQL to the 103 locale (Australia), let some ColdFusion functions and CFQUERYPARAM help you from this CONVERT(datetime,<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FORM.IT_Request_Date#">,103) to this: <cfqueryparam cfsqltype="cf_sql_timestamp" value="#LSParseDateTime(FORM.IT_Request_Date)#" null="#YesNoFormat(not(len(FORM.IT_Request_Date)))#" /> many people have had problems with cf_sql_timestamp (SQLServer- datetime-length:8) but the trick is to use LSParseDateTime() which will make a reasonable attempt to turn anything that looks like a date string into a proper date object. No mention of time means a default to midnight ("00.00.000") after LSParseDateTime() is used. in this case "cf_sql_timestamp" works quite happily with SQLServer- datetime-length:8 - provided that it recieves a datetime object (eg via LSParseDateTime() ), not a string representing one. No need for createODBCdate or splitting a dd/mm/yyyy on the "/" and reassembling. the added bonus is that the "LS" locale-specific series of functions will use the CF server settings to know that 01/02/2008 means the first of Feb 2008 (dd/mm/yyyy) and not 2nd of Jan (mm/dd/yyyy - US format) see: http://livedocs.adobe.com/coldfusion/6.1/htmldocs/functa36.htm ========================================================= these tips - and many more - are available _free_ from your local ColdFusion user group. Open to all, no charge and catering provided.There's probably a CFUG near you. The Qld CF user group has a meeting tonight. Why not come along? more info here: http://qld.cfug.org.au/ ========================================================= ** ignore FORM scope within a CFC - that's worth mentioning another time --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to [email protected] 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 -~----------~----~----~----~------~----~------~--~---
