Updating and Adding Records Being Displayed In ColdFusion 8's cfgrid
Introduction:
The CF 8 Developer's Guide (pages 630-635) discusses the new cfgrid tag. I was especially interested in how to update cfgrid's data provider by changing the values displayed in the grid and by adding a new record to the underlying data. The developer's guide provides an example of how to update the data source for values displayed by the cfgrid. However, the documentation explains that "you cannot insert new rows directly in a grid...To add rows you must enter the data in a form, and make sure the grid refreshes after the form has been submitted." (page 633) Unfortunately, there is no example in the developer's guide of how to do that, so I created my own example code (based somewhat on the cfgrid update example provided in the developer's guide). To view a working version see: /cf8test/employees.cfm.
Discussion:
My example (download the code) shows the user a new employee form and a grid showing the current employees. You can fill out the new employee form and insert the data into the data provider. The grid showing the employees will be refreshed and display the new employee. If you want to change any of the values shown in the current form you can click on a field's value and type in a new value.
You can download my source code (a zip with two files: employees.cfm and employeeService.cfc). Just extract both files to the same folder and then use your browser to open the employees.cfm file.
Below are some excerpts from the example that I believe might need some explanation.
When you click on the Add Employee button, the following JavaScript function is executed.
function doInsertEmployee() {
/*
arguments are form name, cfc and method to send form values to, javascript function to handle result,
javascript function to handle error
*/
ColdFusion.Ajax.submitForm('insertForm', 'employeeService.cfc?method=insertData', resultInsertHandler, insertErrorHandler);
}
The ColdFusion.Ajax.submitForm function submits the form field values of the form named insertForm to the insertData function in the employeeService CFC. The JavaScript function resultInsertHandler is executed if everything works ok and the insertErrorHandler JavaScript function is called if there was a problem. Thanks to Dave Ferguson for insight on how to submit the form directly to a CFC function.
In the JavaScript function resultInsertHandler, I merely add a message saying the record was added and then clear the form field values so new values can be typed in. I also have the cfgrid refresh itself to display the new record. You can refresh the cfgrid using this JavaScript command: ColdFusion.Grid.refresh('employeeGrid', true); employeeGrid is the name of the cfgrid and true means keep the cfgrid on its current page.
Please note that in this example code, I'm not validating the user's input. Whatever the user inputs into the form is just sent to the insertData function in the EmployeeService CFC. Also the example code is very plain vanilla. Hopefully, the example code helps you understand how to add a record to data being displayed in a cfgrid.
http://www.coldfusionjedi.com/index.cfm/2007/7/26/...
Thanks for the info.
This doesn't work:
<cffunction name="insertData" access="remote" output="false" returntype="void">
<cfargument name="emp_id">
<cfargument name="firstName">
<cfargument name="lastName">
<cfargument name="email">
<cfargument name="company">
<cfquery name="team" datasource="flashy">
insert into employees (emp_id, firstname, lastname, email, company)
values (
<cfqueryparam cfsqltype="cf_sql_integer" value="#emp_id#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#firstName#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#lastName#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#email#">
<cfqueryparam cfsqltype="cf_sql_varchar" value="#company#">
)
</cfquery>
</cffunction>
But this one does. The field names are ok because the select and datagrid work with the company added.
<cffunction name="insertData" access="remote" output="false" returntype="void">
<cfargument name="emp_id">
<cfargument name="firstName">
<cfargument name="lastName">
<cfargument name="email">
<cfquery name="team" datasource="flashy">
insert into employees (emp_id, firstname, lastname, email)
values (
<cfqueryparam cfsqltype="cf_sql_integer" value="#emp_id#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#firstName#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#lastName#">,
<cfqueryparam cfsqltype="cf_sql_varchar" value="#email#">
)
</cfquery>
</cffunction>
Can someone let me know what I am missing?
Cheers
Peter
<cfqueryparam cfsqltype="cf_sql_varchar" value="#email#">
you're missing the comma. Should be:
<cfqueryparam cfsqltype="cf_sql_varchar" value="#email#">,
since there is another cfqueryparam after that one
Your a godsend. All good now.
Cheers
Peter
Any idea how to get the number of records returned by the cfc (TOTALROWCOUNT in the data) from the grid.
I wanted to bind an input box or something to a paged grid so I new what the whole record set size was not just the number of pages.
I have to say, from around the CF community, you have taken the CFGrid example to the next level, wow. I was bouncing around the links form the other CF bloggers, and I found this example. I was working on an application and could not figure out how to make the edits inline with the CFGrid. Thanks for your knowledge base & posting the sourse, I was able to make the adjustments to my application, and it worked fine.
Thanks again
Camilo
Does anyone know how to put a confirmation alert on the delete button in cfgrid? I can see people accidentally deleting records they didn't mean to!!
http://www.coldfusionguy.com/ColdFusion/blog/index...
Excellent post and example. :-)
My guess would be that they just want to keep the examples as simple as possible to demonstrate the point they are trying to communicate without cluttering the example code. I do know that Adobe does highly recommend the use of cfparam and cfqueryparam to validate any user submitted data.
Has anyone else run into this? Is there a way to disable application.cfc specifically for the employeeService.cfc? Maybe there is a way to place employeeService.cfc outside of this application folder and reference it there. (any idea of the syntax to reference a CFC that's up one folder?)
Overall, thank you for this great example! :-) When I don't add application.cfc it works great. It's amazing what can be accomplished in so little code.
If I place my employees.cfm file along with my application.cfc inside a folder called, "/user/employees.cfm" and then place my employeeService.cfc on the root...
Inside employees.cfm I can update the reference to the cfc with a leading slash as shown below and it will work properly since the Application.cfc will not interfere:
bind="cfc:/employeeService.cfc.getData( ...(rest of the path is the same)
Based on this example, it could be placed in any other folder outside of my application folder. I'm not sure if there is a better way to reference the application than this path relative to the root.
Once that function was removed from his example application.cfc, this example worked perfectly. :-)
In your CFC's getData() function, you'd need to pass the DeptID value as an additional parameter. What is the proper syntax for including this parameter when you call getData() in the bind parameter of the cfgrid tag?
What if the DeptID was not in a form field but was in the URL or some other scoped variable (e.g. session variable)?
If the DeptID was in a form field in form01, then you'd pass it like this:
<cfgrid
name="employeeGrid"
...
bind="cfc:employeeService.getData({form01:DeptID},{cfgridpage},..."
...
>
Does anyone know how to pass a variable from URL eg. an ID named URL.ID in the bind option in cfgrid?
trying my best...
And do I need the CFPARAMs?
<CFFORM>
<CFGRID NAME="GetMemberDataForDirectory"
FORMAT="HTML"
PAGESIZE="#URL.SHOWROWS#"
STRIPEROWS="YES"
APPENDKEY="YES"
HREF="/index.cfm?fuseaction=members.emailmember"
HREFKEY="ENCRYPTARCODE"
WIDTH="100%"
BIND="cfc:memberDirectory.getMembers(
{URL:equitytype},
{cfgridpage},
{cfgridpagesize},
{cfgridsortcolumn},
{cfgridsortdirection}
)">
<CFGRIDCOLUMN NAME="lastname" HEADER="Last Name" WIDTH="100"/>
<CFGRIDCOLUMN NAME="firstname" HEADER="First Name" WIDTH="100"/>
<CFGRIDCOLUMN NAME="City" HEADER="Home City" WIDTH="125">
<CFGRIDCOLUMN NAME="State" HEADER="State" WIDTH="50">
<CFGRIDCOLUMN NAME="equitytype" HEADER="Type" WIDTH="50">
<CFGRIDCOLUMN NAME="emailme" HEADER="CONTACT">
<CFGRIDCOLUMN NAME="ENCRYPTARCODE" DISPLAY="NO" HEADER="ENCRYPTARCODE">
</CFGRID>
</CFFORM>
<CFCOMPONENT OUTPUT="false">
<CFPARAM NAME="url.initial" DEFAULT="">
<CFPARAM NAME="url.state" DEFAULT="">
<CFPARAM NAME="url.equitytype" DEFAULT="all">
<!--- Get Members --->
<CFFUNCTION NAME="getMembers" 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="">
<CFARGUMENT NAME="equitytype" TYPE="string" REQUIRED="no" DEFAULT="">
<CFARGUMENT NAME="initial" TYPE="STRING" DEFAULT="#url.initial#">
<!--- Local variables --->
<CFSET var GetMemberDataForDirectory="">
<!--- Get data --->
<CFQUERY NAME="GetMemberDataForDirectory" DATASOURCE="ORCIntranet">
SELECT ARCODE,Lastname, Firstname, City, State, equitytype, EMAIL
FROM view_familyinfo_all_activemembers_withcontactinfo
WHERE FAMNUM <= '2' AND MISC1 <> '' and MISC1 BETWEEN '100' and '599'
<CFIF arguments.initial NEQ ''>AND LEFT(Lastname,1) = '#arguments.initial#'</CFIF>
<CFIF URL.state NEQ ''>AND STATE = '#URL.state#'</CFIF>
<CFIF URL.equitytype EQ 'equity'>AND MISC1 IN ('400','500')</CFIF>
<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(GetMemberDataForDirectory,
ARGUMENTS.page,
ARGUMENTS.pageSize)>
</CFFUNCTION>
</CFCOMPONENT>
I got help from http://experts-exchange (be sure you include that hyphen - haha) and here's the deal, for other users' benefit:
HURRAY!!!! so simple...
argh
they keys i was missing were
1. the <CFINPUT tag type=hidden name=MyURLVar> that transformed each url variable into a form variable, which was then in:
2. the BIND attribute which needs each var defined to be pushed to the CFC specified.
3. on the CFC of course each user variable needs CFARGUMENT definition and the SQL code uses ARGUMENT var prefixes.
FORM PAGE:
<CFFORM>
<CFINPUT NAME="equitytype" TYPE="HIDDEN" VALUE="#url.equitytype#">
<CFINPUT NAME="state" TYPE="HIDDEN" VALUE="#url.state#">
<CFINPUT NAME="initial" TYPE="HIDDEN" VALUE="#url.initial#">
<CFGRID NAME="GetMemberDataForDirectory"
FORMAT="HTML"
PAGESIZE="#URL.SHOWROWS#"
STRIPEROWS="YES"
SELECTONLOAD="FALSE"
APPENDKEY="YES"
HREF="/index.cfm?fuseaction=members.emailmember"
HREFKEY="ENCRYPTARCODE"
WIDTH="100%"
BIND="cfc:memberDirectory.getMembers(
{cfgridpage},
{cfgridpagesize},
{cfgridsortcolumn},
{cfgridsortdirection},
'#URL.equitytype#',
'#URL.state#',
'#URL.initial#'
)">
<CFGRIDCOLUMN NAME="lastname" HEADER="Last Name" WIDTH="100"/>
<CFGRIDCOLUMN NAME="firstname" HEADER="First Name" WIDTH="100"/>
<CFGRIDCOLUMN NAME="City" HEADER="Home City" WIDTH="125">
<CFGRIDCOLUMN NAME="State" HEADER="State" WIDTH="50">
<CFGRIDCOLUMN NAME="equitytype" HEADER="Type" WIDTH="50">
<!---<CFGRIDCOLUMN NAME="misc1" HEADER="misc1" WIDTH="50">--->
<CFGRIDCOLUMN NAME="emailme" HEADER="CONTACT">
<CFGRIDCOLUMN NAME="ENCRYPTARCODE" DISPLAY="NO" HEADER="ENCRYPTARCODE">
</CFGRID>
</CFFORM>
CFC:
<CFCOMPONENT OUTPUT="false">
<!--- Get Members --->
<CFFUNCTION NAME="getMembers" 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="">
<CFARGUMENT NAME="equitytype" TYPE="string" REQUIRED="no" DEFAULT="">
<CFARGUMENT NAME="state" TYPE="string" REQUIRED="no" DEFAULT="">
<CFARGUMENT NAME="initial" TYPE="string" REQUIRED="no" DEFAULT="">
<!--- Local variables --->
<CFSET var GetMemberDataForDirectory="">
<!--- Get data --->
<CFQUERY NAME="GetMemberDataForDirectory" DATASOURCE="ORCIntranet">
SELECT ARCODE,Lastname, Firstname, City, State, equitytype, MISC1, EMAIL, CASE WHEN EMAIL LIKE ('%@%') THEN 'EMAIL'+' '+Firstname ELSE 'NO EMAIL' END AS EMAILME, CONVERT(VARCHAR, (ARCODE * 37))+CONVERT(VARCHAR, (FAMNUM * 37)) AS ENCRYPTARCODE
FROM view_familyinfo_all_activemembers_withcontactinfo
WHERE FAMNUM <= '2' AND MISC1 <> '' and MISC1 BETWEEN '100' and '599'
<CFIF arguments.equitytype EQ 'equity'>AND MISC1 IN ('400','500')</CFIF>
<CFIF arguments.equitytype EQ 'social'>AND MISC1 IN ('100','109','110','150','159','160','300','360')</CFIF>
<CFIF arguments.equitytype EQ 'legacy'>AND MISC1 IN ('130','420','430','520','530')</CFIF>
<CFIF arguments.initial NEQ ''>AND LEFT(Lastname,1) = '#arguments.initial#'</CFIF>
<CFIF arguments.state NEQ ''>AND STATE = '#arguments.state#'</CFIF>
<CFIF ARGUMENTS.gridsortcolumn NEQ "" and ARGUMENTS.gridsortdir NEQ "">
ORDER BY #ARGUMENTS.gridsortcolumn# #ARGUMENTS.gridsortdir#
<CFELSE>
ORDER BY Lastname,ARCODE,FAMNUM
</cfif>
</CFQUERY>
<!--- And return it as a grid structure --->
<CFRETURN QueryConvertForGrid(GetMemberDataForDirectory,
ARGUMENTS.page,
ARGUMENTS.pageSize)>
</CFFUNCTION>
</CFCOMPONENT>
What I am trying to do is have conditionals inside the CFC once they edit the data and if the conditional breaks, return an error message.
Example: "Employee cannot be edited because they are inacative."
Thanks in advance.
Error invoking CFC /employeeService.cfc :Not Found
How does it not find it? It is in the same directory
CF8 BTW.
Do you have a data source named cfdocexamples (which is a default data source installed with CF 8)?
Error invoking CFC /employeeService.cfc :Not Found
Use:coldfusion 8
Error Executing Database Query.[Macromedia][SQLServer JDBC Driver][SQLServer]String or binary data would be truncated.
I have downloaded serveral sample files, and get this error with each. I am wondering if I have missed something is administrator? Any suggestions would be greatly appreciated.
Great simple working example !!! and code !
<cfform name="form01">
<cfgrid format="html" name="recordGrid" pagesize=11 stripeRows=true stripeRowColor="gray" width="700"
bind="cfc:hourService.getData({cfgridpage},{cfgridpagesize},
{cfgridsortcolumn},{cfgridsortdirection},#selectForm.whID#)"
delete="yes" selectmode="edit" deletebutton="Unassign"
onchange="cfc:hourService.editData({cfgridaction},{cfgridrow},{cfgridchanged})">
<cfgridcolumn name="whid" display=true header="WebHawk ID"/>
<cfgridcolumn name="ptrecordno" display=true header="PT Record No."/>
<cfgridcolumn name="employee" display=true header="Employee"/>
<cfgridcolumn name="projectTitle" display=true header="Project Title">
<cfgridcolumn name="sunday" display=true header="sunday"/>
</cfgrid>
</cfform>
<cfform name="selectForm" format="html" >
<table class="addTable">
<tr>
<td><div align="right">Employee:</div></td>
<td>
<div align="left">
<cfselect enabled="No" name="whID" multiple="no" query="getEmployees" display="empSel" value="whID">
</cfselect>
</div>
</td>
<td><div align="right"><A HREF="#"
onClick="cal.select(document.forms['selectForm'].whichWeek,'anchor1','MM/dd/yyyy'); return false;"
NAME="anchor1" ID="anchor1">SELECT</A></div></td>
<td><div align="left">
<cfinput type="text" name="whichWeek" value="Week Ends On" size="20">
</div></td>
</tr>
</table>
<cfinput type="button" name="assign" value="Assign" onClick="doSelect('hourService');" class="addButton"/>
</cfform>
function doSelect(whichCFC) {
/*
arguments are form name, cfc and method to send form values to, javascript function to handle result,
javascript function to handle error
*/
ColdFusion.Ajax.submitForm('selectForm', 'functions/' + whichCFC + '.cfc?method=getData', resultSelectHandler, selectErrorHandler);
}
<cffunction name="getData" access="remote" output="false" returntype="any">
<cfargument name="page" default="1">
<cfargument name="pageSize" default="11">
<cfargument name="gridsortcolumn" default="">
<cfargument name="gridsortdirection" default="">
<cfargument name="whichWeek" default="06/03/1971">
<cfargument name="whID" default="">
<cfset sunday = DateFormat(whichWeek,"yyyy-mm-dd")>
<cfset saturday = DateFormat(DateAdd("d",-1,whichWeek),"yyyy-mm-dd")>
<cfquery name="team" datasource="webhawk">
select employee_task.whid as whid, employee_task.ptrecordno as ptrecordno, employee.lastName as employee, projectTitle,
sun.hours as sunday
from employee_task
join employee on
employee.whid = employee_task.whid
join task on
task.ptRecordNo = employee_task.ptRecordNo
left join
(select hours, dateof, whid, ptRecordNo
from hours
where dateof = '#sunday#') as sun
on
employee_task.whid = sun.whid
and employee_task.ptrecordNo = sun.ptrecordno
where employee_task.whid = '#whID#'
<cfif gridsortcolumn neq "" or gridsortdirection neq "">
order by #gridsortcolumn# #gridsortdirection#
</cfif>
</cfquery>
<cfreturn QueryConvertForGrid(team, page, pageSize)>
</cffunction>
Checkboxes and boolean code for updating that worked
<cfset mybool = 1>
<cfset nobool = 0>
<cfif isStruct(gridrow) and isStruct(gridchanged)>
<cfif gridaction eq "U">
<cfset colname=structkeylist(gridchanged)>
<cfset value=structfind(gridchanged,#colname#)>
<cfquery name="qryTopics" datasource="er2000">
update LU_eLibraryTopics
<cfif isBoolean(value)>
<cfif value EQ "true">
set #colname# = #mybool#
<cfelse>
set #colname# = #nobool#
</cfif>
<cfelseif isNumeric(value)>
set #colname# = #value#
<cfelse>
set #colname# = '#value#'
</cfif>
where eLibraryTopicID = #gridrow.eLibraryTopicID#
</cfquery>
Iam unable to display the cfgrid data but the form values are inserting in to database but Iam unable to bind it. So I changed it to
<cfgrid format="html" name="employeeGrid" pagesize=11
stripeRows=true stripeRowColor="gray" bind="url:employeeService.cfc?method=getData&page={cfgridpage}&pagesize={cfgridpagesize}&gridsortcolumn={cfgridsortcolumn}&gridsortdirection={cfgridsortdirection}&returnFormat=json&cfdebug"
delete="yes" selectmode="edit"
onchange="cfc:employeeService.editData({cfgridaction},{cfgridrow},{cfgridchanged})">
Its showing the error message - Error parsing JSON response:
any help would be kindly appreciated.
Thanks,
Shariff
Iam able to display the records now but my new problem is Iam unable to update or delete the records it's showing an error message
_251[i] is undefined
[Break on this error] if(!_251[i].length){ in cfajax.js line 418 whenever I edit it and
_98._cf_grid is undefined
[Break on this error] var _99=_98._cf_grid.getDataSource();
whenever I delete it in cfgrid.js line 374
ANY HELP WOULD BE KINDLY APPRECIATED.
Thanks,
Shariff