Insert Multiple Records from one form (coldfusion)

Report · Sep 27, 2006

Hello,

I need to insert multiple records into a database from a form with Coldfusion and am stuck – it functions properly when inserting one record at a time. Basically the user will select their employee name (EmployeeID) from a dynamic list, and then multiple session numbers (SessionNumber) which are training classes.

When I try and select multiple checkboxes I get this error:
‘Number of query values and destination fields are not the same.’

The SessionNumber field checkboxes are not dynamic as you can see – how do I use CFLOOP or any other method to allow multiple checkboxes to be selected and all SessionNumber records entered into the database on one form submit?

I have used Dreamweaver 8 server behaviors to build the form – which is limiting I know but I’m not a coder. Hopefully I can add some code here to make things function properly.

----APPLICATION CODE------------------------------------------------------------------


<cfquery name="rsDisplayEmployee" datasource="hrtraining">
SELECT *
FROM tblEmployees
ORDER BY LastName ASC</cfquery>


<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">
<cfquery datasource="hrtraining">
INSERT INTO tblEnrollments (SessionNumber, EmployeeID)
VALUES (
<cfif IsDefined("FORM.SessionNumber") AND #FORM.SessionNumber# NEQ "">
#FORM.SessionNumber#
<cfelse>
NULL
</cfif>
,
<cfif IsDefined("FORM.EmployeeID") AND #FORM.EmployeeID# NEQ "">
#FORM.EmployeeID#
<cfelse>
NULL
</cfif>
)</cfquery>
<cflocation url="2.cfm">
</cfif>


--------FORM CODE-----------------------------------------------------------------------

<form action="<cfoutput>#CurrentPage#</cfoutput>" id="form" name="form" method="POST">
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr>
<td width="100%"><h2><strong>Select Employee Name </strong></h2></td>
</tr>
<tr>
<td><select name="EmployeeID">
<cfoutput query="rsDisplayEmployee">
<option value="#rsDisplayEmployee.EmployeeID#">#rsDisplayEmployee.LastName#, #rsDisplayEmployee.FirstName#</option>
</cfoutput>
</select></td>
</tr>
</table>
<h2><strong>Select Training Class </strong></h2>
<table width="600" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="227" valign="top">Class</td>
<td width="373" valign="top"><input name="SessionNumber" type="checkbox" value="937" />
Date</td>
</tr>
<tr>
<td valign="top">Class</td>
<td valign="top"><label>
<input name="SessionNumber" type="checkbox" value="936" />
</label>
Date</td>
</tr>
</table>
<br />
<input type="submit" name="Submit" value="Sign Up!" />
<input type="hidden" name="MM_InsertRecord" value="form">
</form>

Report · Sep 27, 2006

"pantera32" <webforumsuser@macromedia.com> wrote in message
news:efe8cb$bmb$1@forums.macromedia.com...
> Hello,
>
> I need to insert multiple records into a database from a form with
> Coldfusion
> and am stuck ? it functions properly when inserting one record at a time.
> Basically the user will select their employee name (EmployeeID) from a
> dynamic
> list, and then multiple session numbers (SessionNumber) which are training
> classes.

Basically you need to append a number to each form field, so that you have
fields like firstname1, firstname2, etc. Create a hidden field to hold the
number of total records. Then you can use a loop and evaluate function to
insert the fields:

<input name="totalrows" value="3" type="hidden" />
<cfloop from="1" to="#form.totalrows#" index="i">
<cfquery datasource="#mydatasource#">
INSERT mytable
(firstname)
VALUES (
'#Evaluate("form.firstname#i#")#'
}
</cfquery>
</cfloop>

There is another way to do it using a list without appending numbers to the
fields, but it doesn't work with checkboxes.

I wrote an article about it here:
http://www.communitymx.com/content/article.cfm?cid=7B59D

It's free for members, and a small fee for non-members.

--
--
Tom Muck
co-author Dreamweaver MX 2004: The Complete Reference
http://www.tom-muck.com/

Cartweaver Development Team
http://www.cartweaver.com

Extending Knowledge Daily
http://www.communitymx.com/

Report · Sep 28, 2006

Hi Tom,

Does it matter where in the page the code should go? I placed this inside the form tag:

<input name="totalrows" value="3" type="hidden" />

And placed the loop after the form tag on the same page. Anyway after placing the code as I mentioned above, I get this error message after submitting:

Element TOTALROWS is undefined in FORM.

This can all be handled on one page right? Or does the loop need to go on action page after the form is processed? Maybe that is the problem?

Report · Sep 28, 2006

> Does it matter where in the page the code should go? I placed this inside
> the
> form tag:
>
> <input name="totalrows" value="3" type="hidden" />

That's correct.

> And placed the loop after the form tag on the same page. Anyway after
> placing
> the code as I mentioned above, I get this error message after submitting:
>
> Element TOTALROWS is undefined in FORM.
>
> This can all be handled on one page right? Or does the loop need to go on
> action page after the form is processed? Maybe that is the problem?

You have to put the insert code on an action page, or wrap it in an if
statement if you are making the form and action on the same page.

<cfif isdefined("form.totalrows")>
do your insert
</cfif>

--
--
Tom Muck
co-author Dreamweaver MX 2004: The Complete Reference
http://www.tom-muck.com/

Cartweaver Development Team
http://www.cartweaver.com

Extending Knowledge Daily
http://www.communitymx.com/