Skip to main content
Inspiring
April 7, 2011
Answered

Insert multiple rows with dynamic data

  • April 7, 2011
  • 1 reply
  • 2204 views

Hi,
When I do an insert into a table that has multiple rows of dynamic info. on
a page, it inserts all the rows that are dynamically populated. I only want it
to insert the rows that I choose the initials on. How can I do that? Let's say I
have 5 items that are displayed on a page and I only want to insert initials for
the first 2 because the last 3 are left blank, how do I do that? I've tried
setting up an if statement that says:

<cfif "form.PNR_Approval_Initials#aid#" neq "">

Then do the inserting into the table

<cfelse>
</cfif>

I also have all of this set up in a loop that says:

<cfloop index="aid" list="#form.listofids#" delimiters=",">

Below is the code for just the inserting. I also have some code on here to
update another table. I got that to work, so I'm just trying to get the insert
working correctly for right now. I cannot use this where statement in the query:
Where ItemID=#id# because I am trying to insert into the Approval table, not
the Items table. The items table is just being updated with some other info. in
some other input boxes.

<cfloop index="aid" list="#form.listofids#" delimiters=",">
<cfif "form.PNR_Approval_Initials#aid#" neq "">

<cfquery Datasource="#application.DataSource#">
Insert Into Approval (APV_ItemID,
                              APV_ECID,
                              Document_Type,
                              Approval_Initials)

Values ('#Evaluate("form.ItemID#aid#")#',
'#Evaluate("form.ECID#aid#")#',
<cfif isDefined ("form.PNR_Doc_Type#aid#") and
"form.PNR_Doc_Type#aid#" neq "">
'#Evaluate("form.PNR_Doc_Type#aid#")#',
<cfelse>
Null,
</cfif>

<cfif isDefined ("form.PNR_Approval_Initials#aid#") and
"form.PNR_Approval_Initials#aid#" neq "">
'#Evaluate("form.PNR_Approval_Initials#aid#")#'
<cfelse>
Null
</cfif>)

</cfquery>

</cfif>
</cfloop>

On the display page, it just has a table of the dynamic info. These are the 4
things I have on this page that should get inserted into the database.

<input type="hidden" name="ECID#ItemID#" value="#ECID#">
<input type="hidden" name="ItemID#ItemID#" value="#ItemID#">
<input type="hidden" name="PNR_Doc_Type#ItemID#" value="PNR Req">

<td align="center">
<cfif Approval_Initials Is Not "">
#Approval_Initials#
<cfelse>

<select name="PNR_Approval_Initials#ItemID#">
<option value="">Select Initials</option>
<cfloop query="ShowInitials">
<cfif Engineer_Initials EQ 1>
<option value="#Initials#">#Initials#</option>
</cfif>
</cfloop>
</select>

</cfif>
</td>

Can anyone please help me on just the inserting into the database so that it
only inserts on the rows I choose the initials on and not the others? Thanks.

Andy

    This topic has been closed for replies.
    Correct answer Owainnorth

    You've nearly got it, but I see you've commented it out.

    <cfif structKeyExists(form, "PNR_Approval_Initials#aid#") AND form["PNR_Approval_Initials#aid#"] NEQ "" >

      Do stuff here

    </cfif>

    1 reply

    Owainnorth
    Inspiring
    April 7, 2011

    <cfif "form.PNR_Approval_Initials#aid#" neq ""> will never work, as you're creating a string with a constant inside it. Use dot notation instead:

    <cfif form["PNR_Approval_Initials#aid#"] neq "" >

    Inspiring
    April 7, 2011

    Owain,

         Thanks. This works on the first time that I try inserting this data, but if I go back to the link again later and try and update the other remaining initials that I didn't fill out yet, I get an error. Here's what it says:

    Element PNR_Approval_Initials1 is undefined in a Java object of type class coldfusion.filter.FormScope referenced as

      The error occurred in F:\prot_intra\ECO_Register\Queue_PNR_BOM_action.cfm: line 24

    22 : <cfloop index="aid" list="#form.listofids#" delimiters=",">

    23 : <!--- <cfif isDefined("form.PNR_Approval_Initials#aid#")> --->

    24 : <cfif form["PNR_Approval_Initials#aid#"] neq "">

    25 :

    26 : <cfquery Datasource="#application.DataSource#">

    What does this mean? Why would this not work again?

    Andy

    Owainnorth
    OwainnorthCorrect answer
    Inspiring
    April 7, 2011

    You've nearly got it, but I see you've commented it out.

    <cfif structKeyExists(form, "PNR_Approval_Initials#aid#") AND form["PNR_Approval_Initials#aid#"] NEQ "" >

      Do stuff here

    </cfif>