Solved

CFLoop

Posted on 2001-06-19
5
138 Views
Last Modified: 2013-12-24
Dear Experts,

I have a form which will enter new records into 2 table namely Personal(Fields: Name,Age,IdNo) and SkillSet(Fields: IdNo,Skill,Ratings).As you can see, Personal table is joined with SkillSet in IdNo. Each IdNo will have several records of skills.Creating a form to enter the values into Personal is not a problem. I can't say the same for the other. How can I create just 1 form which will add into both tables where there will be unfix no. of skill base by a query?
 
Thanks in advance!

0
Comment
Question by:indzign
  • 3
5 Comments
 
LVL 15

Expert Comment

by:danrosenthal
ID: 6207431
You can create a form with a variable number of input boxes for the skill set and then create them with a loop statement.  Then on your insert template you would loop through the input boxes again and insert them into your table.

Here's how it would go:

Form1.cfm:
=========
<form action="form2.cfm">
Name:<input type="text" name="name">
Age:<input type="text" name="age">

<!--- set the number of skill inputs, not sure exactly how you wanted to do this, you could also pull this number from a database, or be even fancier and display one skill input at first with a button to increase the number of input boxes --->

<CFSET NumSkills=5>
<CFOUTPUT>
<CFLOOP FROM="1" TO="#NumSkills#" INDEX="i">
 Skill:<input type="text" name="Skill#i#">
 Rating:<input type="text" name="Ratings#i#">
</CFLOOP>
<INPUT TYPE="hidden" name="NumSkills" Value="#NumSkills#">
</CFOUTPUT>
<input type="submit">

</form>



Form2.cfm
========
<!--- Check for duplicate Name/Age Here, and display error if duplicate is found --->

<!--- Insert Person --->
<CFQUERY ...>
INSERT INTO Personal(Name, Age)
VALUES('#Name#',#age#)
</CFQUERY>

<!--- Get IDno --->
<CFQUERY ... Name="qryGetID">
SELECT IDNo
FROM personal
WHERE name='#name#'
AND age=#age#
</CFQUERY>

<CFSET IDNo="#qryGetID.IDNo#">

<CFLOOP FROM="1" TO="#NumSkills#" INDEX="i">
  <CFQUERY ...>
    INSERT INTO SkillSet(IDNo,Skill,Ratings)
    VALUES(#IDNo#,'#Skill##i#','#Ratings##i#')
  </CFQUERY>
</CFLOOP>

0
 
LVL 15

Accepted Solution

by:
danrosenthal earned 100 total points
ID: 6207452
The last loop is incorrect, and also there should also be an if statement around the SkillSet Insert to check for incomplete info, like this:

<CFLOOP FROM="1" TO="#NumSkills#" INDEX="i">
  <CFIF Evaluate("Skill"&i) neq "" AND Evaluate("Ratings"&i) neq "")>
    <CFQUERY ...>
     INSERT INTO SkillSet(IDNo,Skill,Ratings)
     VALUES(#IDNo#,'#Evaluate("Skill"&i)#','#Evaluate("Ratings"&i)#')
    </CFQUERY>
  </CFIF>
</CFLOOP>
0
 
LVL 5

Expert Comment

by:Yog
ID: 6207485
form1.
have form fields like
Name, Age , Skill, Ratings

form2
<cftransaction>
<cfquery>
insert into skillset
(#form.skill#, #form.ratings#)
</cfquery>
<cfquery name="name2">
select max(idno) from skillset
</cfquery>
<cfquery>
insert into personal
(#form.name#, #form.age#,#name2.idno#)
</cfquery>
</cftransaction>

OR I will prefer a stored proc. So is it what u r looking for

Cheers..
0
 

Author Comment

by:indzign
ID: 6212761
danrosental gave me almost a perfect answer. In form 1, using cfloop don't really work well. As the values of skill were extracted from a database and there were 5 skills with different values, the output based on query and using cfloop resulted in this manner

Skill1
Skill1
Skill1
Skill1
Skill1
Skill2
x 5
.....

So what I did in for 1 was replacing the name of the form field to
Skill#Skill.CurrentRow#. Don't know if this is the best solution, but it works with danrosenthal's suggestion for form 2 :))

Thanks a million!
0
 
LVL 15

Expert Comment

by:danrosenthal
ID: 6215274
happy to help
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.

Join & Write a Comment

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
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 …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
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…

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

20 Experts available now in Live!

Get 1:1 Help Now