Link to home
Start Free TrialLog in
Avatar of msukow
msukowFlag for United States of America

asked on

Sorting columns in ColdFusion

I have the following code and all I need to sort each of the columns.

<cfparam name="FORM.roles" default="1">
<cfparam name="URL.VendorCode" default="1">
 
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>

<cfquery name="qNagata" datasource="ntk">
SELECT * FROM "All RMOs" ORDER BY VendorCode ASC
</cfquery>
<br><head>
      <link href="../nagata.css" rel="stylesheet" type="text/css">

</head>

<body>
<table border="1" cellpadding="2" cellspacing="2">
  <tr class="nagata8pt">
    <td align="left" valign="top"> <table width="50" border="0" cellspacing="0" cellpadding="1">
        <tr>
          <td class="nagata8ptBold">RMO #</td>
        </tr>
      </table>
      <br> <cfoutput> </cfoutput> </td>
    <td align="left" valign="top"> <table width="75" border="0" cellspacing="0" cellpadding="1">
        <tr>
          <td class="nagata8ptBold">RMO Date</td>
        </tr>
      </table></td>
    <td align="left" valign="top"><table width="50" border="0" cellspacing="0" cellpadding="1">
        <tr>
          <td class="nagata8ptBold">Part #</td>
        </tr>
      </table></td>
    <td align="left" valign="top"> <table width="125" border="0" cellspacing="0" cellpadding="1">
        <tr>
          <td class="nagata8ptBold">NTK RMO to DN Date</td>
        </tr>
      </table></td>
    <td align="left" valign="top"> <table width="200" border="0" cellspacing="0" cellpadding="1">
        <tr>
          <td class="nagata8ptBold">DN Reply </td>
        </tr>
      </table></td>
    <td align="left" valign="top"> <table width="100" border="0" cellspacing="0" cellpadding="1">
        <tr>
          <td class="nagata8ptBold">DN Reply Date </td>
        </tr>
      </table></td>
    <td align="left" valign="top"> <table width="100" border="0" cellspacing="0" cellpadding="1">
        <tr>
          <td class="nagata8ptBold">NTK Part Rcv Date </td>
        </tr>
      </table></td>
  </tr>
  <cfoutput query="qNagata">
    <tr class="nagata8pt">
      <td align="left" valign="top">
        <a href="rmo_details.cfm?RMO_Number=#qNagata.RMO_Number#">
        #qNagata.RMO_Number# </a></td>
      <!--- <td ><a href="rmo_details.cfm?RMO_Number=#qNagata.RMO_Number#"></td> --->
      <!--- <td >#qNagata.RMO_Number#</td> --->
      <td align="left" valign="top"> <table width="50" border="0" cellspacing="0" cellpadding="1">
          <tr>
            <td class="nagata8pt">#LSDateFormat(qNagata.RMO_Date,'YY/MM/DD')#</td>
          </tr>
        </table></td>
      <td align="left" valign="top"> <table width="50" border="0" cellspacing="0" cellpadding="1">
          <tr>
            <td class="nagata8pt">#qNagata.Part_Number#</td>
          </tr>
        </table></td>
      <td align="left" valign="top"> <table width="125" border="0" cellspacing="0" cellpadding="1">
          <tr>
            <td class="nagata8pt">#qNagata.NTK_RMO_to_DN_Date#</td>
          </tr>
        </table></td>
      <td align="left" valign="top"> <table width="200" border="0" cellspacing="0" cellpadding="1">
          <tr>
            <td class="nagata8pt">#qNagata.DN_Reply#</td>
          </tr>
        </table></td>
      <td align="left" valign="top"> <table width="100" border="0" cellspacing="0" cellpadding="1">
          <tr>
            <td class="nagata8pt">#qNagata.DN_Reply_Date#</td>
          </tr>
        </table></td>
      <td align="left" valign="top"> <table width="100" border="0" cellspacing="0" cellpadding="1">
          <tr>
            <td class="nagata8pt">#qNagata.NTK_Part_Rcv_Date#</td>
          </tr>
        </table></td>
    </tr>
  </cfoutput>
