Solved

CFLoop

Posted on 2001-06-19
5
143 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
[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
  • 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

740 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