We help IT Professionals succeed at work.

cfquery problems in coldfusion

355 Views
Last Modified: 2013-12-24
I have form where I am trying to sort the content of the drop down. My where clause is based upon session varibale that was passed from the first screen. The session varibale is created and exists until I pressing the submit button. the form submits to itself . After the submit is pressed then session variable is desapearing . I do not know why ?
<cfquery name="SetOrder" datasource="HotBanana_Manager">
UPDATE trnAcademicProgramCategory
SET categorySort = #Order#
WHERE  categoryId=#field# and schoolCode='#session.schoolcode#'
</cfquery>
<cfset Order = Order + 1>
</cfloop>
</cfif>
Comment
Watch Question

James RodgersWeb Applications Developer
CERTIFIED EXPERT

Commented:
>>After the submit is pressed then session variable is desapearing . I do not know why ?
we need to see the full page code tehn, the problem isn't teh query, something else is goiing on where the session var is reset or deleted
please post the screen where the session var is set(first page) anmd teh second page code.
erikTsomikSystem Architect, CF programmer

Author

Commented:
I will post you the code. The session variable gets set in the function called GenerateAdmissionsWizardtext

<cfcomponent displayname="newPsa" extends="hb50.tool">

<cffunction name="mainMenu" >
      <cfargument name="stepId" required="yes" type="string">


<cfsavecontent variable="output">
      <cfoutput>
<cfif stepId eq "1">
        <cfoutput>
              #school()#        </cfoutput>
      </cfif>
   
 
    <cfif ((stepId eq "business") or (stepId eq "education") or (stepId eq "psl")) >
        <cfoutput>
              #GenerateAdmissionsWizardText (stepId,degree)#      </cfoutput>
      </cfif>
      </cfoutput>
</cfsavecontent>
<cfreturn output>
</cffunction>

<cffunction name="school" >
      <cfsavecontent variable="output">
    <cfquery name="school" datasource="HotBanana_Manager">
          select distinct school_division from trnAcademicPrograms
    </cfquery>
   
      <cfoutput>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

      <script language="JavaScript" type="text/javascript">

function validateForm(objForm){
      var strErr='';
     
      if(!objForm.school.options[objForm.school.selectedIndex].value){
            strErr='Invalid Selection!\nUnable to submit form.\nPlease make a valid selection and try again.'
      }
     
      if(strErr){
            alert(strErr);
      }else{
          objForm.action='?debug&step='+objForm.school.options[objForm.school.selectedIndex].value;
      }

return !strErr;

}





</script>

   
    <div id="menuOption">
    <h2 style="margin:20px;">Please select a desired school:</h2>
 <form action="" method="post" enctype="multipart/form-data" onSubmit="return validateForm(this);" >
             <select name="school" id="school">

<option>[Please select a school]</option>

<cfloop query="school"><option value="#school_division#">#school_division#</option> </cfloop>

        </select>
               
                <input type="submit" value="Select School" id="btnsubmit" >
      </form>
      </div>
  </cfoutput>
    </cfsavecontent>
    <cfreturn output>
</cffunction>


<cffunction name="GenerateAdmissionsWizardText">
            <cfargument name="stepId" required="yes" type="string">
            <cfargument name="degree" required="yes" type="string">

<cfsavecontent variable="output">
         
