?
Solved

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

Posted on 2003-02-27
7
Medium Priority
?
141 Views
Last Modified: 2013-12-24
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
Comment
Question by:CTerreri
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 15

Expert Comment

by:danrosenthal
ID: 8037831
Looks like you are overwriting the value each time you loop through.  That is why your period value equals the last period selected.

0
 

Author Comment

by:CTerreri
ID: 8037946
How do I undo this?  I can't see exactly how this occuring.
0
 
LVL 15

Expert Comment

by:danrosenthal
ID: 8038112
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
Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

 

Author Comment

by:CTerreri
ID: 8038171
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
 
LVL 15

Accepted Solution

by:
danrosenthal earned 400 total points
ID: 8038422
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
 

Author Comment

by:CTerreri
ID: 8038542
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
 
LVL 15

Expert Comment

by:danrosenthal
ID: 8038848
sure, anytime.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Course of the Month7 days, 23 hours left to enroll

765 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