How can I add an up/down button to rearrange the list in a form select

I have a form that has two selects.  I have added a javascript that will allow me to move up or down the field I select.  The problem I'm having is that I don't know how to save the list in its new order.  When I hit the save button, it saves it back in the old order.  The select I need to be able to reorder is the rptFlds.  I have attached the code of my form.  Any help would be great since I have a deadline coming up to get this done.  Thank you!!!
<script>
function listbox_move(listID, direction) {   
  
    var listbox = document.getElementById(listID);   
    var selIndex = listbox.selectedIndex;   
  
    if(-1 == selIndex) {   
        alert("Please select an option to move.");   
        return;   
    }   
  
    var increment = -1;   
    if(direction == 'up')   
        increment = -1;   
    else  
        increment = 1;   
  
    if((selIndex + increment) < 0 ||   
        (selIndex + increment) > (listbox.options.length-1)) {   
        return;   
    }   
  
    var selValue = listbox.options[selIndex].value;   
    var selText = listbox.options[selIndex].text;   
    listbox.options[selIndex].value = listbox.options[selIndex + increment].value   
    listbox.options[selIndex].text = listbox.options[selIndex + increment].text   
  
    listbox.options[selIndex + increment].value = selValue;   
    listbox.options[selIndex + increment].text = selText;   
  
    listbox.selectedIndex = selIndex + increment;   
}  
</script>
 
<cfset #pageTitle# = "Administration">
<cfset #curPage# = 77>
<cfset #custContent# = "AP1">
 
<cfset #LbxName# = RemoveChars(#session.lbxformattable#,1,8)>
<cfif #Session.lbxtype# is "Wholesale">
<cfset #RptTableName# = "lbx_rpt_#LbxName#">
<cfelse>
<cfset #RptTableName# = "rlb_rpt_#LbxName#">
</cfif>
 
<cfquery name = "checkforrpttable" datasource="#Application.dsname#">
  SELECT * from sysobjects where name = <cfqueryparam value="#RptTableName#">
</cfquery>
 
<cfif #checkforrpttable.Recordcount# is 0>
  <cfquery name = "createrpttable" datasource = "#Application.dsname#">
     SELECT * INTO #RptTableName# FROM #SESSION.LBXFORMATTABLE#
  </cfquery>
