Skip to main content
N
NickoSwimmer
Participating Frequently
July 25, 2008
Question

CFINPUT with datefield type issue

  • July 25, 2008
  • 2 replies
  • 731 views
I have a personal information form that has a hire date entry field. I would like to make the field optional, yet I have to enter a hire date as I get an error if I dont. The database autofills the hire date field with the word "null" so if I submit the form without entering a date, I get an error message saying: "Error "null" is an invalud date or time string."

Also, in my database, I have Hire Date as a type DATE, null is selected, and default is 0000-00-00. On a side note, even though I have 0000-00-00 entered as the default, the word "null" still populates the hire date field on the page.

Here is my code:

<cfformgroup type="horizontal" visible="yes" enabled="yes" style="marginLeft:-74;" >

<cfinput type="Datefield" name="HireDate" label="Hire Date:" width="80" bind="{contactList.selectedItem.HireDate}" onchange="UpdateSelect(HireDate, selectYear1);"/>
<cfselect name="selectYear1" width="80" label="" onchange="setStartYear(HireDate, selectYear1);">
<cfoutput>
<option value="">Pick Year</option>
<cfloop index="i" from="1935" to="#thisyear#">
<option value="#i#">#i#</option>
</cfloop>
</cfoutput>
</cfselect>
</cfformgroup>


Any help with this problem would be much appreciated.

Thanks,
Nick Butler
    This topic has been closed for replies.

    2 replies

    A
    Anonymous
    July 25, 2008
    Then I would suspect that it is not receiving an initial value through the bind.

    It sounds that if the user does not enter in a date, then the datefield is returning a string of "null".

    If you cannot figure out a way on the client side to correct this, you can always validate it on the server side if indeed the value is returned as a text string..

    <cfif form.HireDate EQ 'Null'>
    <cfset newHireDate = ''>
    <cfelse>
    <cfset newHireDate = '#form.HireDate#'>
    </cfif>

    Then use your new variable as the date input.
    N
    NickoSwimmerAuthor
    Participating Frequently
    July 25, 2008
    Ok, I implimented your conditional form and it looked like I was on the right step as the hire date field was blank, however, when I tried to submitt the form, I still got the the error "null". I don't know where the null value is coming from as it looks like there is nothing in the entry field.

    I am considering taking another approach to this problem. What if I created some check boxes that ask if the user wants to enter a hire date or not. If so, then the hire date entry field appears. what do you think about this plan?

    Thanks.
    L
    Leonard_B
    Inspiring
    August 15, 2008
    Hi Nick,

    I am not sure which insert method you are using for inserting
    your form information into the database. I prefer to insert my
    form information via a <cfquery>.

    Below is the code I use in my forms and have not encountered
    any issues with blank date fields being inserted into a DB.

    <cfquery name="qry_name" datasource="yourdatasource">
    Insert Into tbl_name (
    db_field,
    hiredate,
    db_field)
    Values(
    nullif(<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.form_field#">,''),
    nullif(<cfqueryparam cfsqltype="CF_SQL_DATE" value="#form.hiredate#">,''),
    nullif(<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.form_field#">,'')
    )
    </cfquery>

    With the above code, should the person leave the hire date field blank, it will
    insert a null value into the db field. If the person enters a date, it will insert the
    date into the db field formatted as yyyy-mm-dd.

    Leonard B
    A
    Anonymous
    July 25, 2008
    When you receive the error, what is the value being inserted for selectYear1? Since you are running javascript on that particular field, most likely that script is returning a string with the value of "null" since there is no current value.

    When you try and insert a text string into a date field in SQL, it will return an error because a text string, of course, is not a date.

    You will either need to remove your javascript or at least alter it to have a default date. But as of now (without looking at your script), my guess is that your script is returning the string "null" as a value.

    A simple alert on that field value will tell you if that is the problem: alert('document.yourformname.selectYear1.value');
    N
    NickoSwimmerAuthor
    Participating Frequently
    July 25, 2008
    I have tried completely removing the javascript such that this is all the hire date input field looks like:

    <cfinput type="Datefield" name="HireDate" label="Hire Date:" width="300" bind="{contactList.selectedItem.HireDate}" />

    Unfortunately I still get type NULL in my field. I don't know where this null is coming from. I also commented out everything that was related to selectYear1. In my database, I have it set to not null, with a default value of 0000-00-00.

    If however I set my database default value to something like 1900-01-01, it does display in my hire date entry field on the page, but it displays in a weird format:

    Mon Jan 1 00:00:00 GMT-0800 1900

    If I leave that entry in the hire date field, I can submit the form ok, but the date gets converted to 01/01/2000.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices