ColdFusion & amp; MSSQL: How to Insert Multiple Lines with a Unique Identifier in a Submission

advertisements

can anyone help me on how to submit multiple rows in one submission?

this survey form will display a set of skill that derived from table skill. the teacher will have to check the students skill's in checkboxes and click submit.

a student can have more than one skill. if he has 3 skills, then the data that is sent into database will be in 3 rows once the teacher click the submit button. (table studentskill)

<cfoutput query="skill">
<tr>
    <td>#skill.skillname#</td>
    <td align="center">:</td>
    <td><input type="checkbox" name="skillid" value="skillid" checked="checked"></td>
</tr>
</cfoutput>

table skill

+---------+------------+
| skillid | skillname  |

+---------+------------+

| 1       | draw       |

+---------+------------+

| 2       | read       |

+---------+------------+

| 3       | dance      |

+---------+------------+

table studentskill

+----------+----------|

|studentid | skillid  |

+----------+----------+
| 001      | 1        |
| 001      | 2        |
| 002      | 1        |
| 002      | 2        |
| 002      | 3        |
+----------+----------+


Another simple approach for inserting multiple records is an INSERT .. SELECT. (It is also mentioned in the link banyr posted). Because the skill id's are stored in another table, you can use an IN clause to SELECT them. Then insert those values directly into your other table studenSkill with a simple query, no looping.

INSERT INTO studenSkill ( studenId, skillId )
SELECT <cfqueryparam value="#form.studentId#" cfsqltype="cf_sql_integer">
       , skillId
FROM   skill
WHERE  skillId IN
       (
       <cfqueryparam value="#form.skillId#" cfsqltype="cf_sql_integer" list="true">
       )

    <input type="checkbox" name="skillid" value="skillid" checked="checked">

BTW, in case that is not a typo, do not forget the # signs around the query column name ie "skillid"

  <input type="checkbox" name="skillid" value="#skillid#" checked="checked">