Skip to main content
P
pantera32
Participating Frequently
September 27, 2006
Answered

Insert Multiple records from one form with checkboxes

  • September 27, 2006
  • 9 replies
  • 2164 views
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------------------------------------------------------------------

<!--Get Employee Names -->
<cfquery name="rsDisplayEmployee" datasource="hrtraining">
SELECT *
FROM tblEmployees
ORDER BY LastName ASC</cfquery>
<!-- -->
<!--Insert Records -->
<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>
    This topic has been closed for replies.
    Correct answer azadisg
    by 'add a row' i mean that for each of selected checkboxes in your form the code will insert a new record in the database table. sorry i probably didn;t phrase it very well.

    so, no, the code does not need to wrap around each element. instead the code looks into the form.fieldnames variable (form.fieldnames, which is a comma-delimited list of all defined fields in the form, is automatically submitted with every form submission - you do not have to define it yourself in any way) and for every checkbox selected will insert a new record into your table with the selected checkbox's value and selected employee's id value.

    9 replies

    M
    michaelmuller
    Known Participant
    September 28, 2006
    If you don't have a form struct on the action page then it's possible the form tag is not using action="post" but instead action="get" or your browser has firewall issues.

    Checkboxes are usually used all with the same name if grouped together. They are a variant of radio button: If there are five radio buttons with the same name, only one can be chosen; if there are five checkboxes with the same name zero or more can be selected and the form variable will comma deliminate all selected values. This is why both use CHECKED as a pre-selector.

    Despite this, radio buttons act more like the drop-down select in their exclusivity, with the exception that if no radio button is selected in a set the name/value pair will not post with the form, whereas a drop-down select must send a name/value pair of some kind.

    Unless it is a multiple select, in which case it acts more like a set of checkboxes in a sliding interface and requires the use of the CTRL key to select more than one value.

    But we all knew that. Just was responding to the person who remarked that all the checkboxes had the same name.
    P
    pantera32Author
    Participating Frequently
    September 28, 2006
    I got this working properly with Sabaidee's solution...I decided to start completely over and when I buit my form and action page this time it all worked.

    Thanks for everyones help -
    P
    pantera32Author
    Participating Frequently
    September 28, 2006
    I got this when I did CFOUTPUT:

    struct [empty]
    D
    Dan_Bracuk
    Inspiring
    September 28, 2006
    Form.fieldnames always exists if you submitted a form. If you got that error, you probably don't have a form structure. To verify, do this:

    <cfdump var="#form#">
    <cfabort>
    P
    pantera32Author
    Participating Frequently
    September 28, 2006
    Sabaidee,

    After inserting the code, I get this error message - I guess I'm confused now if form.fieldnames doesn't have to be defined as you said:

    Element FIELDNAMES is undefined in FORM.

    A
    azadisgCorrect answer
    Inspiring
    September 27, 2006
    by 'add a row' i mean that for each of selected checkboxes in your form the code will insert a new record in the database table. sorry i probably didn;t phrase it very well.

    so, no, the code does not need to wrap around each element. instead the code looks into the form.fieldnames variable (form.fieldnames, which is a comma-delimited list of all defined fields in the form, is automatically submitted with every form submission - you do not have to define it yourself in any way) and for every checkbox selected will insert a new record into your table with the selected checkbox's value and selected employee's id value.
    A
    Anonymous
    September 27, 2006
    In your example with two checkboxes, one of three things can happen.
    1) The user submits the form with neither checkbox checked. In this case, the form variable SessionNumber is not passed to the action page. So you need a <CFPARAM NAME="SessionNumber" DEFAULT=""> at the top of the action page.
    2) The user checks one or the other of the checkboxes. SessionNumber will be either 937 or 936 in the action page.
    3) The user checks both checkboxes. SessionNumber will be 936,936 in the action page.

    So you can loop over a list, similar to the concept code below.
    D
    Dan_Bracuk
    Inspiring
    September 27, 2006
    I, on the other hand, am a coder. I had a similar requirement once. The difference is that I had checkbox/dropdown combos. I did this:

    Form code (inside a cfoutput query="sorted" tag)
    <input type="checkbox" name="id#firstaidid#"<cfif sortcol is "aaa">checked="checked"</cfif>>
    #FirstAidMethod#
    </td>
    <td colspan="2">
    <cfselect name="appropriate#firstaidid#" query="dropdown"
    value="afield" display="afield"
    selected="#sorted.appropriate[currentrow]#"/>

    Form processing code (inside cfscript)
    idlist = "";
    AppropriateList = "";
    /* the form has check boxes named id1, id2, etc. Some will be checked, some won't. There will also be form fields named appropriate1, appropriate2, etc. We want two lists, one for the selected ids, and one for the corresponding appropriateness*/

    for (i = 1; i lte ListLen(form.fieldnames); i = i + 1) {
    ThisItem = ListGetAt(form.fieldnames, i);
    if (left(ThisItem, 2) is "id") {
    ThisNumber = Right(ThisItem, Len(ThisItem) - 2);
    idlist = listappend(idlist, ThisNumber);
    ThisOtherItem = "appropriate" & ThisNumber;
    appropriatelist = ListAppend(appropriatelist, form[ThisOtherItem]);
    } // left 2 is id
    } // end of loop

    Now I have two lists of equal length. Here is my insert

    <cfif listlen(IdList) gt 0>
    <cfloop from="1" to="#ListLen(IDList)#" index="i">
    <cfquery name="addrecords" datasource="abc">
    insert into burns_CaseFirstAid
    (CaseId, FirstAidID, Appropriate)
    values
    (#session.caseid#, #ListGetAt(IDList, i)#, '#ListGetAt(AppropriateList, i)#')
    </cfquery>
    </cfloop>
    </cfif>


    Hope that helps.
    A
    azadisg
    Inspiring
    September 27, 2006
    first of all, i am wondering how you can "select multiple checkboxes" when they all seem to have the same name, which basically makes them into a checkbox group which will allow selecting only one value....

    secondly... to insert multiple values (read: rows) into the db, you will need to cfloop through all selected checkboxes. with them all having the same name, that will be quiet hard without extra javascript... so please do change their names to have same *pattern*, but different sequential number, like sessionnumber_!, sessionnumber_2, etc...
    then you can loop through the formfields and for each form field defined with a name like "sessionnumber_" add a row:
    <cfloop list="#form.fieldnames#" index="fieldName">
    <cfif FindNoCase("sessionnumber_", fieldName) IS 1>
    <cfquery name="insertRow" datasource="yourdsn">
    INSERT INTO tblEnrollments (SessionNumber, EmployeeID)
    VALUES (#Evaluate("form." & fieldName)#, #form.EmplyeeID#);
    </cfquery>
    </cfif>
    </cfloop>

    try that see if it works for you...
    P
    pantera32Author
    Participating Frequently
    September 27, 2006
    Sabaidee,

    When you say to 'add a row' for each form element...what exactly do you mean by that? Does this code need to wrap around each form element?

    "secondly... to insert multiple values (read: rows) into the db, you will need to cfloop through all selected checkboxes. with them all having the same name, that will be quiet hard without extra javascript... so please do change their names to have same *pattern*, but different sequential number, like sessionnumber_!, sessionnumber_2, etc...
    then you can loop through the formfields and for each form field defined with a name like "sessionnumber_" add a row:
    <cfloop list="#form.fieldnames#" index="fieldName">
    <cfif FindNoCase("sessionnumber_", fieldName) IS 1>
    <cfquery name="insertRow" datasource="yourdsn">
    INSERT INTO tblEnrollments (SessionNumber, EmployeeID)
    VALUES (#Evaluate("form." & fieldName)#, #form.EmplyeeID#);
    </cfquery>
    </cfif>
    </cfloop>
    "
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices