• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

CFLoop

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
indzign
Asked:
indzign
  • 3
1 Solution
 
danrosenthalCommented:
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
 
danrosenthalCommented:
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
 
YogCommented:
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
 
indzignAuthor Commented:
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
 
danrosenthalCommented:
happy to help
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now