3 Replies Latest reply: Mar 19, 2011 7:17 PM by cfwild RSS

    [Sorting not working] with CFGRID + Binding

    fabio.bozzo Community Member
    fabio.bozzo Nov 2, 2010 2:23 AM

      My Products.cfc is:

       

      <cffunction name="getProducts" access="remote" returntype="any" output="yes">
        <cfargument name="page">
        <cfargument name="pageSize">
        <cfargument name="gridsortcolumn">
        <cfargument name="gridsortdirection">
        <cfif ARGUMENTS.gridsortcolumn EQ ""><cfset ARGUMENTS.gridsortcolumn = "artcod"></cfif>
        <cfif ARGUMENTS.gridsortdirection EQ ""><cfset ARGUMENTS.gridsortdirection = "ASC"></cfif>
        <cfquery name="qryProducts" datasource="#this.DSN#" result="result">
            SELECT * FROM articoli
            ORDER BY 
                <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.gridsortcolumn#"> 
                <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.gridsortdirection#">
        </cfquery>
        <cfreturn QueryConvertForGrid(qryProducts, page, pageSize)>
    </cffunction>

       

      The query params work correctly, my query runs in the right way but.... the returning JSON is not sorted!

      Could this be an error in the queryconvert function?

       

      However my cfgrid doesn't work:

       

      <cfset args = StructNew()>
<cfset args.name = "ProductsGrid">
<cfset args.format = "html">
<cfset args.bindOnLoad = "true">
<cfset args.bind = "cfc:Products.getProducts({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})">
<cfset args.stripeRows = true>
<cfset args.selectColor = "##D9E8FB">
<cfset args.selectmode = "edit">
<cfset args.onchange = "cfc:Products.editProduct({cfgridaction},{cfgridrow},{cfgridchanged})">

       

      <cfform>
    <cfgrid attributeCollection="#args#" pagesize="20">
        <cfgridcolumn name="artid" display="false">
        <cfgridcolumn name="artcod" header="xxx">
        <cfgridcolumn name="artdes" header="Descrizione" width="250">
        <cfgridcolumn name="artdesrc" header="xxx">
        <cfgridcolumn name="artcodpr" header="xxx">
        <cfgridcolumn name="artcodbar" header="xxx">
        <cfgridcolumn name="artprzacq" header="xxx">
        <cfgridcolumn name="artprzvend" header="xxx">
    </cfgrid>
</cfform>

       

      What am I missing?

      • 2351 Views
      • Tags:
        • 1. Re: [Sorting not working] with CFGRID + Binding
          BKBK Community Member
          BKBK Mar 13, 2011 8:07 AM (in response to fabio.bozzo)

          My thinking is that it has nothing to do with cfgrid, and everything to do with cfqueryparam binding. Run the following code on a test page, and you will see what I mean:

           

          <cfset col = "someColumnName">
          <cfset sort = "desc">

           

          <cfquery name="qryProducts" datasource="yourDSN" result="result">
              SELECT * FROM articoli
              ORDER BY
                  <cfqueryparam cfsqltype="cf_sql_varchar" value="#col#">
                  <cfqueryparam cfsqltype="cf_sql_varchar" value="#sort#">
          </cfquery>

           

          <cfdump var = "#result#">

           

          The SQL that ColdFusion sends to the database server at compile-time is "SELECT * FROM articoli ORDER BY ? ?", plus instructions to use 'someColumnName' and 'desc' as the respective input parameters.

          • 2. Re: [Sorting not working] with CFGRID + Binding
            Adam Cameron. Community Member
            Adam Cameron. Mar 13, 2011 8:10 AM (in response to BKBK)

            You're right.

             

            CFQUERYPARAM is for parameters, not for building the SQL statement.  What the OP is trying to do is simply not valid (in any meaningful way).

             

            --

            Adam

            • 3. Re: [Sorting not working] with CFGRID + Binding
              cfwild Community Member
              cfwild Mar 19, 2011 7:17 PM (in response to fabio.bozzo)

              Hi,

               

              I've tried your version of the default cfset, but haven't had much luck in the past.  I typically use something like the following in the ORDER BY section of the SQL:

               

              <cfif ARGUMENTS.gridSortColumn NEQ "" AND ARGUMENTS.gridSortDir NEQ "">
                ORDER BY #ARGUMENTS.gridSortColumn# #ARGUMENTS.gridSortDir#
              <cfelse>

                  ORDER BY artcod ASC
              </cfif>

               

              Good Luck!

               

              <cfwild>