CFGrid Date Picker "Error invoking CFC"

Got the code from http://cfsilence.com/blog/client/index.cfm/2008/4/7/CFGrid-Date-Picker-Editor
but I still can't get it working - driving me crazy :"Error invoking CFC/cfartgallery/artists.cfc :Error Executing Database Query..."
- The other columns update - no problem
- Update LAST Modified (date) column - error

I'm sure this is easy for someone to quickly fix, much appreciate any help.

see firebug results:
------------------------------------------------------------
_cf_nocache      true
_cf_nodebug      true
argumentCollection      {"gridaction":"U","gridrow":{"ARTISTID":3,"FIRSTNAME":"Eliciaweruio","LASTNAME":"Kimff","ADDRESS":"2523 National Blvd","CITY":"Los Angeles","STATE":"CA","POSTALCODE":"90064-5134","LASTMODIFIED":"04/01/2008","CFGRIDROWINDEX":2},"gridchanged":{"LASTMODIFIED":"2008-04-09T00:00:00"}}
method      editArtist
returnFormat      json

------------------------------------------------------------
ColdFusion Error:
Error Executing Database Query.
'LASTMODIFIED' is not a column in table or VTI 'APP.ARTISTS'.
 
The error occurred in C:\ColdFusion8\wwwroot\cfartgallery\artists.cfc: line 55

53 :                 UPDATE artists
54 :                 SET #colname# = '#value#'
55 :                 WHERE artistid = #ARGUMENTS.gridrow.artistid#
56 :                 </cfquery>
57 :             </cfcase>

SQLSTATE         42X14
SQL          UPDATE artists SET LASTMODIFIED = '2008-04-16T00:00:00' WHERE artistid = 1
VENDORERRORCODE         30000
DATASOURCE         cfartgallery
Resources:

    * Check the ColdFusion documentation to verify that you are using the correct syntax.
    * Search the Knowledge Base to find a solution to your problem.

Browser         Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101203 Firefox/3.6.13 ( .NET CLR 3.5.30729)
Remote Address         127.0.0.1
Referrer         http://localhost:8500/cfartgallery/DatePicker.cfm
Date/Time         27-Dec-10 10:23 AM
Stack Trace
at cfartists2ecfc794488454$funcEDITARTIST.runFunction(C:\ColdFusion8\wwwroot\cfartgallery\artists.cfc:55) at cfartists2ecfc794488454$funcEDITARTIST.runFunction(C:\ColdFusion8\wwwroot\cfartgallery\artists.cfc:55)

java.sql.SQLSyntaxErrorException: 'LASTMODIFIED' is not a column in table or VTI 'APP.ARTISTS'.
datepicker.cfm
------------------------------------------------

<cfsetting showdebugoutput="false">

<cfajaximport tags="cfinput-datefield" />

<html>
<head>
<title>Edit Artist Grid</title>
<link href="/CFIDE/scripts/ajax/ext/resources/css/ytheme-aero.css" rel="stylesheet" type="text/css">

<style>
.x-menu-list{
margin: 0px; padding: 0px;
}
</style>

<style>
.x-date-picker{
   width:150px;
}
</style>

<script type="text/javascript" src="/CFIDE/scripts/ajax/ext/ext-all.js"></script>

</head>

<cfsavecontent variable="js">
<script type="text/javascript">
init = function(){
//grid object

grid = ColdFusion.Grid.getGridObject("ArtistGrid");

//column model

cm = grid.getColumnModel();

//we need to know the column id

stIndex = cm.findColumnIndex("LASTMODIFIED");

var df = new Ext.grid.GridEditor(
   new Ext.form.DateField(
      {
         format: 'm/d/Y',
         minValue: '04/01/08'
      }
   )
);


cm.setEditor(stIndex, df);
cm.setRenderer(stIndex, Ext.util.Format.dateRenderer('m/d/Y'));

grid.reconfigure(grid.getDataSource(),cm);
}
</script>
</cfsavecontent>
<cfhtmlhead text="#js#" />
<cfset ajaxOnLoad("init") />
<body>


<a href="javascript:init();">init</a>
<cfquery name="getArtists" datasource="cfartgallery">
SELECT artistId, firstname, lastname, address, city, state,
postalcode, email, '04/01/2008' as lastModified
FROM Artists
</cfquery>

<cfset args = structNew()>
<cfset args.name = "ArtistGrid">

<cfset args.format = "html">
<cfset args.query = "getArtists">
<cfset args.stripeRows = true>
<cfset args.selectColor = "##D9E8FB">
<cfset args.selectmode = "edit">
<cfset args.onchange = "cfc:artists.editArtist({cfgridaction},{cfgridrow},{cfgridchanged})">

<cfform>
<cfgrid attributeCollection="#args#">
<cfgridcolumn name="artistid" display="false">
<cfgridcolumn name="firstname" header="First Name">
<cfgridcolumn name="lastname" header="Last Name">
<cfgridcolumn name="address" header="Address">
<cfgridcolumn name="city" header="City">
<cfgridcolumn name="state" header="State">
<cfgridcolumn name="postalcode" header="Zip">
<cfgridcolumn name="lastModified" header="Last Modified">
</cfgrid>
</cfform>

</body>
</html>


artists.cfc
-----------------------------
<cfcomponent output="false">


    <cfset THIS.dsn="cfartgallery">


    <!--- Get artists --->
    <cffunction name="getArtists" access="remote" returntype="struct">
        <cfargument name="page" type="numeric" required="yes">
        <cfargument name="pageSize" type="numeric" required="yes">
        <cfargument name="gridsortcolumn" type="string" required="no" default="">
        <cfargument name="gridsortdir" type="string" required="no" default="">

        <!--- Local variables --->
        <cfset var artists="">

        <!--- Get data --->
        <cfquery name="artists" datasource="#THIS.dsn#">
        SELECT artistid, lastname, firstname, email 
        FROM artists
        <cfif ARGUMENTS.gridsortcolumn NEQ ""
            and ARGUMENTS.gridsortdir NEQ "">
            ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdir#
        </cfif>
        </cfquery>

        <!--- And return it as a grid structure --->
        <cfreturn QueryConvertForGrid(artists,
                            ARGUMENTS.page,
                            ARGUMENTS.pageSize)>
    </cffunction>


    <!--- Edit an artist --->
    <cffunction name="editArtist" access="remote">
        <cfargument name="gridaction" type="string" required="yes">
        <cfargument name="gridrow" type="struct" required="yes">
        <cfargument name="gridchanged" type="struct" required="yes">

        <!--- Local variables --->
        <cfset var colname="">
        <cfset var value="">

        <!--- Process gridaction --->
        <cfswitch expression="#ARGUMENTS.gridaction#">
            <!--- Process updates --->
            <cfcase value="U">
                <!--- Get column name and value --->
                <cfset colname=StructKeyList(ARGUMENTS.gridchanged)>
                <cfset value=ARGUMENTS.gridchanged[colname]>
                <!--- Perform actual update --->
                <cfquery datasource="#THIS.dsn#">
                UPDATE artists
                SET #colname# = '#value#'
                WHERE artistid = #ARGUMENTS.gridrow.artistid#
                </cfquery>
            </cfcase>
            <!--- Process deletes --->
            <cfcase value="D">
                <!--- Perform actual delete --->
                <cfquery datasource="#THIS.dsn#">
                DELETE FROM artists
                WHERE artistid = #ARGUMENTS.gridrow.artistid#
                </cfquery>
            </cfcase>
        </cfswitch>
    </cffunction>


</cfcomponent>

Open in new window

Nigel-SAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

duncancummingCommented:
ok, the problem's obviously the date column!  You don't bother calling the getArtists function in the CFC.  Instead you have your own getArtists query inline in your .cfm page.  That's fine.  However you add lastModified as a hardcoded value, leading me to believe there is no lastModified column in the Artists table?  That's also fine.  The same query in the cfc doesn't select that column either.  So when you then come to update the table, if that column doesn't exist, then you're going to have a problem.

