Solved

CFLoop

Posted on 2001-06-19
5
148 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

Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

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.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

623 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