</table>
</body>
Avatar of anandkp
anandkp
Flag of India image
run this code ... make necessary changes for the column names -as per ur DB. I have used ur table headers as column names ...



<CFPARAM NAME="FORM.roles" DEFAULT="1">
<CFPARAM NAME="URL.VendorCode" DEFAULT="1">

<CFPARAM NAME="sortid" DEFAULT="VendorCode">
<CFPARAM NAME="sortorder" DEFAULT="Asc">

<CFSET CURRENTPAGE=GETFILEFROMPATH(GETTEMPLATEPATH())>

<CFQUERY NAME="qNagata" DATASOURCE="ntk">
      SELECT * FROM "All RMOs"
      ORDER BY #sortid# #sortorder#
</CFQUERY>
<HEAD>
<LINK HREF="../nagata.css" REL="stylesheet" TYPE="text/css">
</HEAD>

<BODY>
<TABLE BORDER="1" CELLPADDING="2" CELLSPACING="2">
      <TR CLASS="nagata8pt">
            <TD ALIGN="left" VALIGN="top">
            <TABLE WIDTH="50" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                  <TR>
                        <TD CLASS="nagata8ptBold"><A HREF="ThisPage.cfm?sortid='RMO'&sortorder=<Cfif sortid EQ 'RMO' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">RMO</A>#</TD>
                  </TR>
            </TABLE>            
            </TD>
            <TD ALIGN="left" VALIGN="top">
            <TABLE WIDTH="75" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                  <TR>
                        <TD CLASS="nagata8ptBold"><A HREF="ThisPage.cfm?sortid='RMO Date'&sortorder=<Cfif sortid EQ 'RMO Date' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">RMO Date</A></TD>
                  </TR>
            </TABLE></TD>
            <TD ALIGN="left" VALIGN="top">
            <TABLE WIDTH="50" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                  <TR>
                        <TD CLASS="nagata8ptBold"><A HREF="ThisPage.cfm?sortid='Part'&sortorder=<Cfif sortid EQ 'Part' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">Part #</A></TD>
                  </TR>
            </TABLE></TD>
            <TD ALIGN="left" VALIGN="top">
            <TABLE WIDTH="125" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                  <TR>
                        <TD CLASS="nagata8ptBold"><A HREF="ThisPage.cfm?sortid='NTK RMO'&sortorder=<Cfif sortid EQ 'NTK RMO' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">NTK RMO to DN Date</A></TD>
                  </TR>
            </TABLE></TD>
            <TD ALIGN="left" VALIGN="top">
            <TABLE WIDTH="200" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                  <TR>
                        <TD CLASS="nagata8ptBold"><A HREF="ThisPage.cfm?sortid='DN Reply'&sortorder=<Cfif sortid EQ 'DN Reply' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">DN Reply</A></TD>
                  </TR>
            </TABLE></TD>
            <TD ALIGN="left" VALIGN="top">
            <TABLE WIDTH="100" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                  <TR>
                        <TD CLASS="nagata8ptBold"><A HREF="ThisPage.cfm?sortid='DN Reply Date'&sortorder=<Cfif sortid EQ 'DN Reply Date' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">DN Reply Date</A></TD>
                  </TR>
            </TABLE></TD>
            <TD ALIGN="left" VALIGN="top">
            <TABLE WIDTH="100" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                  <TR>
                        <TD CLASS="nagata8ptBold"><A HREF="ThisPage.cfm?sortid='NTK Part Rcv Date'&sortorder=<Cfif sortid EQ 'NTK Part Rcv Date' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">NTK Part Rcv Date</A></TD>
                  </TR>
            </TABLE></TD>
      </TR>
      <CFOUTPUT QUERY="qNagata">
            <TR CLASS="nagata8pt">
                  <TD ALIGN="left" VALIGN="top"><A HREF="rmo_details.cfm?RMO_Number=#qNagata.RMO_Number#">#qNagata.RMO_Number#</A></TD><!---
                  <TD ><A href="rmo_details.cfm?RMO_Number=#qNagata.RMO_Number#"></TD>--->
     <!---
                  <TD >#qNagata.RMO_Number#</TD>--->
                  <TD ALIGN="left" VALIGN="top">
                  <TABLE WIDTH="50" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                        <TR>
                              <TD CLASS="nagata8pt">#LSDateFormat(qNagata.RMO_Date,'YY/MM/DD')#</TD>
                        </TR>
                  </TABLE></TD>
                  <TD ALIGN="left" VALIGN="top">
                  <TABLE WIDTH="50" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                        <TR>
                              <TD CLASS="nagata8pt">#qNagata.Part_Number#</TD>
                        </TR>
                  </TABLE></TD>
                  <TD ALIGN="left" VALIGN="top">
                  <TABLE WIDTH="125" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                        <TR>
                              <TD CLASS="nagata8pt">#qNagata.NTK_RMO_to_DN_Date#</TD>
                        </TR>
                  </TABLE></TD>
                  <TD ALIGN="left" VALIGN="top">
                  <TABLE WIDTH="200" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                        <TR>
                              <TD CLASS="nagata8pt">#qNagata.DN_Reply#</TD>
                        </TR>
                  </TABLE></TD>
                  <TD ALIGN="left" VALIGN="top">
                  <TABLE WIDTH="100" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                        <TR>
                              <TD CLASS="nagata8pt">#qNagata.DN_Reply_Date#</TD>
                        </TR>
                  </TABLE></TD>
                  <TD ALIGN="left" VALIGN="top">
                  <TABLE WIDTH="100" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                        <TR>
                              <TD CLASS="nagata8pt">#qNagata.NTK_Part_Rcv_Date#</TD>
                        </TR>
                  </TABLE></TD>
            </TR>
      </CFOUTPUT>
