insert with loop

Shawn
Shawn used Ask the Experts™
on
I'm trying to get the insert query below to loop through records but loops have never liked me.

help please
query needed to loop
        <cfloop index="i">
        <cfquery datasource="#application.DSHome#" username="#application.dbuserHome#" password="#application.dbpassHome#">
        INSERT INTO tblATPeopleCauses(CausePhrase, ATPeopleLanguageID, PhraseID, MemberID, CauseVersion)
		VALUES   (	<CFQUERYPARAM cfsqltype="cf_sql_varchar" value="#form.CausePhrase#">,
        			<CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form.lid#">,
                    <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form.PhraseID#">, 
                    <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#session.memberID#">,
                    <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form.CauseVersion#">
                    )
		</cfquery>
        </cfloop>
 
 
from fields in a loop. seems to work fine
<cfoutput>
<cfloop query="MasterCause">
  <cfloop  query="MasterVariables">
    <cfif MasterCause.CauseID neq 1>
      <cfif MasterVariables.VariableID eq 1>
           <tr>
            <td></td>
    		<td></td>
    		<td> <span class="blue">#MasterCause.CausePhrase#</span></td>
            <td></td>
           </tr>
           <tr>
            <td></td>
    		<td></td>
    		<td> <span class="blue">
            <input name="CausePhrase" id="CausePhrase" type="text" class="formcellwthBg" size="40" value="#getCause.CausePhrase#"></span>
            <input type="Hidden" name="CauseVersion" id="CauseVersion" value="#MasterCause.CauseVersion#">V#MasterCause.CauseVersion#
            </td>
            <td></td>
           </tr>
     </cfif>
  	</cfif>
  </cfloop>
</cfloop>
</cfoutput>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Not sure what your trying to do, why do you want to insert multiple times

A look will normally look like

<cfloop index="i" from="1" to="10">

which will repeat the code 10 times, and i will be the current time, ie 1 2 3 4 etc

Author

Commented:
it is for a translation project. In the form the field "CausePhrase" loops 7 times because there are 7 causes. The form input loops with it so the translator can translate the 7 causes beside the original (aka Master) cause. Once these are filled in I need his/her filled in CausePhrase to be inserted in the table. 1 per line.

hope this helps

Author

Commented:
ok in the loop around the insert I put <cfloop index="i" from="1" to="7">
this looks better but I am now getting the error:
Invalid data '' for CFSQLTYPE CF_SQL_INTEGER. for the following line:
<CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form.CauseVersion#">

I'm thinking in the form loop the id or name is not unique so it is not working. Shouldn't we give it an id via looping and then pass this to the insert? not sure how to do this though

Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

I think this may work....

*NOTE i moved your loop within the cfquery tag, no need to make each insert a trip to the database and back. Run them all at one time. (I think this is cross DBMS supported.. but most of my time is spent with MSSQL)

        <cfquery datasource="#application.DSHome#" username="#application.dbuserHome#" password="#application.dbpassHome#">
        <cfloop from="1" to="#arrayLen(form.CauseVersion)#" index="i">
        INSERT INTO tblATPeopleCauses(CausePhrase, ATPeopleLanguageID, PhraseID, MemberID, CauseVersion)
                VALUES   (      <CFQUERYPARAM cfsqltype="cf_sql_varchar" value="#form.CausePhrase[i]#">,
                                <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form.lid[i]#">,
                    <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form.PhraseID[i]#">, 
                    <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#session.memberID#">,
                    <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form.CauseVersion[i]#">
                    )
        </cfloop>
        </cfquery>

Open in new window

<CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form.CauseVersion[i]#">

Author

Commented:
I'm getting this error now
Object of type class java.lang.String cannot be used as an array
with this line
<cfloop from="1" to="#arrayLen(form.CauseVersion)#" index="i">

if I hard code the first line of the loop to <cfloop from="1" to="7" index="i"> I get this error:
You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members.
for <CFQUERYPARAM cfsqltype="cf_sql_varchar" value="#form.CausePhrase[i]#">
right.. probably because you aren't passing seven form items with the same name.

You should do a <cfdump var="#form#"/><cfabort/> above the loop and see what you are passing.

Author

Commented:
ok tried <cfdump var="#form#"/><cfabort/> and am getting

The requested template has been denied access to createobject(java)
are you using createObject anywhere?

Author

Commented:
don't think so. It's poiting to the dump line.

also tried
<cfdump var="#form.CausePhrase#"/><cfabort/>

same problem

Author

