freelanceprogrammers.org Forum Index » Cold Fusion
Nested Loop Problem
Joined: 05 Apr 2005
Posts: 37
Nested Loop Problem
I am perplexed. I am working on this tool which lets you select all
records from a newly uploaded excel spreadsheet (which is
established as a datasource and then overwritten as needed), pull
field names for the table in that database that is getting the data
from the excel file, and loop through the rows from the excel file
that was uploaded and write them to the table in the database. So,
I have two nested loops. The first loop uses query results from the
uploaded excel file (in my current test case there are 2 rows of
data under a row of field headers). The second loop is a list of
field names for the table the excel file is being uploaded into.
The problem is that, although the two queries appear to be working
correctly by and large, it is just writing the same first row of
data twice to the database table which is receiving the data.
I have set a variable name after the first loop to see whether the
second row is generating at all, and it is. But it is not changing
in the second loop. Because I`m generating the field names
dynamically, I`m not sure where to go from here. I am posting the
code below for those who are brave enough to venture in... Only my
first test display changes (in outer loop). I`ve tried to
straighten the code out so it`s not too messy.. not sure how it will
display...
TIA
Jen
<cfquery name="new_tbl" datasource="#ds2#" >
SELECT * FROM [Sheet1$]
</cfquery>
<cfquery name="fields" datasource="#ds#" >
SELECT FIELD, FIELD_TYPE FROM Start_Tables WHERE
TABLE_NAME=`#form.table#`
</cfquery>
<cfset cntr=0>
<cfset cntr2=0>
<cfloop query="new_tbl">
<cfset var="">
<cfset var2="">
<cfset makesure="">
<cfset fieldset="">
<cfset values="">
<cfset value2="">
<cfset query_str="">
#new_tbl.Test# - TEST<br>
<cfset cntr=0>
<cfloop query="fields">
<cfset cntr=#cntr#+1>
<cfset var="new_tbl."&#fields.FIELD#>
<cfset var2=#Evaluate(var)#>
#var2#<br>
<cfset makesure="2">
<cfif #cntr# is 1>
<cfif #fields.FIELD_TYPE# is "num">
<cfset fieldset=#fields.FIELD#>
<cfset values=#var2#>
<cfif #isNumeric(values)#>
<cfset values=#int(values)#>
<cfelse>
<cfset values="``">
</cfif>
<cfelseif #fields.FIELD_TYPE# is "auto">
<cfset fieldset="">
<cfset values="">
<cfset cntr=0>
<cfelseif #fields.FIELD_TYPE# is "dt">
<cfset fieldset=#fields.FIELD#>
<cfset values=#var2#>
<cfif #isDate(values)#>
<cfset values=#CREATEODBCDATETIME(values)#>
<cfelse>
<cfset values="``">
</cfif>
<cfelse>
<cfset fieldset=#fields.FIELD#>
<cfset values="`#var2#`">
</cfif>
<cfelse>
<cfif #fields.FIELD_TYPE# is "num">
<cfset fieldset=#fieldset#&", "&#fields.FIELD#>
<cfset value2=#var2#>
<cfif #isNumeric(value2)#>
<cfelse>
<cfset value2="">
</cfif>
<cfset values=#preservesinglequotes(values)#&", "&#value2#>
<cfelseif #fields.FIELD_TYPE# is "auto">
<cfelseif #fields.FIELD_TYPE# is "dt">
<cfset fieldset=#fieldset#&", "&#fields.FIELD#>
<cfset value2=#var2#>
<cfif #isDate(value2)#>
<cfset value2=#CREATEODBCDATETIME(value2)#>
<cfelse>
<cfset value2="">
</cfif>
<cfset values=#preservesinglequotes(values)#&", "&#value2#>
<cfelse>
<cfset fieldset=#fieldset#&", "&#fields.FIELD#>
<cfset values=#preservesinglequotes(values)
#&", `"&#var2#&"`">
</cfif>
</cfif>
#var2#<br>
</cfloop>
<cfset query_str="INSERT INTO "&#form.table#&"("&#fieldset#&") VALUES
("&#preservesinglequotes(values)#&")">
<cfquery name="insert_recs" datasource="#ds#">
#preservesinglequotes(query_str)#
</cfquery>
</cfloop>
All times are GMT
Page 1 of 1
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Freelace Website Designer - Customer web design and software building.
Booking Calendar - reservation calendar script
Land Surveying - total station instruments and equipments
China Wholesale - Electronics Products
Character Studio - Tutorials and Help
Booking Calendar - reservation calendar script
Land Surveying - total station instruments and equipments
China Wholesale - Electronics Products
Character Studio - Tutorials and Help