</TABLE>
</BODY>

K'Rgds
Anand
Avatar of msukow
msukow
Flag of United States of America image

ASKER

I tried that and I get the following message:

Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '''RMO'''.  
 
The error occurred in C:\CFusionMX\wwwroot\Nagata\site\RMO\rmoSort.cfm: line 12
 
10 :       SELECT *
11 :       FROM "All RMOs"
12 :       ORDER BY #sortid# #sortorder#
13 : </CFQUERY>
14 :
Avatar of msukow
msukow
Flag of United States of America image

ASKER

Additional points added
Avatar of anandkp
anandkp
Flag of India image
Can u print - what is the output of the above query on ur browser ... so we know where what went wrong !

let me know ...

PS : - just try removing the single quotes from this :
<A HREF="ThisPage.cfm?sortid=NTK Part Rcv Date&sortorder=<Cfif sortid EQ 'NTK Part Rcv Date' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">NTK Part Rcv Date</A>

if it works  - remove it from all the "A hrefs" ...
i think that cld be the reason & it shld work !
... my apologies for the mistype in my earlier comment.
Avatar of msukow
msukow
Flag of United States of America image

ASKER

I'm still getting an error - The next section is a copy/paste from the first column:

<TD ALIGN="left" VALIGN="top">
          <TABLE WIDTH="50" BORDER="0" CELLSPACING="0" CELLPADDING="1">
               <TR>
                    <TD CLASS="nagata8ptBold"><A HREF="rmosort.cfm?sortid=NTK &sortorder=<Cfif sortid EQ 'RMO' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">RMO</A>#</TD>
               </TR>
          </TABLE>          
          </TD>

And the error message on the screen is now:

Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.  
 
The error occurred in C:\CFusionMX\wwwroot\Nagata\site\RMO\rmoSort.cfm: line 12
 
10 :       SELECT *
11 :       FROM "All RMOs"
12 :       ORDER BY #sortid# #sortorder#
13 : </CFQUERY>
14 :

 I tried using your code: <A HREF="ThisPage.cfm?
and "thisPage.cfm" was not able to be located.

Mike
Avatar of anandkp
anandkp
Flag of India image
"thispage.cfm" - refers to ur page name ... replace it will the name of ur page !

