ColdFusion & MSSQL: how to insert multiple rows with one unique identifier in one view

Question

can anyone help me on how to send multiple rows in one view?

this survey form will display a set of skills derived from a table skill. the teacher will need to check students' skills in checkboxes and click "Submit."

A student may have more than one skill. if he has 3 skills, then the data that is sent to the database will be in 3 lines as soon as the teacher clicks 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 | +----------+----------+ 

Answer 1

Another simple approach for inserting multiple records is INSERT .. SELECT . (Also mentioned in banyr link). Since the skill ID is stored in another table, you can use the IN SELECT for SELECT . Then paste these values ​​directly into another studenSkill table with a simple query, without loops.

 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"> 

By the way, in case this is not a typo, do not forget the # characters around the name of the query column, that is, "skillid"

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

Answer 2

You should be able to use the following syntax, assuming you are on a SQL 2008 server:

 INSERT INTO StudentSkill (StudentID, SkillID) VALUES (100, 1), (100, 2), (100, 3) 

This method is gleaned from here , which also contains several alternative methods.

You just need to iterate over the list of identifiers in FORM.skillid (assuming your form works) to create the SQL above. Also, make sure you use <CFQueryParam> for values ​​when creating SQL. Something like the code below should do:

 <cfif ListLen(FORM.skillid)> <cfquery> INSERT INTO StudentSkill (StudentID, SkillID) VALUES <cfloop list="#form.skillid#" index="skill"> (<cfqueryparam value="#form.studentID#" CFSQLType="CF_SQL_INTEGER">, <cfqueryparam value="#skill#" CFSQLType="CF_SQL_INTEGER">) </cfloop> </cfquery> </cfif>