passing time value to Access using cfinsert/cfupdate

[earachefl]

When either adding or updating records in an Access database, using cfinsert or cfupdate, time values always get set to 12:00 AM. The Access field is set to Data type:Date/Time. The value is set from the following field:

<cfinput type="text"
                        name="StartTime"
                        value="#StartTime#"
                        required="no"
                        validate="time"
                        validateat="onsubmit">

All other fields get populated correctly, including two Date fields.

I have a sinking feeling that my answer is going to be to not use cfinsert/cfupdate, but I await with bated breath.

[Dan_Bracuk]

I did a quick search on msaccess datatypes and didn't notice any time fields. Are you sure it really is one, and not a datetime field formatted for time only in access?

[earachefl]

Sorry, I must not have been clear. The Access field I'm referring to is indeed a Date/Time type.

I have two fields which are intended to hold dates and two fields which are intended to hold times. In the cfform, the two Date cfinputs use USDATE validation and the two Time fields use TIME validation. When either inserting or updating a record, the dates get inserted/updated correctly in the database, but the times get set to 12:00 AM.

[Dan_Bracuk]

Out of curiousity, what is the date portion of the field when you insert just the time?

If you want to continue to use cfinsert/cfupdate, you can always change the form values by prepending the appropriate date string to the time value.

[earachefl]

I'm not sure I understand your question...are you asking about the date portion of my "StartTime" and "EndTime" fields, in the cfform? Or in the database? These two fields are set up like so:

<cfinput type="text"
                        name="StartTime"
                        value="#StartTime#"
                        required="no"
                        validate="time"
                        validateat="onsubmit">

So as you can see, the validation type is "time". I was under the impression that ColdFusion would validate the field allowing only legal time values and pass that on without a date portion. (The dates are to be set separately, in seperate fields.)

As it turns out, if I modify the processing template to use standard SQL queries, I can use the existing form to enter all the data, no problem. Using the query (partially shown)

UPDATE EventCalendar
        SET StartDate=#CreateODBCDate(FORM.StartDate)#,
            EndDate=#CreateODBCDate(FORM.EndDate)#,
            StartTime=#CreateODBCTime(FORM.StartTime)#,
            EndTime=#CreateODBCTime(FORM.EndTime)#       
        WHERE EventID=#FORM.EventID#

or

INSERT INTO EventCalendar  (StartDate,
                                    EndDate,
                                    StartTime,
                                    EndTime)                                  
        VALUES    (#CreateODBCDate(FORM.StartDate)#,
                #CreateODBCDate(FORM.EndDate)#,
                #CreateODBCTime(FORM.StartTime)#,
                #CreateODBCTime(FORM.EndTime)#)

I can successfully insert or update records using the form, as long as I make sure that I enter values into the time fields (which are supposed to be optional, therefore the wish to use cfupdate and cfinsert). Now I have to figure out how to do the whole conditional SQL statements, which I had hoped to avoid.

So any ideas on why cfupdate and cfinsert won't successfully pass the validated time values to the database?

[Dan_Bracuk]

Regarding:

I'm not sure I understand your question...are you asking about the date  portion of my "StartTime" and "EndTime" fields, in the cfform? Or in the  database?

In the database.

Regarding:

So any ideas on why cfupdate and cfinsert won't successfully pass the  validated time values to the database?

Not sure.  What did your debugging information show you?

[earachefl]

My debugging info shows absolutely nothing at least in regards to form fields passed.

If I look at the Access database, the time fields that I've created in this way show a date of December 30, 1899. Does this help?

I created the following conditional code which seems to work:

<!--- Edit or add? --->
<cfif IsDefined("FORM.EventID")>
    <cfquery datasource="cpac">
        UPDATE EventCalendar
        SET StartDate=#CreateODBCDate(FORM.StartDate)#,
            EndDate=#CreateODBCDate(FORM.EndDate)#,
           <cfif len(trim(#FORM.StartTime#))>
                StartTime=#CreateODBCTime(FORM.StartTime)#,
                <cfelse>
                    StartTime=null,
            </cfif>
            <cfif len(trim(#FORM.EndTime#))>
                   EndTime=#CreateODBCTime(FORM.EndTime)#,
                <cfelse>
                    EndTime=null,
            </cfif>
            PresentedBy='#Trim(FORM.PresentedBy)#',
            EventDescription='#Trim(FORM.EventDescription)#',
            AdditionalInfo='#Trim(FORM.AdditionalInfo)#',
            IsFeatured=#FORM.IsFeatured#,
            FeaturedText='#Trim(FORM.FeaturedText)#'           
        WHERE EventID=#FORM.EventID#
    </cfquery>
<cfelse>
    <cfquery datasource="cpac">
        INSERT INTO EventCalendar  (StartDate,
                                    EndDate,
                                   <cfif IsDefined('#FORM.StartTime#')>
                                        StartTime,
                                    </cfif>
                                    <cfif IsDefined('#FORM.EndTime#')>
                                        EndTime,
                                    </cfif>
                                    PresentedBy,
                                    EventDescription,
                                    AdditionalInfo,
                                    IsFeatured,
                                    FeaturedText)                                  
        VALUES    (#CreateODBCDate(FORM.StartDate)#,
                #CreateODBCDate(FORM.EndDate)#,
               <cfif IsDefined('#FORM.StartTime#')>
                    #CreateODBCTime(FORM.StartTime)#,
                </cfif>
                <cfif IsDefined('#FORM.EndTime#')>
                    #CreateODBCTime(FORM.EndTime)#,
                </cfif>
                '#Trim(FORM.PresentedBy)#',
                '#Trim(FORM.EventDescription)#',
                '#Trim(FORM.AdditionalInfo)#',
                #FORM.IsFeatured#,
                '#Trim(FORM.FeaturedText)#')
     </cfquery>                                 
</cfif>

which is a far cry from:

<!--- Edit or add? --->
<cfif IsDefined("FORM.EventID")>
    <cfupdate datasource="cpac" tablename="EventCalendar">
<cfelse>
    <cfinsert datasource="cpac" tablename="EventCalendar">
</cfif>

[Dan_Bracuk]

Going back to my earlier suggestion of prepending a date string to your form variable, what date did you want to associate with your time fields.

[earachefl]

I don't care to associate any date with my time fields. I only care about the

time values. The dates that I care about will be stored in separate fields.