Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 142
  • Last Modified:

Index variable #i# in CFLOOP TAG is not passed to SQL DB every time

Input Form:

 <CFform method="post" action="generate_rotation-OK.cfm">
           
           
              <p><font face="Arial, Helvetica, sans-serif" size="2" color="#9966cc">Academic Year</font>&nbsp;&nbsp;<font face="Arial, Helvetica, sans-serif" size="2" color="#9966cc">Grad Class</font>&nbsp;&nbsp;&nbsp;<font face="Arial, Helvetica, sans-serif" size="2" color="#9966cc">rotation</font></p>
              <p> </p>
              <p>
             <input type="text" name="grad_class" size="8" maxlength="8" REQUIRED="yes" >
                &nbsp;&nbsp;&nbsp;&nbsp;<input type="text" name="academic_year" size="8" maxlength="8" REQUIRED="yes">&nbsp;&nbsp;&nbsp;&nbsp;
                    <input type="text" name="rotation_code" size="8" maxlength="8">
            <p><font face="Arial, Helvetica, sans-serif" size="2" color="#9966cc">Period
               </font></p>
               <input type="checkbox" name="period" size="4" VALUE="1">1  
               <input type="checkbox" name="period" size="4" VALUE="2">2
               <input type="checkbox" name="period" size="4" VALUE="3">3
               <input type="checkbox" name="period" size="4" VALUE="4">4
               <input type="checkbox" name="period" size="4" VALUE="5">5
               <input type="checkbox" name="period" size="4" VALUE="6">6
               <input type="checkbox" name="period" size="4" VALUE="7">7
                 <input type="checkbox" name="period" size="4" VALUE="8">8
               <input type="checkbox" name="period" size="4" VALUE="9">9
               <input type="checkbox" name="period" size="4" VALUE="10">10
                <input type="checkbox" name="period" size="4" VALUE="11">11
                <input type="checkbox" name="period" size="4" VALUE="12">12
                <input type="checkbox" name="period" size="4" VALUE="13">13  
              <p>  
                <input type="submit" name="Submit" value="Submit">
              </p>
            </cfform>

Action Page:

<CFSET period=#form.period#>

<CFTRANSACTION>
 
 <CFLOOP INDEX="i" LIST="#period#">

 <!--- <CFOUTPUT>#i#</CFOUTPUT> --->
 
<CFQUERY NAME="qaddrots" DATASOURCE="#db#">
     exec spSM_generaterotation
      @grad_class=#form.grad_class#, @period=#i#, @academic_year=#form.academic_year#,@rotation_code='#form.rotation_code#'  </CFQUERY>
  <CFOUTPUT>#i#</CFOUTPUT>
  </CFLOOP>
 
 
</CFTRANSACTION>

spSM_generaterotation code:

begin


insert  rotation_schedule (academic_year,grad_class,rotation_choice_code,rotation_choice_limit)
select academic_year,grad_class,rotation_choice_code,rotation_choice_limit
from rotation_choice
where grad_class=@grad_class
and academic_year=@academic_year
and rotation_code=@rotation_code
end

begin
update rotation_schedule
set period=@period
where grad_class=@grad_class
and academic_year=@academic_year
and rotation_choice_code='ac'

end

The problem is that when the update occurs it is only picking up the value of the last checkbox that was checked rather than iterating through each value of i for each update.  (The insert portion is working fine.  It does insert the same number of rows that is checked ).  Why is SQL Server "dropping all of the values of the index "i" except for the last one when doing the update?
0
CTerreri
Asked:
CTerreri
  • 4
  • 3
1 Solution
 
danrosenthalCommented:
Looks like you are overwriting the value each time you loop through.  That is why your period value equals the last period selected.

0
 
CTerreriAuthor Commented:
How do I undo this?  I can't see exactly how this occuring.
0
 
danrosenthalCommented:
If your period list is 1,2,3,4

the query:

update rotation_schedule
set period=@period
where grad_class=@grad_class
and academic_year=@academic_year
and rotation_choice_code='ac'

gets run 4 times...
the first time setting period = 1
then period = 2
then period = 3
and finally period = 4

what exactly were you trying to do?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
CTerreriAuthor Commented:
Yes, that is exactly the intent but that is not what is occurring.  If I use your example, what actually occurs is that all four rows will update period to 4.  That is my problem.
0
 
danrosenthalCommented:
why not just add it in one step, instead of trying to add first and update immediately?

something like...

insert  rotation_schedule (academic_year,grad_class,rotation_choice_code,rotation_choice_limit, period)
select academic_year,grad_class,rotation_choice_code,rotation_choice_limit, @period
from rotation_choice
where grad_class=@grad_class
and academic_year=@academic_year
and rotation_code=@rotation_code
end

0
 
CTerreriAuthor Commented:
The column period does not exist in table rotation_choice, but I never thought of selecting the variable @period in the insert statement.  This worked fine.

Thanks for your help.
0
 
danrosenthalCommented:
sure, anytime.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now