<cfparam name="form.school" default="">
<cfset session.schoolcode=form.school>
       
        <link href="/spry/widgets/tabbedpanels/SpryTabbedPanels.css" rel="stylesheet" type="text/css" />
        <script type="text/javascript" src="/spry/widgets/tabbedpanels/SpryTabbedPanels.js"></script>
        <script type="text/javascript">
            
            function Field_up(lst) {
var i = lst.selectedIndex;
if (i>0) Field_swap(lst,i,i-1);
}
function Field_down(lst) {
var i = lst.selectedIndex;
if (i<lst.length-1) Field_swap(lst,i+1,i);
}
function Field_swap(lst,i,j) {
var t = '';
t = lst.options[i].text; lst.options[i].text = lst.options[j].text; lst.options[j].text = t;
t = lst.options[i].value; lst.options[i].value = lst.options[j].value; lst.options[j].value = t;
t = lst.options[i].selected; lst.options[i].selected = lst.options[j].selected; lst.options[j].selected = t;
t = lst.options[i].defaultSelected; lst.options[i].defaultSelected = lst.options[j].defaultSelected; lst.options[j].defaultSelected = t;
}
 function SetFields(lst,lstSave) {
var t;
lstSave.value=""
for (t=0;t<=lst.length-1;t++)
lstSave.value+=String(lst.options[t].value)+",";
if (lstSave.value.length>0)
lstSave.value=lstSave.value.slice(0,-1);
}

            
            
            </script>
        <div id="TabbedPanels1" class="TabbedPanels">
          <ul class="TabbedPanelsTabGroup">
                
            <li class="TabbedPanelsTab" tabindex="0" >Category</li>
            <li class="TabbedPanelsTab" tabindex="0" >SubCategory</li>
          </ul>
          <div class="TabbedPanelsContentGroup">
            <div class="TabbedPanelsContent">
         
            <div class="TabbedPanelsContent">
             <cfstoredproc procedure="spWebProgramList" datasource="#this.datasource#">
                  <cfprocparam type="in" cfsqltype="cf_sql_varchar" variable="@school" value="#session.schoolcode#">
                  <cfprocresult name="request.programlist">
            </cfstoredproc>
           
                  <cfif session.schoolcode eq "psl">
                  <h2>Step 1: Select Your Program</h2>
            <cfelse>    
                        <h2>Step 1: Select Your Area of Interest</h2>
            </cfif>    
           
            <cfoutput><p>Thank you for choosing the #session.schoolname#.<cfif session.schoolcode eq "business"> <strong>Please note that all applications to the Carey Business School should be submitted via the Web using the online application. Paper applications are no longer accepted.</strong></cfif> The information and steps that follow should help you easily navigate the online admissions process. If you have additional questions, please don't hesitate to contact us at #session.appphonenumber# or email <a href="mailto:#session.appemail#">#session.appemail#</a>.</cfoutput>

                   
    <!--- if school is PSL, set the URL param f to n, which waives the application fee--->
            <cfoutput>
                  <cfif session.schoolcode eq "psl">
           
                        <form action="" method="post" enctype="multipart/form-data" name="category">
            <cfelse>
                  <form action="" method="post" enctype="multipart/form-data" name="category" onsubmit="SetFields(document.category.programlist,document.category.FieldsSave);">
            </cfif>    
               <input type="hidden" Name="FieldsSave">
                  </cfoutput>
                  
         
      <cfif session.schoolcode eq "psl">
                  <cfoutput query="request.programlist" >
                        <h2>#category#</h2>
                <label for="programlist" title="Area of interest"></label>
                        <select id="programlist" name="degree" multiple="true" size="#cnt#">                  
                <cfoutput>
                    <option value="#id#">#webname#</option>
                </cfoutput>      
                </select>
               </cfoutput>
           
            <cfelse>
            
                  <h2>Select an area of interest from the list below:</h2>
                       <select  id="programlist" size="15">
               
                         <cfoutput query="request.programlist" group="category">
                               <option value="#categoryid#">#category#</option>
                        </cfoutput>      
                         </select>
             </cfif>  
             <div id="upDown">
            <input type="button" id="moveUp" name="moveUp" value="MoveUp" onclick="javascript:Field_up(document.category.programlist);"/>
                  <input type="button" id="moveDown" name="moveDown" value="moveDown" onclick="javascript:Field_down(document.category.programlist);"/>
            </div>
            <cfif session.schoolcode eq "psl">
               <p> <input type="reset" name="reset" value="Reset">
                <input type="submit" name="submit" value="Select"></p>
               
            <cfelseif session.schoolcode eq "educaton">    
                <p><em>Inquiries about doctoral programs in education should be directed to the Department of Teacher Development and Leadership at 410-516-9755 or the Department of Special Education at 410-516-8275.</em></p>
            </cfif>    
            <input type="submit" value="Submit" name="btnsubmit" id="btnsubmit2">
                  </form>
           </div>
             
          </div>
          <div class="TabbedPanelsContent">Content 3</div>
       
        </div>
   
    <cfparam name="form.FieldsSave" default="">

