Solved

500 points - Add records from a temp table

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Migration from IIS6 onto IIS7 6 54
Asp.net Hosting Plan security, reliable, stable 1 75
JKS to store upstart data 2 99
site launch date and last modified date 3 94
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

822 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