Siraj Sayeed

Todd Sharp has posted a nice blog on his cfsilence on how to do it by modifying the bind parameter. Based on that and code from Gary Gilbert and implemented it myself. Here is the code:


<cfform name="form01">
<cfgrid
format="html"
name="employeeGrid"
pagesize=11
stripeRows=true
autoWidth=true
width="800"
pictureBar=true
bind="cfc:employeeService.getDataByParam(
{cfgridpage},
{cfgridpagesize},
{cfgridsortcolumn},
{cfgridsortdirection},
getFirstNameSearchParam(),
getLastNameSearchParam())"
delete="yes"
selectmode="edit"
onchange="cfc:employeeService.editData({cfgridaction},{cfgridrow},{cfgridchanged})"
>
<cfgridcolumn name="Emp_ID" display=true header="Employee ID" select="no" />
<cfgridcolumn name="FirstName" display=true header="First Name"/>
<cfgridcolumn name="LastName" display=true header="Last Name"/>
<cfgridcolumn name="email" display=true header="Email" />
</cfgrid>
</cfform>Look at the last two params in the cfc, I used JS functions to get the firstName and lastName from some text boxes and send those to the CFC. Here are the JS functions:


function getFirstNameSearchParam(){
return document.getElementById("firstName").value;
}

function doSearchEmployee() {
ColdFusion.Grid.refresh('employeeGrid', false);
}


The first function simply gets the text field value and returns it. The second function is triggered when users click the ‘Search’ button and refreshes the Grid to get the resultset again from the backend.

Next I tried to do the same with a different approcah, instead a calling the CFC, I called an url (though same cfc metheod) like the following:


bind="url:employeeService.cfc?method=getDataByParam&page={cfgridpage}&pagesize={cfgridpagesize}&
gridsortcolumn={cfgridsortcolumn}&gridsortdirection={cfgridsortdirection}&
firstname={insertForm:firstName}&lastname={insertForm:lastName}&returnFormat=json"


Notice, passing these four parameters is very important otherwise ColdFusion will through erros: {cfgridpage},{cfgridpagesize},{cfgridsortcolumn}, {cfgridsortdirection}. Next two parameters are “firstname={insertForm:firstName}” and “lastname={insertForm:lastName}” which will take the values of firstName and lastName fields of the form ‘inserForm’ and pass it through the url parameters. But only problem is, it binds the text fields with the grid and every time I tab out of the field, it refreshes the Grid even before I click the search button whcih I do not want. Probably there is a way to do it but I do not know. Also the final parameter of “returnFormat=json” is very important. Otherwise ColdFusion returns a WDDX packet. Interestingly there is no special code block in the function ‘getDataByParam’ to handle retrunFormat (probably ColdFusion takes care of it internally):

<cffunction name="getDataByParam" access="remote" output="false">
<cfargument name="page">
<cfargument name="pageSize">
<cfargument name="gridsortcolumn" default="">
<cfargument name="gridsortdirection" default="">
<cfargument name="firstname" default="">
<cfargument name="lastname" default="">

<cfquery name="team" datasource="cfdocexamples">
SELECT Emp_ID, FirstName, LastName, email
FROM Employees
WHERE
1=1
<cfif len(firstname)>
AND FirstName like '%#firstname#%'
</cfif>
<cfif len(lastname)>
AND LastName like '%#lastname#%'
</cfif>
<cfif gridsortcolumn neq "" or gridsortdirection neq "">
order by #gridsortcolumn# #gridsortdirection#
</cfif>
</cfquery>
<cfreturn QueryConvertForGrid(team, page, pageSize)>
</cffunction>

I wanted to create a new datasource in CF Admin but somehow I could not find the password associated to the user account in the database. Of course, I could create a new user in the DB, assign a new password and use that password in CF Admin. But I did not want to do that either. And retrieving password from the database using some third party tool seemed too risky. So I started looking into the neo-query.xml file where ColdFusion stores all the data source information. The file resides in “\cfusion-ear\cfusion-war\WEB-INF\cfusion\lib” folder. I went into another JRun instance, opened up the ‘neo-query.xml’ and copied the encrypted password string for the same database-user combination from that and pasted it into the instance I am working on. I had to restart the CF server after saving the file. And as expected, the datasource worked! Sweet!! Next time I will probably ask the DBA for the password instead of going through all the hassle…:-)