<cfif isDefined("btnsubmit")>
<cfset Order=1>
<cfloop list="#Form.FieldsSave#" index="field">
<cfquery name="SetOrder" datasource="HotBanana_Manager">
UPDATE trnAcademicProgramCategory
SET categorySort = #Order#
WHERE  categoryId=#field# and schoolCode='#session.schoolcode#'
</cfquery>
<cfset Order = Order + 1>
</cfloop>
</cfif>
       <cfdump var="#form#">
      <cfdump var="#Form.FieldsSave#">
<cfoutput>
               <script language="JavaScript" type="text/javascript">
                     var TabbedPanels1 = new Spry.Widget.TabbedPanels("TabbedPanels1", { defaultTab: 0});
</script>


       
</cfoutput>
            </cfsavecontent>
            <cfreturn output>
      </cffunction>


<!---Get Front End function--->
<cffunction name="getFrontEnd">
      <cfset var output = StructNew()>
            <cfset output.title ="">
        <cfset output.blnHideContent = false>
       
        <cfparam name="URL.degree" default="">
            <cfparam name="URL.step" default="1">
       
            <cfset stepCardinality=Left("#URL.step#", 1)>
       
        <cfsavecontent variable="output.body">
     

    <cfoutput>
             #mainMenu(url.step)#   </cfoutput>
 </cfsavecontent>
<cfreturn output>
</cffunction>
</cfcomponent>

     
James RodgersWeb Applications Developer
CERTIFIED EXPERT

Commented:
here is the problem
<cfparam name="form.school" default="">
<cfset session.schoolcode=form.school>
teh session var is set to '' whenteh form is submitted, you need to change this processing to validate before setting teh session var
James RodgersWeb Applications Developer
CERTIFIED EXPERT

Commented:
you can fix this by adding a hidden field to teh second form
<input type="hidden" value='#session.schoolcode#" name="school">
erikTsomikSystem Architect, CF programmer

Author

Commented:
I added the hidden field to the second form but still the query does ncot receive the schoolcode

<cfcomponent displayname="newPsa" extends="hb50.tool">

<cffunction name="mainMenu" >
      <cfargument name="stepId" required="yes" type="string">


<cfsavecontent variable="output">
      <cfoutput>
<cfif stepId eq "1">
        <cfoutput>
              #school()#        </cfoutput>
      </cfif>
   
 
    <cfif ((stepId eq "business") or (stepId eq "education") or (stepId eq "psl")) >
        <cfoutput>
              #GenerateAdmissionsWizardText (stepId,degree)#      </cfoutput>
      </cfif>
      </cfoutput>
</cfsavecontent>
<cfreturn output>
</cffunction>

<cffunction name="school" >
      <cfsavecontent variable="output">
    <cfquery name="school" datasource="HotBanana_Manager">
          select distinct school_division from trnAcademicPrograms
    </cfquery>
   
      <cfoutput>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

      <script language="JavaScript" type="text/javascript">

function validateForm(objForm){
      var strErr='';
     
      if(!objForm.school.options[objForm.school.selectedIndex].value){
            strErr='Invalid Selection!\nUnable to submit form.\nPlease make a valid selection and try again.'
      }
     
      if(strErr){
            alert(strErr);
      }else{
          objForm.action='?debug&step='+objForm.school.options[objForm.school.selectedIndex].value;
             
      }

return !strErr;

}





</script>

   <cfparam name="form.school" default="">
   <cfparam name="form.session" default="">
<cfset session.schoolcode=form.school>
<cfoutput>session:#session.schoolcode#</cfoutput>
    <div id="menuOption">
    <h2 style="margin:20px;">Please select a desired school:</h2>
 <form action="" method="post" enctype="multipart/form-data" onSubmit="return validateForm(this);" >
             <select name="school" id="school">

<option>[Please select a school]</option>

<cfloop query="school"><option value="#school_division#">#school_division#</option> </cfloop>

        </select>
               
                <input type="submit" value="Select School" id="btnsubmit" >
               
      </form>
      </div>
  </cfoutput>
    </cfsavecontent>
    <cfreturn output>
</cffunction>


<cffunction name="GenerateAdmissionsWizardText">
            <cfargument name="stepId" required="yes" type="string">
            <cfargument name="degree" required="yes" type="string">