Commented:
I'm working from home right now and am doing this over the internet without debugging. My office is 20 min away and has debugging locally. I'm going to hop over there to see if we can't get a little more nfo.
maybe
<cfdump var="#form#"><cfabort>
Sorry... its a list.. not array...

 <cfquery datasource="#application.DSHome#" username="#application.dbuserHome#" password="#application.dbpassHome#">
        <cfloop from="1" to="#listLen(form.CauseVersion)#" index="i">
        INSERT INTO tblATPeopleCauses(CausePhrase, ATPeopleLanguageID, PhraseID, MemberID, CauseVersion)
                VALUES   (      <CFQUERYPARAM cfsqltype="cf_sql_varchar" value="#listgetAt(form.CausePhrase,i)#">,
                                <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#listgetAt(form.lid,i)#">,
                    <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#listgetAt(form.PhraseID,i)#">, 
                    <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#session.memberID#">,
                    <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#listgetAt(form.CauseVersion,i)#">
                    )
        </cfloop>
        </cfquery>

Open in new window

Most Valuable Expert 2015

Commented:
> ok tried <cfdump var="#form#"/><cfabort/> and am getting
> The requested template has been denied access to createobject(java)

I seem to recall that cfdump does require createobject permission.  It must use it behind the scenes.  So if you do not have access to createObject (like in a shared hosting environ) you may not be able to use cfdump.  A bit annoying really.  

As an alternative, you could loop through the FORM collection and <cfoutput> the values.  At least the "simple" ones, which should be fine for a FORM.








<cfoutput>
<cfloop collection="#FORM#" item="field">
	<cfset value = FORM[field]>
	Field name = #field#: 
	<cfif IsSimpleValue(value)>
		#value#
	<cfelse>
		(complex)
	</cfif><br>
</cfloop>
</cfoutput>

Open in new window

Most Valuable Expert 2015

Commented:
I would recommend dynamically naming the form fields, instead of giving them all the same name. The problem with using the same name for all fields is the values will be passed as a comma delimited list.   So if any of the field "values" contain a comma, you won't be able to correctly extract the values.

You can use a counter to dynamically name each set of fields in sequence.  The resulting field names would be:

     form.CausePhrase1,  form.CauseVersion1
     form.CausePhrase2,  form.CauseVersion2
     ...
    form.CausePhrase7,  form.CauseVersion7

Store the total number of fields (ie 7) in a hidden field.  Then on the action page, loop from 1 to #totalNumberOfFields#, and insert each value.

NOTE:  I do not know if your other fields (PhraseID, form.lid, ...) need to be dynamically named as well. So you may need to update the example.



<cfoutput>
<cfset fieldCounter = 0>	
<cfloop query="MasterCause">
  <cfloop  query="MasterVariables">
    <cfif MasterCause.CauseID neq 1>
      <cfif MasterVariables.VariableID eq 1>
			<cfset fieldCounter = fieldCounter + 1>
            <input name="CausePhrase" id="CausePhrase#fieldCounter#" type="text" class="formcellwthBg" size="40" value="#getCause.CausePhrase#"></span>
            <input type="Hidden" name="CauseVersion#fieldCounter#" id="CauseVersion" value="#MasterCause.CauseVersion#">V#MasterCause.CauseVersion#
     	</cfif>
     </cfif>
  </cfloop>
</cfloop>
<input type="hidden" name="numOfFields" value="#fieldCounter#">
</cfoutput>
 
ACTION PAGE
<cfparam name="form.numOfFields" default="0">
<!--- loop through each of the dynamically named form fields --->
<cfloop from="1" to="#form.numOfFields#" index="counter">
      <cfquery datasource="#application.DSHome#" username="#application.dbuserHome#" password="#application.dbpassHome#">
        INSERT INTO tblATPeopleCauses(CausePhrase, ATPeopleLanguageID, PhraseID, MemberID, CauseVersion)
        VALUES   
		(      
			<CFQUERYPARAM cfsqltype="cf_sql_varchar" value="#form['CausePhrase'& counter]#">,
            <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form.lid#">,
            <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form.PhraseID#">, 
            <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#session.memberID#">,
            <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form['CauseVersion'& counter]#">
            )
		</cfquery>
</cfloop>

Open in new window

Most Valuable Expert 2015

Commented:
> <cfloop query="MasterCause">
>  <cfloop  query="MasterVariables">

Though it is not really part of your question, I am wondering if the the nested query loops are needed, or if it could be combined into one query.  

Author

Commented:
> <cfloop query="MasterCause">
>  <cfloop  query="MasterVariables">
these are from different tables. sure we could look at it.
just setting up code in your previous post

Author

