Solved

CFLoop

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

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

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…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

734 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