run this code & see what it prints on ur browser ... copy it & paste it here - so i can have a look at it.

<cfoutput>
SELECT *
FROM "All RMOs"
ORDER BY #sortid# #sortorder#
</cfoutput>

K'Rgds
Anand
Avatar of msukow
msukow
Flag of United States of America image

ASKER

SELECT * FROM "All RMOs" ORDER BY VendorCode Asc

and then the table as normal

Avatar of anandkp
anandkp
Flag of India image
OK so the field names & the sorting order are passed properly & shld work fine

did u change the " thispage.cfm " - to the name of the file that has this code ?
Avatar of msukow
msukow
Flag of United States of America image

ASKER

Yes, I changed " thispage.cfm " to "rmosort.cfm" which is the name of the file that has this code.
Avatar of anandkp
anandkp
Flag of India image
so now all the "ahref" have the link as

<TD CLASS="nagata8ptBold"><A HREF="rmosort.cfm?sortid=NTK&sortorder=<Cfif sortid EQ 'RMO' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">RMO</A>#</TD>

PS : note there shld be no spaces in ur links

if ur code looks like above - then u shld be able to get it working ... i dont understand y ur getting a error ... can i have a look at ur entire code - pls !
Avatar of msukow
msukow
Flag of United States of America image

ASKER

Here's the code:

<CFPARAM NAME="FORM.roles" DEFAULT="1">
<CFPARAM NAME="URL.VendorCode" DEFAULT="1">

<CFPARAM NAME="sortid" DEFAULT="VendorCode">
<CFPARAM NAME="sortorder" DEFAULT="Asc">

<CFSET CURRENTPAGE=GETFILEFROMPATH(GETTEMPLATEPATH())>

<CFQUERY NAME="qNagata" DATASOURCE="ntk">
      SELECT *
      FROM "All RMOs"
      ORDER BY #sortid# #sortorder#
</CFQUERY>

<cfoutput>
SELECT *
FROM "All RMOs"
ORDER BY #sortid# #sortorder#
</cfoutput>

<HEAD>
<LINK HREF="../nagata.css" REL="stylesheet" TYPE="text/css">
</HEAD>

