?
Solved

cfquery problems in coldfusion

Posted on 2007-10-10
15
Medium Priority
?
331 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>
0
Comment
Question by:erikTsomik
  • 8
  • 7
15 Comments
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20049242
>>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.
0
 
LVL 19

Author Comment

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

     
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20049944
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
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 25

Expert Comment

by:James Rodgers
ID: 20049982
you can fix this by adding a hidden field to teh second form
<input type="hidden" value='#session.schoolcode#" name="school">
0
 
LVL 19

Author Comment

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

     
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 20050150
OK I got it to work, but how can I see the changes as soon as the submit button button is pressed
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20050171
>>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'
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 20050483
OK I got it to work, but how can I see the changes as soon as the submit button button is pressed
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20050618
>>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
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 20050635
how can I avoid refreshing the page
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20050701
if you do not want to refresh teh page, you will need to use AJAX or a popup window to sho the changes
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 20050723
tell me how can i use the ajax to support that
0
 
LVL 25

Accepted Solution

by:
James Rodgers earned 2000 total points
ID: 20050788
its not that simple...i'm just learning AJAX and if your siste is not set up for it it's a lot of work to reconfigure
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 20050811
MY system is setum so where can we begin
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 20051349
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

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
Suggested Courses

571 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