<cfelse>
 
      <cfquery name = "getfmtflds" datasource = "#Application.dsname#">
         SELECT * FROM #SESSION.LBXFORMATTABLE# where layout = 'AP'
      </cfquery>       
        
      <cfloop query="getfmtflds">
          <cfquery name = "updaterpttable" datasource= "#Application.dsname#">
              UPDATE #RptTableName#  SET fld_head1 = '#getfmtflds.fld_head1#', fld_head2 = '#getfmtflds.fld_head2#', fld_prompt = '#getfmtflds.fld_prompt#'
                   WHERE (layout = <cfqueryparam value="AP">) AND
                         (fld_order = #getfmtflds.fld_order#)
          </cfquery>
      </cfloop>
        
  <cfquery name = "comparefmt" datasource = "#Application.dsname#">
      SELECT * FROM #Session.LbxFormatTable# T1 
	        WHERE (T1.layout = <cfqueryparam value="AP">) AND 
			NOT EXISTS(SELECT * FROM #RptTableName# T2 where (T1.fld_order = T2.fld_order) and layout= <cfqueryparam value="AP">)
  </cfquery>
  <cfif #comparefmt.RecordCount# gt 0>
     <cfquery name = "insertintoReportTable" datasource = "#Application.dsname#">
	     INSERT INTO #RptTableName#(layout,fld_order,fld_name,fld_otype,fld_len,fld_dec,fld_prompt,fld_head1,fld_head2,can_modify,can_search,at_info,at_caption,at_trans,at_chkbody,at_invbody,at_cpnbody,at_support,at_report,rollup,total)
    	   (SELECT * FROM 
			    #Session.LbxFormatTable# T1 
				WHERE (T1.layout = <cfqueryparam value="AP">) AND 
				NOT EXISTS(SELECT * FROM #RptTableName# T2 where (T1.fld_order = T2.fld_order) and layout= <cfqueryparam value="AP">))
	 </cfquery>
  <cfelse>
	  <cfquery name = "comparerpt" datasource = "#Application.dsname#">
    	  SELECT * FROM #RptTableName# T1 
	    	    WHERE (T1.layout = <cfqueryparam value="AP">) AND 
				NOT EXISTS(SELECT * FROM #Session.LbxFormatTable# T2 where (T1.fld_order = T2.fld_order)and layout= <cfqueryparam value="AP">)
	  </cfquery>
		<cfset #Session.report# = 1>
       
  	<cfif #comparerpt.RecordCount# gt 0>
	
    	<cfloop query = "comparerpt">
		<cfquery name="Del" datasource="#Application.dsname#">
	   		DELETE  FROM #RPTTableName# where (fld_order = #comparerpt.fld_order#) and (layout = <cfqueryparam value="AP">)
		</cfquery>
		</cfloop>
	</cfif>
  </cfif>
  
  <cfquery name ="checkforcan_search" datasource="#Application.dsname#">
     SELECT T2.fld_name,T2.layout,T2.fld_order,T2.can_search FROM #RptTableName# T1,#Session.lbxFormatTable# T2 WHERE 
	    (T1.layout = T2.layout) And (T1.layout = <cfqueryparam value="AP">) AND
		(T2.can_search <> T1.can_search) AND (T1.fld_name = T2.fld_name) 
		ORDER BY T1.fld_order
  </cfquery>
  
  <cfif #checkforcan_search.RecordCount# gt 0>
    <cfloop query = "checkforcan_search">
	
   <cfquery name = "updaterpttable" datasource= "#Application.dsname#">
            UPDATE #RptTableName#  SET can_search = #checkforcan_search.can_search#
                 WHERE (layout = <cfqueryparam value="AP">) AND
     		           (fld_name = '#checkforcan_search.fld_name#')
	</cfquery>
	</cfloop>
  </cfif>
</cfif>
 
 
<cfif #ParameterExists(form.add)# AND #Len(Trim(form.add))# gt 0>
<cfif isdefined("form.Flds")>
  <cfset #form.reportFields#="#form.reportFields#,#form.flds#">
</cfif>
 </cfif>
<cfif #ParameterExists(form.addAll)# AND #Len(Trim(form.addAll))# gt 0>
  <cfquery name="getAll" datasource="#Application.dsname#">
	SELECT fld_name, layout, can_search FROM #RptTableName# WHERE layout= <cfqueryparam value="AP"> AND can_search= <cfqueryparam value="1">
  </cfquery>
  <cfset #lcv# = 1>
  <cfloop query="getAll">
    <cfquery name="updateAll" datasource="#Application.dsname#">
	  UPDATE #RptTableName# SET at_report=#lcv# WHERE layout= <cfqueryparam value="AP"> AND fld_name='#getAll.fld_name#'
    </cfquery>
    <cfset #lcv# = #lcv# + 1>
  </cfloop>
</cfif>
 
<cfif #ParameterExists(form.remove)# AND #Len(Trim(form.remove))# gt 0>
<cfif isdefined("form.rptFlds")>
  <cfset #tmpList# = #form.reportFields#>
  <cfset #tmpFindVal# = #ListFindNoCase(tmpList, form.rptFlds, ",")#>
  <cfif #tmpFindVal# gt 0>
    <cfset #form.reportFields# = #ListDeleteAt(tmpList, tmpFindVal, ",")#>
    <cfquery name="remove" datasource="#Application.dsname#">
	  UPDATE #RptTableName# SET at_report=0 WHERE layout= <cfqueryparam value="AP">
    </cfquery>
	 </cfif> 		
  </cfif>
</cfif>
 
<cfif #ParameterExists(form.removeAll)# AND #Len(Trim(form.removeAll))# gt 0>
  <cfset #form.reportFields# = "">
  <cfquery name="removeAll" datasource="#Application.dsname#">
	UPDATE #RptTableName# SET at_report=0 WHERE layout= <cfqueryparam value="AP">
  </cfquery>
</cfif>
 
<cfif (#ParameterExists(form.finished)# AND #Len(Trim(form.finished))# gt 0) OR
      (#ParameterExists(form.add)# AND #Len(Trim(form.add))# gt 0) OR
      (#ParameterExists(form.remove)# AND #Len(Trim(form.remove))# gt 0) OR
      (#ParameterExists(form.removeAll)# AND #Len(Trim(form.removeAll))# gt 0)>
 
  <cfset #lcv# = 0>
  <cfloop index="fldName" list="#form.reportFields#">
    <cfoutput>
      <cfset #lcv# = #lcv# + 1>
      <cfquery name="addRptFields" datasource="#application.dsname#">
			UPDATE #RptTableName# SET at_report=#lcv# WHERE fld_name= <cfqueryparam value="#fldName#"> AND layout= <cfqueryparam value="AP">
      </cfquery>
    </cfoutput>
  </cfloop>
</cfif>
 
<cfif #ParameterExists(form.finished)# AND #Len(Trim(form.finished))# gt 0>
  <cfif #ParameterExists(form.reportFields)# AND #Len(Trim(form.reportFields))# gt 0>
    <cfset #tmpUrl# = "custRpt.cfm?errMsg=Custom%20report%20updated">
  <cfelse>
    <cfset #tmpUrl# = "custRpt.cfm?errMsg=Unable%20to%20update%20custom%20report">
  </cfif>
  <cflocation url="#tmpUrl#" addtoken="no">
<cfelse>
  <cfinclude template="header-main.cfm">
 
  <cfquery name="getMaxFields" datasource="#Application.dsname#">
	SELECT fld_name, layout, can_search FROM #RptTableName# WHERE layout= <cfqueryparam value="AP"> AND can_search= <cfqueryparam value="1">
  </cfquery>
 
  <cfquery name="getFields" datasource="#application.dsname#">
	SELECT * FROM #RptTableName# WHERE layout= <cfqueryparam value="AP"> and can_search= <cfqueryparam value="1"> AND at_report= <cfqueryparam value="0"> ORDER BY fld_name
  </cfquery>
 
  <cfquery name="getRptFields" datasource="#application.dsname#">
	SELECT * FROM #RptTableName# WHERE layout= <cfqueryparam value="AP"> and at_report> <cfqueryparam value="0"> ORDER BY at_report
  </cfquery>
 
  <p><br></p>
  <cfif #ParameterExists(URL.errMsg)# AND #Len(Trim(URL.errMsg))# gt 0>
    <p><div align="center"><font color="#Application.emphtext#"><b><cfoutput>#URL.errMsg#</cfoutput></b></font></div></p>
  </cfif>
  <p><br></p>
 
  <CFSET tmpReportFields = #ValueList(getRptFields.fld_name)#>
  <table>
  	<tr>
    	<td>
        <div> 
        <b class="round"> 
        <b class="round1"><b></b></b>
        <b class="round2"><b></b></b>
        <b class="round3"></b>
        <b class="round4"></b>
        <b class="round5"></b> 
        </b> <div class="round_content">	  
  <table>
    <form method="post" action="custRpt.cfm">
      <input type="hidden" name="reportFields" value="<cfoutput>#tmpReportFields#</cfoutput>">
      <tr>
        <td><span style="color: #FFFFFF"><b>Available Fields:</b></span></td>
        <td>&nbsp;</td>
        <td><span style="color: #FFFFFF"><b>Selected Fields:</b></span></td>
      </tr>
      <tr>
        <td valign="top">
          <select name="flds" size="<cfoutput>#getMaxFields.RecordCount#</cfoutput>">
            <cfif #getFields.RecordCount# is 0>
              <option value="">-- All Selected --</option>
            <cfelse>
              <cfoutput query="getFields">
                <option value="#getFields.fld_name#">#getFields.fld_head1#&nbsp;#getFields.fld_head2#</option>
              </cfoutput>
            </cfif>
          </select>        </td>
        <td valign="top">
          <p><input type="submit" name="add" value="     >     "></p>
          <p><input type="submit" name="addAll" value="    >>    "></p>
          <p><input type="submit" name="remove" value="     <     "></p>
          <p><input type="submit" name="removeall" value="    <<    "></p>        
          <p><input type="button" name="up" onclick="listbox_move('rptFlds', 'up'); " value="    Up    " /></p>
          <p><input type="button" name="down" onclick="listbox_move('rptFlds', 'down'); " value="  Down  " /></p>
          <p><input type="submit" name="finished" value="  Save   "></p>        
        </td>
        <td valign="top">
          <select id="rptFlds" name="rptFlds" size="<cfoutput>#getMaxFields.RecordCount#</cfoutput>">
            <cfif #getRptFields.RecordCount# is 0>
              <option value="">-- None Selected --</option>
            <cfelse>
              <cfoutput query="getRptFields">
                <option value="#getRptFields.fld_name#">#getRptFields.fld_head1#&nbsp;#getRptFields.fld_head2#</option>
              </cfoutput>
            </cfif>
          </select>
        </td>
      </tr>
    </form>
  </table>
            </div>
            <b class="round"> 
            <b class="round5"></b>
            <b class="round4"></b>
            <b class="round3"></b>
            <b class="round2"><b></b></b>
            <b class="round1"><b></b></b> 
            </b> 
            </div>         
  		</td>
	</tr>
</table>
 
  <cfinclude template="footer.cfm">
</cfif>

Open in new window

CashmgmtAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

erikTsomikSystem Architect, CF programmer Commented:
in your query you need also Update the ordering column and in the main query then order by that column, that will keep your drop down ordered
0
CashmgmtAuthor Commented:
erikTsomik, thanks for the quick response.  Is there any way you can give me an example.  I'm new to coldfusion and I'm a little lost at this point.   thanks.
0
erikTsomikSystem Architect, CF programmer Commented:
OK.
it all of your queries i do not see order by clause

SELECT * from sysobjects where name = <cfqueryparam value="#RptTableName#">

Order by , you need to order by column so you will see the effect of you movement.

I have done the similar thing just by using coldFusion. Where I was selecting items from i drop down and moved them ito the other . Without using javascript
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

pimpsuitCommented:
Your getRptFields query has a static ORDER BY clause. You need to plug in the incoming value of form.rptFlds. First, set up a default value in case the user is visiting the page for the first time:

<cfparam name="form.rptFlds" default="at_report">

Then change the static ORDER BY clause:

SELECT * FROM #RptTableName# WHERE layout= <cfqueryparam value="AP"> and at_report> <cfqueryparam value="0"> ORDER BY <cfqueryparam value="#form.rptFlds#">

Also, you don't really need cfqueryparams for static values like "AP" and "0" above.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CashmgmtAuthor Commented:
Hey pimpsuit, I get this error when I tried to change the query.

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
The error occurred on line 195.

0
pimpsuitCommented:
Try getting rid of the cfqueryparam tag just to see if you can get it working:

SELECT * FROM #RptTableName# WHERE layout= <cfqueryparam value="AP"> and at_report> <cfqueryparam value="0"> ORDER BY "#form.rptFlds#"


0
erikTsomikSystem Architect, CF programmer Commented:
change to this
SELECT * FROM #RptTableName# WHERE layout= <cfqueryparam value="AP"> and at_report> <cfqueryparam value="0"> ORDER BY #form.rptFlds#
0
CashmgmtAuthor Commented:
None of these have worked.  Very frustrating when you're a newbie(me) and can't figure it out even with help from you guys! right now my 2 selects work with moving fields from the left to the right.  The javascript I added to the top of the code works with the up/down buttong i added where it will move the fields up and down, it's the saving that's killing me.  
0
pimpsuitCommented:
Are you getting an error with the dynamic ORDER BY clause, and, if so, what is it?
0
CashmgmtAuthor Commented:
well, no.  it's just doing what it was doing before.  the javascript moves the field up or down to where i want it, but when I click save, it puts the field back in the original position.  shouldn't I need a new query to handle the save?
0
pimpsuitCommented:
I think that you should first determine if the right value is in fact being passed. Somewhere after the <cfparam name="form.rptFlds" default="at_report"> I suggested earlier you should temporarily display the value of form.rptFlds via <cfoutput>#form.rptFlds#</cfoutput>. When you click your Save button, the value in your select box will be displayed. Try that and see what happens.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.