Solved

500 points - Add records from a temp table

Posted on 2004-09-02
3
177 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 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
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 …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now