Updating DB date field as NULL from Coldfusion code

[rtj_ram_tcs]

Hello,

I'm running an insert query and one of the DB fields (DateCreated) is date/time.

I am using the following query to insert values

<CFQUERY Name="Inserttable" DATASOURCE="#Session.DS1#">
INSERT into table1 (Num, NumSeq ,DateCreated)
Values ('#AcctNum#','#AcctNumseq#',#CreateODBCDate(DateCreated))#)
</CFQUERY>

I need to pass NULL value when the Datecreated field is empty ? How can i pass the NULL value ?

Pls advise.

Thanks,
Rams

[Newsgroup_User]

rtj_ram_tcs wrote:
> Hello,
>
> I'm running an insert query and one of the DB fields (DateCreated) is
> date/time.
>
> I am using the following query to insert values
>
> <CFQUERY Name="Inserttable" DATASOURCE="#Session.DS1#">
> INSERT into table1 (Num, NumSeq ,DateCreated)
> Values ('#AcctNum#','#AcctNumseq#',#CreateODBCDate(DateCreated))#)
> </CFQUERY>
>
> I need to pass NULL value when the Datecreated field is empty ? How can i pass
> the NULL value ?
>
> Pls advise.
>
> Thanks,
> Rams
>

In your code a large <cfif...> statement wrapped around your code so
that you can pass either the date value or a NULL value.

In my code making use of the usually recommended <cfqueryparam ...> tag
something like this.

<cfquery...>
...
VALUES (...,<cfqueryparam value="#createODBCDate(DateCreated)#"
cfsqltype="CF_SQL_DATE" null="#yesNoFomat(NOT isDate(DateCreated)#">
</cfquery>

This will send a null value if DateCreated is not a data string
otherwise it will send in the date value.

[rtj_ram_tcs]

hello Skinner,

Thanks much for your response. But I need to use the CFQUERY format which I have used above. I tried the below method but i got an error.

<CFQUERY Name="Inserttable" DATASOURCE="#Session.DS1#">
INSERT into table1 (Num, NumSeq ,DateCreated)
Values ('#AcctNum#','#AcctNumseq#',
<CFIF DateCreated is " " OR len(DateCreated) is 0 >
'#CreateODBCDate(DateCreated)#'
<CFELSE>
NULL
</CFIF>
)
</CFQUERY>

[Newsgroup_User]

Ian's code IS for cfquery

you should probably read up on <cfqueryparam> tag...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Newsgroup_User]

rtj_ram_tcs wrote:
> hello Skinner,
>
> Thanks much for your response. But I need to use the CFQUERY format ...

As Azadi says, my example IS cfquery format.

What was the error?

Your logic is backwards. Your if statement says if dateCreated is an
single space or has a length of zero, pass in the the value, otherwise
if it is not empty or a space pass in NULL.

P.S. You could combine your if condition as such: <CFIF
len(trim(dateCreated)) IS 0>

P.P.S. You can cover other possible errors, such as somebody entering
"mom's apple pie" in a date field which is neither an empty string nor a
space but it is still not a date. The use of the isDate() function will
filter out these other invalid strings as well.

[Dan_Bracuk]

I tend to be a bit more methodical.

before the query.

if your variable is a date
NullVar = false;
else
NullVar = true;

in the cfqueryparam tag
null="#NullVar#"

[SafariTECH]

If you are getting an error that says you can not insert a NULL value, then the database field is set to disallow NULLs and you won't be able to enter a NULL value anyways.

If that is the case, and if you have access to the table design, you need to allow NULL entries.

[Dan_Bracuk]

quote:

---

Originally posted by: SafariTECH
If you are getting an error that says you can not insert a NULL value, then the database field is set to disallow NULLs and you won't be able to enter a NULL value anyways.

If that is the case, and if you have access to the table design, you need to allow NULL entries.

---

Either that or use a default value. Maybe there is a reason that nulls are not allowed.