?
Solved

500 points - Add records from a temp table

Posted on 2004-09-02
3
Medium Priority
?
214 Views
Last Modified: 2013-12-24
I am currently working with ColdFusion 5.0.

I have a group of student records in a temp table (FName, LName, UserName, Password) which I need to add to 3 other tables within the same database.

We have created a routine that generates random numbers for the users, but now I need a solution that will use the information from the temp table in place of the random numbers.  The temp table will hold anywhere from 250 to 500 students at a time and I need it to loop through all students in the temp table and create user accounts for them.  I've included the random code below for reference, but the student counts and student limts aren't important for this solution.

The code for the random function is below.

<cfif isdefined("url.AutoCreate") >
                  <cfif isdefined("form.autocreate")>
                        <cfif #form.studentcount# is "">
                        <cfset studentcount = 0>
                        <cfelse>
                        <cfset studentcount = "#form.studentcount#">
                        </cfif>
                        <cfset count = #getstudentlimit2.recordcount# + #variables.studentcount#>      
                        <cfif #variables.count# lte #getmaxstudent2.maxstudents# and #variables.studentcount# gt 0>
                              <cfquery name="GetTeacherDetails" datasource="Coin_GLynk">
                                    Select substring(lname,1,2) as lname from users where user_id = #client.user_id#
                              </cfquery>
                        <cfloop from="1" to="#variables.studentcount#" index="i">                        
                                    <cftransaction>
                                                <cfquery datasource ="Coin_Glynk">
                                                Insert into Users (siteID, agroup, role, viewable, status, logout, teacherID)
                                                                  Values('#client.SiteID#', 'Student', 'USR', 'T', 'Active','1', #client.user_id#)
                                                </cfquery>      
                                                
                                                <cfquery name="GetID2" datasource="Coin_Glynk">
                                                Select max(user_id) as user_id from users
                                                </cfquery>
                                                
                                    
                                                <cfset username1 =  "#GetID2.User_ID#">
                                                
                                                                              
                                                <cfquery datasource ="Coin_Glynk">
                                                Update Users
                                                Set
                                                      fname='#variables.username1#',
                                                      lname='#variables.username1#',
                                                      username='#variables.username1#',
                                                      password='#variables.username1#'
                                                where user_id = #GetID2.user_id# and siteid = '#client.siteid#'                                          
                                                </cfquery>
                                                
                                                <cfquery datasource="Coin_Glynk">
                                                Insert into user_logging(user_id) values (#GetID2.user_id#)
                                                </cfquery>
                                                
                                          </cftransaction>      
                                          <cfquery datasource="Resource">
                                          Insert into planner (siteid, user_id) values ('#client.siteid#', #GetID2.user_id#)
                                          </cfquery>                        
                              </cfloop>
                        <cfelse>
                              <cfset errmessage =
                              "<h3>These student accounts have NOT been created. You have tried to add more students than you have remaining or you did not enter a value for <i><u>how many</u></i>. Please contact your site administrator to request additional accounts.</h3>">
                              <cfoutput>
                              <!--- #GETMAXSTUDENT2.MAXSTUDENTS#
                              <br>
                              #VARIABLES.COUNT# --->
                              </cfoutput>
                        </cfif>
                  <cfelse>
                        <!--- You must check the box for <strong>"Would you like to generate Student Accounts?"</strong><br>
                        to enable auto generation. --->
                  </cfif>
            </cfif>
0
Comment
Question by:TobinLewis
  • 2
3 Comments
 
LVL 8

Accepted Solution

by:
MartinCMS earned 2000 total points
ID: 11974878
hi @TobinLewis,  try adding the codes I have added.....

<cfif isdefined("url.AutoCreate") >
               <cfif isdefined("form.autocreate")>
                    <cfif #form.studentcount# is "">
                    <cfset studentcount = 0>
                    <cfelse>
                    <cfset studentcount = "#form.studentcount#">
                    </cfif>
                    <cfset count = #getstudentlimit2.recordcount# + #variables.studentcount#>    
                    <cfif #variables.count# lte #getmaxstudent2.maxstudents# and #variables.studentcount# gt 0>
                         <cfquery name="GetTeacherDetails" datasource="Coin_GLynk">
                              Select substring(lname,1,2) as lname from users where user_id = #client.user_id#
                         </cfquery>

<!--- Add this query to you code --->
<CFQUERY NAME="Get_Student" DATASOURCE="Coin_GLynk">
      SELECT  Distinct FName, LName, UserName, Password
      FROM    yourTempTable <!--- your temp table name --->
      ORDER BY LName,FName
</CFQUERY>

<!--- this will loop thru all student from the above query --->
<CFLOOP QUERY="Get_Student">
       
                              <cftransaction>
                                        <cfquery datasource ="Coin_Glynk">
                                        Insert into Users (siteID, agroup, role, viewable, status, logout, teacherID)
                                                       Values('#client.SiteID#', 'Student', 'USR', 'T', 'Active','1', #client.user_id#)
                                        </cfquery>    
                                       
<!--- assuming that user_id field is auto increment Identity field --->
<!---you don't need to select max, will slow down the process.  --->
<cfqueryname="GetID2" datasource="Coin_Glynk">
        Select      @@IDENTITY as user_id  
    </cfquery>
                                       
<!--- not sure why you would need this --->                              
<cfset username1 =  "#GetID2.User_ID#">
                                       
<!--- Change this Update Statement  --->                                                                
<cfquery datasource ="Coin_Glynk">
Update Users
    Set      fname='#Get_Student.FName#',  <!--- from Get_Student --->
      lname='#Get_Student.LName#', <!--- from Get_Student --->
                username='#Get_Student.UserName#', <!--- from Get_Student --->
                password='#Get_Student.Password#' <!--- from Get_Student --->
where       user_id = #GetID2.user_id# and siteid  = '#client.SiteID#'
</cfquery>
                                       
                                        <cfquery datasource="Coin_Glynk">
                                        Insert into user_logging(user_id) values (#GetID2.user_id#)
                                        </cfquery>
                                       
                                   </cftransaction>    
                                   <cfquery datasource="Resource">
                                   Insert into planner (siteid, user_id) values ('#client.siteid#', #GetID2.user_id#)
                                   </cfquery>                    
                         </cfloop>
                    <cfelse>
                         <cfset errmessage =
                         "<h3>These student accounts have NOT been created. You have tried to add more students than you have remaining or you did not enter a value for <i><u>how many</u></i>. Please contact your site administrator to request additional accounts.</h3>">
                         <cfoutput>
                         <!--- #GETMAXSTUDENT2.MAXSTUDENTS#
                         <br>
                         #VARIABLES.COUNT# --->
                         </cfoutput>
                    </cfif>
               <cfelse>
                    <!--- You must check the box for <strong>"Would you like to generate Student Accounts?"</strong><br>
                    to enable auto generation. --->
               </cfif>
          </cfif>
0
 

Author Comment

by:TobinLewis
ID: 12084342
Thank you for the changes.  The code and the procedure works great.

Thanks again,

Tobin
0
 
LVL 8

Expert Comment

by:MartinCMS
ID: 12084406
You're most welcome, glad that I could help out!

Martin
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses
Course of the Month17 days, 12 hours left to enroll

830 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