Commented:
getting this error now
Element CausePhrase1 is undefined in a Java object of type class coldfusion.filter.FormScope.
referring to this line
<CFQUERYPARAM cfsqltype="cf_sql_varchar" value="#form['CausePhrase'& counter]#">,

in the debuggnig part I can see

Form Fields:
CAUSEPHRASE=testtext2,testtext3,testtext4,testtext5,testtext6,testtext7
CAUSEVERSION=
CAUSEVERSION1=2
CAUSEVERSION2=3
CAUSEVERSION3=4
CAUSEVERSION4=5
CAUSEVERSION5=6
CAUSEVERSION6=7

Author

Commented:
just uploading files so you can see better what is going in. The code we're looking at in the dsp file starts on line 364 and 81 for the act file. Had to change the extensions to txt from cfm to upload.
files.zip
Most Valuable Expert 2015

Commented:
My bad.  Change this line:

<input name="CausePhrase" id="CausePhrase#fieldCounter#" type="text" class="formcellwthBg" size="40" value="#getCause.CausePhrase#"></span>

To this:
           <input id="CausePhrase" name="CausePhrase#fieldCounter#" type="text" class="formcellwthBg" size="40" value="#getCause.CausePhrase#"></span>

Author

Commented:
well with that it looks like it is trying to insert. now I'm getting this error:
Invalid data '' for CFSQLTYPE CF_SQL_INTEGER.
for this line:
<CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form['CauseVersion'& counter]#">

it shouldn't be blank though??
Most Valuable Expert 2015

Commented:
Check the debugging output. What are the values of the CauseVersion fields? Also do they match what you input in the form?



Author

Commented:
ok, dump helped there.
now I'm getting
You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members.
for this
<CFQUERYPARAM cfsqltype="cf_sql_varchar" value="#form.CausePhrase[i]#">
Most Valuable Expert 2015

Commented:
I do not think that was in the example.  But the syntax needs to be more like this:
         
     <CFQUERYPARAM cfsqltype="cf_sql_varchar" value="#form['CausePhrase'& i]#">  

      ie:   #form['FieldNameWithCounterNumber']#

When you use #form.CausePhrase[i]#, you are telling CF that #form.CausePhrase# is an array or structure object (which it is not).  So that is why you are getting the error.

Author

Commented:
yep, sorry fixed that. some old trial code. now I know the why though

I think I've found something while using the dump
The PhrasID is empty. Logically it couldn't be added to CFSQLTYPE CF_SQL_INTEGER

I based the PhraseID unwittingly on the form. That's only good for an update because on the insert it hasn't been created yet.

So looking back a the insert query I need to retrieve it from the insert query above it rather than the form. See the 2 queries below

What would be the easiest way to retrive the PhraseID from the first query? In this table PhraseID is the PK.
<cfquery datasource="#application.DSHome#" username="#application.dbuserHome#" password="#application.dbpassHome#">
        INSERT INTO tblATPeoplePhrases(Part1, Part2A, Part2B, Part2C, ATPeopleLanguageID, MemberID, TranslatorTestComments)
		VALUES   (	<CFQUERYPARAM cfsqltype="cf_sql_varchar" value="#form.Part1#">,
        			<CFQUERYPARAM cfsqltype="cf_sql_varchar" value="#form.Part2A#">,
                    <CFQUERYPARAM cfsqltype="cf_sql_varchar" value="#form.Part2B#">, 
                    <CFQUERYPARAM cfsqltype="cf_sql_varchar" value="#form.Part2C#">,
                    <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form.lid#">, 
                    <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#session.memberID#">, 
                    <CFQUERYPARAM value="#form.TranslatorTestComments#"> 
                    )
		</cfquery>
        
        
        <cfparam name="form.numOfFields" default="0">
<!--- loop through each of the dynamically named form fields --->
<cfloop from="1" to="#form.numOfFields#" index="counter">
      <cfquery datasource="#application.DSHome#" username="#application.dbuserHome#" password="#application.dbpassHome#">
        INSERT INTO tblATPeopleCauses(CausePhrase, ATPeopleLanguageID, PhraseID, MemberID, CauseVersion)
        VALUES   
		(      
			<CFQUERYPARAM cfsqltype="cf_sql_varchar" value="#form['CausePhrase'& counter]#">,
            <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form.lid#">,
            <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form.PhraseID#">, 
            <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#session.memberID#">,
            <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form['CauseVersion'& counter]#">
            )
		</cfquery>
</cfloop>

Open in new window

