Skip to main content
A
Al_D_
New Participant
October 7, 2010
Question

Date Insert 2010-08-02T00:00:00 from CFGRID to Access DB Insert

  • October 7, 2010
  • 2 replies
  • 879 views

I have a datagrid that is bound to a cfc.  The datagrid has a date in the type = date column and the grid is bound to an update query to an Access database in the cfc.  The date that is being passed from the grid is this format 2010-08-02T00:00:00.  It is throwing an error on the update for the sql update.  How do I convert/format the date to get it into the database.

ERROR:  2010-08-02T00:00:00 is an Invalid date or time string

Code for Grid:

<cfform>
        <cfgrid
            name="basicGrid"
            bind="cfc:bindFeeder.getData({cfgridpage}, {cfgridpagesize}, {cfgridsortcolumn}, {cfgridsortdirection})"
            format="html"
            autowidth="true"
            striperows="true"
            title="Bookings Tracker Data Entry / Update Grid"
            collapsible="true"
            pagesize="20"
            selectmode="edit"
            onchange="cfc:bindFeeder.editData({cfgridaction}, {cfgridrow}, {cfgridchanged})"
            insert="true"
            delete="true"
            insertbutton="Create Booking"
            deletebutton="Delete Booking"
            onerror="catchGridError"
        >
       
            <cfgridcolumn name="ISU" header="ISU" values="#ISUData#" />
            <cfgridcolumn name="Market" header="Market" values="#MarketData#" />
            <cfgridcolumn name="ClientMgr" header="Client Mgr" select="false" />
            <cfgridcolumn name="PrjStartDate" header="Prj Start" type="date" />
            <cfgridcolumn name="CompanyName" header="Company Name" />
        </cfgrid>
    </cfform>

CFC Code:

<cffunction name="editData" access="remote" returntype="boolean">
        <cfargument name="gridaction">
        <cfargument name="gridrow">
        <cfargument name="gridchanged">

        <cfswitch expression="#Arguments.GridAction#">
       
        <!--- UPDATE Data IN the database --->
            <cfcase value="U">
               
                    <cfquery datasource="#REQUEST.dsn#">
                    UPDATE BookingsInfo
                    SET
                        ILT = ILT
                        <cfif StructKeyExists(Arguments.GridChanged, "ISU")>
                            , ISU = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Arguments.GridChanged.ISU#" />
                        </cfif>
                        <cfif StructKeyExists(Arguments.GridChanged, "Market")>
                            , Market = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Arguments.GridChanged.Market#" />
                        </cfif>
                        <cfif StructKeyExists(Arguments.GridChanged, "CompanyName")>
                            , CompanyName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Arguments.GridChanged.CompanyName#" />
                        </cfif>
                        <cfif StructKeyExists(Arguments.GridChanged, "PrjStartDate")>
                             , PrjStartDate = <cfqueryparam cfsqltype="CF_SQL_DATE" value="#Arguments.GridChanged.PrjStartDate#" />
                        </cfif>
                    WHERE
                        ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.GridRow.ID#" />
                    </cfquery>
            </cfcase>

    This topic has been closed for replies.

    2 replies

    A
    Al_D_Author
    New Participant
    October 8, 2010

    See my answer above.

    -
    -__cfSearching__-
    Inspiring
    October 8, 2010

    Glad you solved your problem.  But it's always nice to give the authors of open source code credit for their work ;-)

    http://www.bennadel.com/blog/811-Converting-ISO-Date-Time-To-ColdFusion-Date-Time.htm

    Just my $0.02

    A
    Al_D_Author
    New Participant
    October 8, 2010

    Yes, it is always good to give credit to the authors. It was late and I was tired and didn't even think of it.

    Sorry about that

    A
    Al_D_Author
    New Participant
    October 8, 2010

    I have found a solution online for this.  The date that is created is an ISODate Time that needs to be converted to a Coldfusion Date Time.  Here is what I added to my cfc template:

    <cffunction
        name="ISOToDateTime"
        access="public"
        returntype="string"
        output="false"
        hint="Converts an ISO 8601 date/time stamp with optional dashes to a ColdFusion date/time stamp.">

        <!--- Define arguments. --->
        <cfargument
            name="Date"
            type="string"
            required="true"
            hint="ISO 8601 date/time stamp."
            />

        <!---
            When returning the converted date/time stamp,
            allow for optional dashes.
        --->
        <cfreturn ARGUMENTS.Date.ReplaceFirst(
            "^.*?(\d{4})-?(\d{2})-?(\d{2})T([\d:]+).*$",
            "$1-$2-$3 $4"
            ) />
    </cffunction>

    Then for the insert to the database in the Cfquery insert:
    <cfquery datasource="#REQUEST.dsn#">
                        INSERT INTO  BookingsInfo(
                            ClientMgr
                            ,ClientMgrUID
                            ,ISU
                            ,Market
                            ,PrjStartDate

                        ) VALUES (
                            <cfqueryparam cfsqltype="cf_sql_varchar" value="#Session.sn#" />
                            , <cfqueryparam cfsqltype="cf_sql_varchar" value="#Session.UID#" />
                            , <cfqueryparam cfsqltype="cf_sql_varchar" value="#Arguments.GridRow.ISU#" />
                            , <cfqueryparam cfsqltype="cf_sql_varchar" value="#Arguments.GridRow.Market#" />
                            , <cfqueryparam cfsqltype="CF_SQL_DATE" value="#ISOToDateTime(Arguments.GridRow.PrjStartDate)#" />
                        )
                        </cfquery>

    Hope this helps someone else.


    Regards,

    Al D.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices