coldfusion form with sql server

Posted on 2007-08-01
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
    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

    How would I pass it as a null?
    LVL 75

    Expert Comment

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

    Assisted Solution

    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

    if you are familiar with using cfqueryparam, use the null attribute like so:

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

    Assisted Solution

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now