Solved

500 points - Add records from a temp table

Posted on 2004-09-02
3
202 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
[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
  • 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
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…

687 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