<BODY>
<TABLE BORDER="1" CELLPADDING="2" CELLSPACING="2">
     <TR CLASS="nagata8pt">
          <TD ALIGN="left" VALIGN="top">
          <TABLE WIDTH="50" BORDER="0" CELLSPACING="0" CELLPADDING="1">
               <TR>
                    <TD CLASS="nagata8ptBold"><A HREF="rmosort.cfm?sortid=NTK&sortorder=<Cfif sortid EQ 'RMO' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">RMO</A>#</TD>
                    </TR>
          </TABLE>          
          </TD>
          <TD ALIGN="left" VALIGN="top">
          <TABLE WIDTH="75" BORDER="0" CELLSPACING="0" CELLPADDING="1">
               <TR>
                    <TD CLASS="nagata8ptBold"><A HREF="rmosort.cfm?sortid=RMO_Date&sortorder=<Cfif sortid EQ 'RMO_Date' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">RMO Date</A></TD>
               </TR>
          </TABLE></TD>
          <TD ALIGN="left" VALIGN="top">
          <TABLE WIDTH="50" BORDER="0" CELLSPACING="0" CELLPADDING="1">
               <TR>
                    <TD CLASS="nagata8ptBold"><A HREF="rmosort.cfm?sortid=Part_Number&sortorder=<Cfif sortid EQ 'Part_Number' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">Part #</A></TD>
               </TR>
          </TABLE></TD>
          <TD ALIGN="left" VALIGN="top">
          <TABLE WIDTH="125" BORDER="0" CELLSPACING="0" CELLPADDING="1">
               <TR>
                    <TD CLASS="nagata8ptBold"><A HREF="rmosort.cfm?sortid=NTK_RMO&sortorder=<Cfif sortid EQ 'NTK_RMO' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">NTK RMO to DN Date</A></TD>
               </TR>
          </TABLE></TD>
          <TD ALIGN="left" VALIGN="top">
          <TABLE WIDTH="200" BORDER="0" CELLSPACING="0" CELLPADDING="1">
               <TR>
                    <TD CLASS="nagata8ptBold"><A HREF="rmosort.cfm?sortid=DN_Reply&sortorder=<Cfif sortid EQ 'DN_Reply' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">DN Reply</A></TD>
               </TR>
          </TABLE></TD>
          <TD ALIGN="left" VALIGN="top">
          <TABLE WIDTH="100" BORDER="0" CELLSPACING="0" CELLPADDING="1">
               <TR>
                    <TD CLASS="nagata8ptBold"><A HREF="rmosort.cfm?sortid=DN_Reply_Date&sortorder=<Cfif sortid EQ 'DN_Reply_Date' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">DN Reply Date</A></TD>
               </TR>
          </TABLE></TD>
          <TD ALIGN="left" VALIGN="top">
          <TABLE WIDTH="100" BORDER="0" CELLSPACING="0" CELLPADDING="1">
               <TR>
                    <TD CLASS="nagata8ptBold"><A HREF="rmosort.cfm?sortid=NTK_Part_Rcv_Date&sortorder=<Cfif sortid EQ 'NTK_Part_Rcv_Date' And sortorder EQ 'Asc'>Desc<CFELSE>Asc</CFIF>">NTK Part Rcv Date</A></TD>
                     </TR>
          </TABLE></TD>
     </TR>
     <CFOUTPUT QUERY="qNagata">
          <TR CLASS="nagata8pt">
               <TD ALIGN="left" VALIGN="top"><A HREF="rmo_details.cfm?RMO_Number=#qNagata.RMO_Number#">#qNagata.RMO_Number#</A></TD><!---
               <TD ><A href="rmo_details.cfm?RMO_Number=#qNagata.RMO_Number#"></TD>--->
     <!---
               <TD >#qNagata.RMO_Number#</TD>--->
               <TD ALIGN="left" VALIGN="top">
               <TABLE WIDTH="50" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                    <TR>Fthis
                         <TD CLASS="nagata8pt">#LSDateFormat(qNagata.RMO_Date,'YY/MM/DD')#</TD>
                    </TR>
               </TABLE></TD>
               <TD ALIGN="left" VALIGN="top">
               <TABLE WIDTH="50" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                    <TR>
                         <TD CLASS="nagata8pt">#qNagata.Part_Number#</TD>
                    </TR>
               </TABLE></TD>
               <TD ALIGN="left" VALIGN="top">
               <TABLE WIDTH="125" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                    <TR>
                         <TD CLASS="nagata8pt">#qNagata.NTK_RMO_to_DN_Date#</TD>
                    </TR>
               </TABLE></TD>
               <TD ALIGN="left" VALIGN="top">
               <TABLE WIDTH="200" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                    <TR>
                         <TD CLASS="nagata8pt">#qNagata.DN_Reply#</TD>
                    </TR>
               </TABLE></TD>
               <TD ALIGN="left" VALIGN="top">
               <TABLE WIDTH="100" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                    <TR>
                         <TD CLASS="nagata8pt">#qNagata.DN_Reply_Date#</TD>
                    </TR>
               </TABLE></TD>
               <TD ALIGN="left" VALIGN="top">
               <TABLE WIDTH="100" BORDER="0" CELLSPACING="0" CELLPADDING="1">
                    <TR>
                         <TD CLASS="nagata8pt">#qNagata.NTK_Part_Rcv_Date#</TD>
                    </TR>
               </TABLE></TD>
          </TR>
     </CFOUTPUT>
</TABLE>
</BODY>
Avatar of anandkp
anandkp
Flag of India image
the code looks perfect to me !

what happens when u click on the link - DN Reply ?
Avatar of msukow
msukow
Flag of United States of America image

ASKER

Seems to be ok for some of the columns - I will have to check the columns that are not working to find out why.

Non-working columns:
RMO#
NTK RMO to DN Date
ASKER CERTIFIED SOLUTION
Avatar of anandkp
anandkp
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial