Link to home
Create AccountLog in
Avatar of Nigel-SA
Nigel-SA

asked on

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

Avatar of duncancumming
duncancumming
Flag of United Kingdom of Great Britain and Northern Ireland image

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.  
Avatar of Nigel-SA
Nigel-SA

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of duncancumming
duncancumming
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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

This sounds like an entirely different question from the original.  I don't know enough about cfgrid to help you.
Thanks for your help