<cfsavecontent variable="output">
         

       
        <link href="/spry/widgets/tabbedpanels/SpryTabbedPanels.css" rel="stylesheet" type="text/css" />
        <script type="text/javascript" src="/spry/widgets/tabbedpanels/SpryTabbedPanels.js"></script>
        <script type="text/javascript">
            
            function Field_up(lst) {
var i = lst.selectedIndex;
if (i>0) Field_swap(lst,i,i-1);
}
function Field_down(lst) {
var i = lst.selectedIndex;
if (i<lst.length-1) Field_swap(lst,i+1,i);
}
function Field_swap(lst,i,j) {
var t = '';
t = lst.options[i].text; lst.options[i].text = lst.options[j].text; lst.options[j].text = t;
t = lst.options[i].value; lst.options[i].value = lst.options[j].value; lst.options[j].value = t;
t = lst.options[i].selected; lst.options[i].selected = lst.options[j].selected; lst.options[j].selected = t;
t = lst.options[i].defaultSelected; lst.options[i].defaultSelected = lst.options[j].defaultSelected; lst.options[j].defaultSelected = t;
}
 function SetFields(lst,lstSave) {
var t;
lstSave.value=""
for (t=0;t<=lst.length-1;t++)
lstSave.value+=String(lst.options[t].value)+",";
if (lstSave.value.length>0)
lstSave.value=lstSave.value.slice(0,-1);
}

            
            
            </script>
       
  <cfparam name="form.school" default="">
<cfset session.schoolcode=form.school>
<!---<cfparam name="form.school" default="">--->
<input type="hidden" value="#session.schoolcode#" name="form.school">


        <div id="TabbedPanels1" class="TabbedPanels">
          <ul class="TabbedPanelsTabGroup">
                
            <li class="TabbedPanelsTab" tabindex="0" >Category</li>
            <li class="TabbedPanelsTab" tabindex="0" >SubCategory</li>
          </ul>
          <div class="TabbedPanelsContentGroup">
            <div class="TabbedPanelsContent">
         
            <div class="TabbedPanelsContent">
             <cfstoredproc procedure="spWebProgramList" datasource="#this.datasource#">
                  <cfprocparam type="in" cfsqltype="cf_sql_varchar" variable="@school" value="#session.schoolcode#">
                  <cfprocresult name="request.programlist">
            </cfstoredproc>
           
                  <cfif session.schoolcode eq "psl">
                  <h2>Step 1: Select Your Program</h2>
            <cfelse>    
                        <h2>Step 1: Select Your Area of Interest</h2>
            </cfif>    
           
            <cfoutput><p>Thank you for choosing the #session.schoolname#.<cfif session.schoolcode eq "business"> <strong>Please note that all applications to the Carey Business School should be submitted via the Web using the online application. Paper applications are no longer accepted.</strong></cfif> The information and steps that follow should help you easily navigate the online admissions process. If you have additional questions, please don't hesitate to contact us at #session.appphonenumber# or email <a href="mailto:#session.appemail#">#session.appemail#</a>.</cfoutput>

                   
    <!--- if school is PSL, set the URL param f to n, which waives the application fee--->
            <cfoutput>
                  <cfif session.schoolcode eq "psl">
           
                        <form action="" method="post" enctype="multipart/form-data" name="category">
            <cfelse>
                  <form action="" method="post" enctype="multipart/form-data" name="category" onsubmit="SetFields(document.category.programlist,document.category.FieldsSave);">
            </cfif>    
               <input type="hidden" Name="FieldsSave">
               <input type="hidden" value="#session.schoolcode#" name="form.school">
                  </cfoutput>
                  
         
      <cfif session.schoolcode eq "psl">
                  <cfoutput query="request.programlist" >
                        <h2>#category#</h2>
                <label for="programlist" title="Area of interest"></label>
                        <select id="programlist" name="degree" multiple="true" size="#cnt#">                  
                <cfoutput>
                    <option value="#id#">#webname#</option>
                </cfoutput>      
                </select>
               </cfoutput>
           
            <cfelse>
            
                  <h2>Select an area of interest from the list below:</h2>
                       <select  id="programlist" size="15">
               
                         <cfoutput query="request.programlist" group="category">
                               <option value="#categoryid#">#category#</option>
                        </cfoutput>      
                         </select>
             </cfif>  
             <div id="upDown">
            <input type="button" id="moveUp" name="moveUp" value="MoveUp" onclick="javascript:Field_up(document.category.programlist);"/>
                  <input type="button" id="moveDown" name="moveDown" value="moveDown" onclick="javascript:Field_down(document.category.programlist);"/>
            </div>
            <cfif session.schoolcode eq "psl">
               <p> <input type="reset" name="reset" value="Reset">
                <input type="submit" name="submit" value="Select"></p>
               
            <cfelseif session.schoolcode eq "educaton">    
                <p><em>Inquiries about doctoral programs in education should be directed to the Department of Teacher Development and Leadership at 410-516-9755 or the Department of Special Education at 410-516-8275.</em></p>
            </cfif>    
            <input type="submit" value="Submit" name="btnsubmit" id="btnsubmit2">
                  </form>
                <cfparam name="form.FieldsSave" default="">

