Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


coldfusion form with sql server

Posted on 2007-08-01
Medium Priority
Last Modified: 2008-01-09
Hi, I am using sql server 2000 and coldfusion. I have a form where I am asking the user to supply dates. (e.g 4/27/2007).
In my database I have the date type smalldatetime.  When the field is left blank, the date stored in the database is 1/1/1900.

I want it so if the field is blank and the user did not supply a date, then there should be no value in the database.

Help please.
Question by:tiredntroubled
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19608045
if you supply blank, sql server wont treat as NULL, instead it will fill the column with the default value of the datetime datatype . so if you want it to be null, you should pass as null

Author Comment

ID: 19608212
How would I pass it as a null?
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19608261
i am not sure about Coldfusion, if you can move this question under confusion section, i think some will help you.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Assisted Solution

rucky544 earned 400 total points
ID: 19609002
Use cfif to check if the form field contains anything
  <cfif not isdefined("form.datefield") or #form.datefield# is "">
If not then set the date field as null.

Insert into tblYourTable (yourdatefield)
values (<cfif not isdefined("form.datefield") or #form.datefield# is "">NULL<cfelse>"#form.datefield#" </cfif>)
LVL 10

Accepted Solution

js_vaughan earned 1200 total points
ID: 19611800
if you are familiar with using cfqueryparam, use the null attribute like so:

<cfqueryparam cfsqltype="cf_sql_date" value="#FORM.date#" null="#iif(isDate(FORM.date),false,true)#">
LVL 39

Assisted Solution

gdemaria earned 400 total points
ID: 19618187
Note that you don't want to use quotes " around your date.  You can use createODBCdate to convert it into sql.   Also testing  isDate() may be more complete than just testing for NULL, at least to protect database errors.  However, if the user has typed in an invalid date, you show shown them an error before it gets to this point.

update YourTable
 set dateField =
  <cfif isDate("form.datefield")>#createODBCdate(form.datefield)#<cfelse>NULL</cfif>

Also, they say (whomever "they" is) that iif () is a function to avoid due to its poor performance.  You can do the same with CFIF...

<cfqueryparam cfsqltype="cf_sql_date" value="#form.dateField#"
           <cfif NOT IsDate("form.dateField")>null="true"</cfif>  >


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question