However if the column does exist, then the problem will be to do with the way the date is formatted.  Your query you have a string that looks like a date, '04/01/2008'.  So if the column exists, is it of a Date / DateTime or a Varchar?  If a Date, then passing it a string is going to cause problems (e.g. is 04/01 the 1st April or 4th Jan?).  Instead you want to pass it a valid ColdFusion date object.  
Nigel-SAAuthor Commented:
Duncan, thanks for your help on this!!

You're correct, there is no date in the artists table. However I'm trying to use it in a real situation talking to an Oracle db using a date field and still the same error.

If I manually enter the date 23 dec 2010 it accepts it no problem.
See CF AJAX logger notes:
":{"DATE_SITE_NOTIFIED":"23 dec 2010"}}

If I include the above code in, it gives a Error invoking CFC
See CF AJAX logger notes:
":{"DATE_SITE_NOTIFIED":"2010-12-29T00:00:00"}}

How does one pass a valid CF date object?
duncancummingCommented:
Well you would use CreateDate, CreateDateTime or ParseDateTime to create that date.  Alternatively, in the original query, use a SQL date function to get the date you require instead of hardcoding it as a string.  

If you're  using ColdFusion and wanting to have it as part of your query, you could use QueryAddColumn and QuerySetCell to populate the query with it:

<cfset QueryAddColumn(getArtists, "lastModified")>

<cfset yourDate = CreateDateTime(2008, 4, 1, 0, 0, 0)>

<cfloop query="getArtists">
	<cfset QuerySetCell(getArtists, "lastModified", yourDate, getArtists.CurrentRow)>
</cfloop>

Open in new window



If you're using Oracle, I think you could use To_Date() to convert the string into a proper date within the query:

SELECT to_date('04/01/2008') as lastModified

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Nigel-SAAuthor Commented:
Thanks

I'm going to stick to ColdFusion - Oracle - sorry for the confusion - the real situation.

I changed the date field "DATE_SITE_NOTIFIED" in oracle to a VARCHAR2 - 30 byte, and the date selected was saved - "2010-12-29T00:00:00"

Problem is when displayed in CFGRID it displays "NaN NaN NaN" I know it's a formatting issue, but I just can't get it nailed.

Thanks - almost there...

I've tried various date format substitutes in code below like:

 <!--- Date Picker--->
init = function(){
//grid object

grid = ColdFusion.Grid.getGridObject("SuperGrid");

//column model

cm = grid.getColumnModel();

//we need to know the column id

stIndex = cm.findColumnIndex("DATE_SITE_NOTIFIED");

var df = new Ext.grid.GridEditor(
   new Ext.form.DateField(
      {
         format: 'm/d/Y',
         minValue: '04/01/08'
      }
   )
);


cm.setEditor(stIndex, df);
cm.setRenderer(stIndex, Ext.util.Format.dateRenderer('m/d/Y'));

grid.reconfigure(grid.getDataSource(),cm);
}



<!--- Date Picker--->
init = function(){
//grid object

grid = ColdFusion.Grid.getGridObject("SuperGrid");

//column model

cm = grid.getColumnModel();

//we need to know the column id

stIndex = cm.findColumnIndex("DATE_SITE_NOTIFIED");

var df = new Ext.grid.GridEditor(
   new Ext.form.DateField(
      {
         format: 'd m Y',
         minValue: '01 Dec 2008'
      }
   )
);


cm.setEditor(stIndex, df);
cm.setRenderer(stIndex, Ext.util.Format.dateRenderer('d m Y'));

grid.reconfigure(grid.getDataSource(),cm);
}
 
</script>

Open in new window

duncancummingCommented:
This sounds like an entirely different question from the original.  I don't know enough about cfgrid to help you.
Nigel-SAAuthor Commented:
Thanks for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.