<cfif isDefined("btnsubmit")>
<cfset Order=1>
<cfloop list="#Form.FieldsSave#" index="field">
<cfquery name="SetOrder" datasource="HotBanana_Manager">
UPDATE trnAcademicProgramCategory
SET categorySort = #Order#
WHERE  categoryId=#field# and schoolCode='#session.schoolcode#'
</cfquery>
<cfset Order = Order + 1>
</cfloop>
</cfif>
           </div>
             
          </div>
          <div class="TabbedPanelsContent">Content 3</div>
       
        </div>
   

       <cfdump var="#form#">
      <cfdump var="#Form.FieldsSave#">
<cfoutput>
               <script language="JavaScript" type="text/javascript">
                     var TabbedPanels1 = new Spry.Widget.TabbedPanels("TabbedPanels1", { defaultTab: 0});
</script>


       
</cfoutput>
            </cfsavecontent>
            <cfreturn output>
      </cffunction>


<!---Get Front End function--->
<cffunction name="getFrontEnd">
      <cfset var output = StructNew()>
            <cfset output.title ="">
        <cfset output.blnHideContent = false>
       
        <cfparam name="URL.degree" default="">
            <cfparam name="URL.step" default="1">
        <cfparam name="url.school" default="">
       
            <cfset stepCardinality=Left("#URL.step#", 1)>
       
       
        <cfsavecontent variable="output.body">
     

    <cfoutput>
    <cfset session.schoolcode=#url.school#>
             #mainMenu(url.step)#   </cfoutput>
 </cfsavecontent>
<cfreturn output>
</cffunction>
</cfcomponent>

     
erikTsomikSystem Architect, CF programmer

Author

Commented:
OK I got it to work, but how can I see the changes as soon as the submit button button is pressed
James RodgersWeb Applications Developer
CERTIFIED EXPERT

Commented:
>>I added the hidden field to the second form but still the query does ncot receive the schoolcode
you named it form.school, it's supposed to be 'school'
erikTsomikSystem Architect, CF programmer

Author

Commented:
OK I got it to work, but how can I see the changes as soon as the submit button button is pressed
James RodgersWeb Applications Developer
CERTIFIED EXPERT

Commented:
>>OK I got it to work, but how can I see the changes as soon as the submit button button is pressed
not sure what you are asking here
to see teh cahnges you would neeed to refresh teh page
erikTsomikSystem Architect, CF programmer

Author

Commented:
how can I avoid refreshing the page
James RodgersWeb Applications Developer
CERTIFIED EXPERT

Commented:
if you do not want to refresh teh page, you will need to use AJAX or a popup window to sho the changes
erikTsomikSystem Architect, CF programmer

Author

Commented:
tell me how can i use the ajax to support that
Web Applications Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
erikTsomikSystem Architect, CF programmer

Author

Commented:
MY system is setum so where can we begin
James RodgersWeb Applications Developer
CERTIFIED EXPERT

Commented:
thats out of scope fro this question and my knowledge...
you need to have a processing page that can be called to return the values you need to update, any page will do, i plain cf page with a query and a return value

<cfquery name="getDesc">
SELECT SCHOOL_NAME
FROM TABLE
WHERE ....

</cfquery>
<cfoutput>#getDesc.school_name#</cfoutput>

you need to make a call to teh page and return the value
see here for more
http://www.w3schools.com/ajax/default.asp

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.