?
Solved

500 points - Add records from a temp table

Posted on 2004-09-02
3
Medium Priority
?
207 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 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Suggested Courses

764 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