Skip to main content
July 24, 2011
Question

How can I insert data from a cfloop?

  • July 24, 2011
  • 3 replies
  • 1318 views

I have a query that outputs into a cfloop. What I need it to do is insert into an access database when the changes have been made. I have looked at several tutorials but they all offer different solutions than what I am looking for. My cflloop is:

<cfoutput>

<cfloop query="update" >

<tr>

<td><input type="text" name="meeting_date" value="#meeting_date#" /></td>

<td><input type="text" name="contact_first" value="#update.contact_first#" /></td>

<td><input type="text" name="contact_last" value="#update.contact_last#" /></td>

<td>Attended <input type="checkbox" name="attended" value="yes" /></td>

<td><input type="text" name="leads" size="3" /></td>

</tr>

</cfloop>

</cfoutput>

I then try to insert into the database with:

<cfset j = "1">

<cfloop index="i" from="1" to="#update.recordcount#">

<cfset meeting_date = "Form.i#j#">

<cfset contact_first = "Form.i#j#">

<cfset contact_last = "Form.i#j#">

<cfset attended = "Form.i#j#">

<cfset leads = "Form.i#j#">

<cfset meeting_date = Form["i#j#"]>

<cfset contact_first = Form["i#j#"]>

<cfset contact_last = Form["i#j#"]>

<cfset attended = Form["i#j#"]>

<cfset leads = Form["i#j#"]>

<cfquery datasource="#application.Database#">

INSERT INTO quarterly (meeting_date, contact_first, contact_last, attended, leads)

VALUES ('#meeting_date#', '#contact_first#', '#contact_last#','#attended#',leads)

</cfquery>

<cfset j=j+1>

</cfloop>

Any ideas as to how to get my cfloop to insert into my database? I don't think I can lose anymore sleep over this. Thanks,

    This topic has been closed for replies.

    3 replies

    pyae phyoe shein
    Known Participant
    July 25, 2011

    It's because of the way of your coding is quite wrong. Because we didn't give specific name for each individual input and didn't call specific value from each inputs.

    Here is your code I've fixed already. Check it out.

    <cfoutput>

         <cfloop query="update" >

         <tr>

              <td><input type="text" name="meeting_date#currentrow#" value="#meeting_date#" /></td>

              <td><input type="text" name="contact_first#currentrow#" value="#update.contact_first#" /></td>

              <td><input type="text" name="contact_last#currentrow#" value="#update.contact_last#" /></td>

              <td>Attended <input type="checkbox" name="attended#currentrow#" value="yes" /></td>

              <td><input type="text" name="leads#currentrow#" size="3" /></td>

         </tr>

         </cfloop>

    </cfoutput>

    <cfloop index="i" from="1" to="#update.recordcount#">

         <cfset meeting_date = form["meeting_date#i#"]>

         <cfset contact_first = form["contact_first#i#"]>

         <cfset contact_last = form["contact_last#i#"]>

         <cfset attended = form["attended#i#"]>

         <cfset leads = form["leads#i#"]>

         <cfquery datasource="#application.Database#">

              INSERT INTO quarterly (meeting_date, contact_first, contact_last, attended, leads)

              VALUES ('#meeting_date#', '#contact_first#', '#contact_last#','#attended#',leads)

         </cfquery>

    </cfloop>

    July 25, 2011

    Thanks, this script works perfectly. You are a lifesaver!

    pyae phyoe shein
    Known Participant
    July 25, 2011

    I'm glad.

    Participating Frequently
    July 25, 2011

    First of all, you can just use CFOUTPUT to do your loop:

    Of course, that assumes that all the datatypes in the database are

    character / simple string columns.

    Inspiring
    July 24, 2011

    The first thing that comes to my mind is, why do you want to insert new records instead of updating the existing ones?

    I also have an observation about your meeting_date field.  It appears to be text instead of a date.  If that's the case, it's a bad idea.

    As far as your existing code goes, the only thing drastically wrong is a lack of octothorps around the word "leads" in the values part of your insert query.