Got error when updating record with blank date

[leekikwan]

I am having a error message when I update the record with blank date field which is formatted as follows.

<cfquery NAME = &quot;EditRecord&quot; datasource=&quot;xplant&quot;>
UPDATE T_TRANSPLANT
SET RDR='#FORM.RDR#',
REFDATE=TO_DATE('#FORM.REFDATE#', 'MM/DD/YYYY'),
RCDATE=TO_DATE('#FORM.RCDATE#', 'MM/DD/YYYY'),
RSDATE=TO_DATE('#FORM.RSDATE#', 'MM/DD/YYYY'),
XTYPE='#FORM.XTYPE#',
EVALDATE=TO_DATE('#FORM.EVALDATE#','MM/DD/YYYY'),
LISTDATE=TO_DATE('#FORM.LISTDATE#','MM/DD/YYYY'),
ADATE=TO_DATE('#FORM.ADATE#','MM/DD/YYYY'),
XDATE=TO_DATE('#FORM.XDATE#','MM/DD/YYYY'),
DDATE=TO_DATE('#FORM.DDATE#','MM/DD/YYYY'),
REPAT_DATE=TO_DATE('#FORM.REPAT_DATE#','MM/DD/YYYY'),
XMEMO='#FORM.XMEMO#'
WHERE XID = #FORM.XID#
</cfquery>

I want to use this date format and the users can skip the date fields (null date) if necessary. Need your help.

 

[r937]

validate your form fields to ensure that they are filled in

if a field is not filled in and you wish to interpret this as an indicator that the associated database column should be set to null, then you can alter the UPDATE statement on the fly like this:

update t_transplant
set rdr='#form.rdr#'
, refdate=
<CFIF Len(form.refdate)>
to_date('#form.refdate#', 'mm/dd/yyyy')
<CFELSE> null </CFIF>
, rcfdate=
<CFIF Len(form.rcfdate)>
to_date('#form.rcfdate#', 'mm/dd/yyyy')
<CFELSE> null </CFIF>
etc.

rudy

http://r937.com/

 

[leekikwan]

Thanks thousands.
It works.