Solved

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

Posted on 2009-06-29
11
269 Views
Last Modified: 2013-12-24
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

0
Comment
Question by:Cashmgmt
  • 4
  • 4
  • 3
11 Comments
 
LVL 19

Expert Comment

by:erikTsomik
ID: 24736450
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
 

Author Comment

by:Cashmgmt
ID: 24736539
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
 
LVL 19

Expert Comment

by:erikTsomik
ID: 24736613
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
 
LVL 3

Accepted Solution

by:
pimpsuit earned 500 total points
ID: 24738036
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
 

Author Comment

by:Cashmgmt
ID: 24738451
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Expert Comment

by:pimpsuit
ID: 24738624
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
 
LVL 19

Expert Comment

by:erikTsomik
ID: 24738641
change to this
SELECT * FROM #RptTableName# WHERE layout= <cfqueryparam value="AP"> and at_report> <cfqueryparam value="0"> ORDER BY #form.rptFlds#
0
 

Author Comment

by:Cashmgmt
ID: 24739882
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
 
LVL 3

Expert Comment

by:pimpsuit
ID: 24740016
Are you getting an error with the dynamic ORDER BY clause, and, if so, what is it?
0
 

Author Comment

by:Cashmgmt
ID: 24740060
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
 
LVL 3

Expert Comment

by:pimpsuit
ID: 24740146
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now