0 Replies Latest reply on Jun 23, 2009 1:12 PM by rmorgan

    CF-RIA grid paging/filter

    rmorgan Level 1

      Hi, got this code from the Coldfusion-RIA page for handling pagination and filtering, and I am having difficulties in getting it to work. The original code is here: http://www.coldfusion-ria.com/Blog/index.cfm/2009/1/13/Playing-with-cfgrid--Filter-showhid e-Columns-and-using-the-YUI-Buttons-library.

       

      I am able to get the grid to show with the filter dialog, but for the life of me I cannot get any data, can someone see something here that I am missing. I dont even get any error messages. This is being used on a MySQL db. TIA

       

      CFM PAGE:

      <body>
          <!-- Individual YUI CSS files --> 
          <link rel="stylesheet" type="text/css" href="http://yui.yahooapis.com/2.6.0/build/button/assets/skins/sam/button.css">
          <!-- Individual YUI CSS files --> 
          <link rel="stylesheet" type="text/css" href="http://yui.yahooapis.com/2.6.0/build/datatable/assets/skins/sam/datatable.css">
        
          <!-- Individual YUI JS files --> 
          <script type="text/javascript" src="http://yui.yahooapis.com/2.6.0/build/yahoo-dom-event/yahoo-dom-event.js"></script> 
          <script type="text/javascript" src="http://yui.yahooapis.com/2.6.0/build/element/element-beta-min.js"></script> 
          <script type="text/javascript" src="http://yui.yahooapis.com/2.6.0/build/button/button-min.js"></script>
         
          <style>
             
              .yui-skin-sam .yui-button  {
                  background:#ffffff;
                  color: #071b45;
                  border-color:#a3a3a3;   
              }
             
              .yui-skin-sam .yui-button button,
              .yui-skin-sam .yui-button a {
                 font-size: 75%;
                 color:#071b45;
                           
              }
             
              .yui-skin-sam .yui-button-hover {
                  background: #DDDDDD; 
                  color:#071b45;         
              }
             
              .yui-skin-sam .yui-radio-button-checked .first-child,
              .yui-skin-sam .yui-checkbox-button-checked .first-child {
                  border-color:#a3a3a3;   
              }
             
              .yui-skin-sam .yui-radio-button-checked,
              .yui-skin-sam .yui-checkbox-button-checked {
                  border-color:#a3a3a3;           
                  background:#DDDDDD;
                  color:#071b45;                   
              }
             
          </style> 

       

           <script>
              function showColumn(e)
              {   
                  //alert(this.get("value"));           
                  var showColId = this.get("value");       
                  showCol = cols.getColumnById(showColId);
                  if(showCol.hidden == false)
                  {
                      showCol.hidden = true;
                  }else
                  {
                      showCol.hidden = false;
                  }
                  grid.reconfigure(grid.getDataSource(),cols);
              }
             
              function getGrid()
              {
                  grid = ColdFusion.Grid.getGridObject('usersgrid');
                  cols = grid.getColumnModel();
                  for(var i=0; i<cols.getColumnCount();i++)
                  {
                      colid = cols.getColumnId(i);
                      column = cols.getColumnById(colid);
                      if(column.hidden == true && column.header != 'CFGRIDROWINDEX')
                      {
                          var showButton = new YAHOO.widget.Button({
                                  type: "checkbox",
                                  label: column.header,
                                  id: column.header,
                                  name: column.header,
                                  value: colid,
                                  container: "buttons",
                                  title: "Show Column",                           
                                  checked: false });
                                 
                          showButton.addListener("click",showColumn)
                      }
                  }
              }
             
             
          </script>
      <cfoutput>
      <cfform>
      <table border="1" width="90%">
      <tr>
      <td>
      Filter By: <cfselect id="filtercolumn" name="filtercolumn" bind="cfc:Users.getUserColumns()" display="ColumnName" value="ColumnName" bindOnLoad="true" />
      Filter Text: <cfinput type="text" id="filter" name="filter">
      <cfinput type="button" name="filterbutton" value="Filter" id="filterbutton"    onclick="ColdFusion.Grid.refresh('usersgrid',false)">
      </td>
      </tr>
      <tr>
      <td id="gridtd"  style="padding-top:10px;" >
      <div style="min-height:200px;">
      <cfgrid name="usersgrid" pagesize="10" format="html" width="100%" height="200" bind="cfc:Users.getUsers({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdire ction},{filtercolumn},{filter})">
      <cfgridcolumn name="FirstName" header="First Name">
      <cfgridcolumn name="LastName" header="Last Name">
      <cfgridcolumn name="UserName" header="User Name">
      <cfgridcolumn name="DisplayName" header="Display Name">
      <cfgridcolumn name="UserAccountingCode" header="GL Code (User)" display="false">
      <cfgridcolumn name="Phone" header="Phone No." display="false">
      </cfgrid>
      </div>
      </td>
      </tr>
      <tr>
      <td>
      Click to show the columns:<br>
      <div id="buttons" class="yui-skin-sam">
      </div>
      </td>       
      </tr>
      </table>
      </cfform>
      </cfoutput>

       

      <cfset AjaxOnLoad("getGrid")>
      </body>

       

      CFC PAGE

      <cfcomponent output="false">

       

         
          <cffunction name="getUsers" access="remote" returntype="any">
              <cfargument name="page" required="yes">
                <cfargument name="pageSize" required="yes">
              <cfargument name="gridsortcolumn" required="yes">
              <cfargument name="gridsortdirection" required="yes">
              <cfargument name="filtercolumn" required="no" default="">
              <cfargument name="filter" required="no" default="">
             
              <cfquery name="selUsers" datasource="broadcast">
                  SELECT FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone
                  FROM User
                  <cfif Arguments.filtercolumn NEQ "" AND Arguments.filter NEQ "">
                      WHERE #Arguments.filtercolumn# LIKE '#Arguments.filter#%'
                  </cfif>
                 
                  <cfif Arguments.gridsortcolumn NEQ "">
                      ORDER BY #Arguments.gridsortcolumn# #Arguments.gridsortdirection#
                  </cfif>
              </cfquery>
             
              <cfreturn queryconvertforgrid(selUsers,Arguments.page,Arguments.pagesize)/>
             
          </cffunction>
         
         
          <cffunction name="getUserColumns"  access="remote" returntype="any" >
             
              <cfquery name="selUsers" datasource="broadcast" result="selResult">
                  SELECT FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone
                  FROM Users
                  Limit 1
                 
                  <!--- SELECT FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone
                  FROM Users  --->
                    
                   <!--- SELECT TOP 1 FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone
                  FROM Users --->         
              </cfquery>
             
              <cfset selColumns = QueryNew("ColumnName,ColumnID")>
             
              <cfloop list="#selResult.columnList#" index="listIndex">
                  <cfset temp = QueryAddRow(selColumns,1)>
                  <cfset temp = QuerySetCell(selColumns,"ColumnName","#listIndex#")>
                  <cfset temp = QuerySetCell(selColumns,"ColumnID","#Val(selColumns.RecordCount-1)#")>
              </cfloop>
             
              <cfreturn selColumns>
             
          </cffunction>
         
      </cfcomponent>