Most Valuable Expert 2015
Commented:
If PhraseID is an identity column, you can use cfquery's result attribute.  It populates a structure with details about the query.  For simple inserts, it also returns the new ID value.  The name used, depends on your database.  For sql server it is:  result_name.GENERATED_KEY

So add the result attribute to the first query:

     <cfquery result="newPhrase"  ....>
        ....
      </cfquery>

Then use the id value in the second query:

<cfquery ...>
        INSERT INTO tblATPeopleCauses(....)
        VALUES  
         (      
           ....
           <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#newPhrase.GENERATED_KEY#">,
            .....
         )
</cfquery>

Author

Commented:
just got it to insert!

added query below to get phrase id.

the data is in the db now but it doesn't want to show up. Would I need to do something similar as the insert loop to make the form names unique? If I did this it might break the insert loop...or just replace it
        <cfquery name="getNewPhraseID" datasource="#application.DSHome#" username="#application.dbuserHome#" password="#application.dbpassHome#">
		SELECT     PhraseID
		FROM       tblATPeoplePhrases
		WHERE     (memberID = <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#session.memberID#">)
		AND 	  (ATPeopleLanguageID = <CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form.lid#">)
</cfquery>

Open in new window

Author

Commented:
didn't know that was possible. I prefer yours. +  yes it's sql server 2005
Most Valuable Expert 2015

Commented:
Yes, "result" was one of the nicer (and eagerly anticipated) additions to CF8 :)
Most Valuable Expert 2015

Commented:
> the data is in the db now but it doesn't want to show up.

On which part?

Author

Commented:
it inserts into tblATPeopleCauses. I can see the rows added  in in Management Studio.

On the form however when it reloads the fields are blank

this for example is blank
<input id="CausePhrase" name="CausePhrase#fieldCounter#" type="text" class="formcellwthBg" size="40" value="#getCause.CausePhrase#">
Most Valuable Expert 2015

Commented:
1. First check the getCause query. Dump the query and/or the recordCount.  See if it is even returning any data for the criteria you are using.  It may not be

2. Second, even if it were returning data, I am not sure that code is right to begin with.  When you use #getCause.CausePhrase#, CF will output the value in the first row of the query.  If #getCause# is supposed to contain multiple records, you have to loop through it to display them all.

Author

Commented:
1 checked and yes it is returning the records

2 I thought it was already in the loop...but your right. It's in a loop but ont its own. hmmm I better have a think about this. be right back

Author

Commented:
ok I managed to get the code below to display nicely but it only works IF data exists.

How would I construct an if else structure in the code to show a blank cell if no data?
<cfoutput>
  <cfset fieldCounter = 0>    
  <cfloop query="MasterCause">
    <cfloop  query="getCause">
      <cfif MasterCause.CauseVersion neq 1>
        <cfif getCause.CauseVersion eq MasterCause.CauseVersion>
          
		  <cfset fieldCounter = fieldCounter + 1>
                    
          <input id="CausePhrase" name="CausePhrase#fieldCounter#" type="text" class="formcellwthBg" size="40" value="#getCause.CausePhrase#"></span><br>
          <input type="Hidden" name="CauseVersion#fieldCounter#" id="CauseVersion#fieldCounter#"
          value="<cfif isDefined("CauseVersion")>#getCause.CauseVersion#<cfelse>#MasterCause.CauseVersion#</cfif>">
          
          </cfif>
        </cfif>
      </cfloop>
    </cfloop>
  <input type="hidden" name="numOfFields" value="#fieldCounter#">
</cfoutput>

Open in new window

Most Valuable Expert 2015

Commented:
It depends on how you are defining "no data".  You have a few nested loops there, so help us out ;-) What conditions equal "no data" (no records in masterCause, no records in getCause, etc..)

Author

Commented:
i think yo ualready more than answered the question. I'm going to open another one for help with the loop and give you an A+ for this one. I'll post the new question heree so you follow it.

Couldn't have gone this far without you. thanks again _agx_:
Most Valuable Expert 2015

Commented:
Always glad to help :)

Author

Commented:
to answer your question...no records in getCause,

if in masterCause versions are 1,2,3,4,5,6,7 and in getCause versions are no records I need the same number of form cells as in masterCause

It's for translating...maybe an example will help

english to french example
masterCause        |  getCause
Good cause          |  (empty) but still need a form cell for the tranlator to fill in the blanks
Bad cause            | Mauvaise cause
not so bad cause | Cause pas si mal

Author

Commented:
here is the question. I tried to make it a little clearer :)
http://www.experts-exchange.com/index.jsp?qid=24447075

hope you can help.

thanks again

Author

Commented:
good solution. I like the feature. Will come in really handy

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial