insert with loop

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

LVL 1
ShawnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PluckaCommented:
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
0
ShawnAuthor 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
0
ShawnAuthor 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

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Andrew MaurerCommented:
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

0
Andrew MaurerCommented:
<CFQUERYPARAM cfsqltype="cf_sql_integer" value="#form.CauseVersion[i]#">
0
ShawnAuthor 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]#">
0
Andrew MaurerCommented:
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.
0
ShawnAuthor Commented:
ok tried <cfdump var="#form#"/><cfabort/> and am getting

The requested template has been denied access to createobject(java)
0
Andrew MaurerCommented:
are you using createObject anywhere?
0
ShawnAuthor Commented:
don't think so. It's poiting to the dump line.

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

same problem
0
ShawnAuthor 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.
0
Andrew MaurerCommented:
maybe
<cfdump var="#form#"><cfabort>
0
Andrew MaurerCommented:
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

0
_agx_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

0
_agx_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

0
_agx_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.  
0
ShawnAuthor 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
0
ShawnAuthor 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
0
ShawnAuthor 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
0
_agx_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>
0
ShawnAuthor 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??
0
_agx_Commented:
Check the debugging output. What are the values of the CauseVersion fields? Also do they match what you input in the form?



0
ShawnAuthor 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]#">
0
_agx_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.
0
ShawnAuthor 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

0
_agx_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>

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShawnAuthor 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

0
ShawnAuthor Commented:
didn't know that was possible. I prefer yours. +  yes it's sql server 2005
0
_agx_Commented:
Yes, "result" was one of the nicer (and eagerly anticipated) additions to CF8 :)
0
_agx_Commented:
> the data is in the db now but it doesn't want to show up.

On which part?
0
ShawnAuthor 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#">
0
_agx_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.
0
ShawnAuthor 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
0
ShawnAuthor 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

0
_agx_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..)

0
ShawnAuthor 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_:
0
_agx_Commented:
Always glad to help :)
0
ShawnAuthor 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
0
ShawnAuthor 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
0
ShawnAuthor Commented:
good solution. I like the feature. Will come in really handy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.