Insert Multiple form records into DB

[futureman]

I have a form that populates with data from several queries and calcs, it
creates about 70 rows of form data. I need for the form fields to be editable
and then submit the form with all 70 rows allowing the 70 seperate records to
be inserted into my Access DB as seperate records. I have searched the forum
and I found one way using an index list loop but it was only for a single
variable, I have about 7 variables per insert/row based on the clientID. I am
thinking that I may need to use a structure or array but I'm not sure how. Any
help would be appreciated. I have attached what the form records look like
after populating, I need these to be inserted into seperate records at the same
time.
thanks again

Futureman



<td><input type="text" value="274" name="clientID" size="4" readonly
class="ro"></td>

<td><input type="text" size="4" name="projectiD" value="756"
class="ro"></td>
<td><input type="text" name="noteid" value="63" size="3"
class="ro"></td>
<td><input type="text" name="datedeposited" value="6/1/2005" size="3"
class="ro"></td>
<td><input type="text" name="dateposted" value="07/01/2005"
size="10"></td>
<td><input type="text" name="interestrate" value="0.11" size="8"
class="ro"></td>
<td><input type="text" name="sumtotal" value="$10" size="8"
class="ro"></td>


<td><input type="text" value="2" name="clientID"size="4" readonly class="ro">
<td><input type="text" size="4" name="projectiD" value="756"
class="ro"></td>
<td><input type="text" name="noteid" value="63" size="3"
class="ro"></td>
<td><input type="text" name="datedeposited" value="6/1/2005" size="3"
class="ro"></td>
<td><input type="text" name="dateposted" value="07/01/2005"
size="10"></td>
<td><input type="text" name="interestrate" value="0.11" size="8"
class="ro"></td>
<td><input type="text" name="sumtotal" value="$20" size="8"
class="ro"></td>

[futureman]

I have a form that populates with data from several queries and calcs, it
creates about 70 rows of form data. I need for the form fields to be editable
and then submit the form with all 70 rows allowing the 70 seperate records to
be inserted into my Access DB as seperate records. I have searched the forum
and I found one way using an index list loop but it was only for a single
variable, I have about 7 variables per insert/row based on the clientID. I am
thinking that I may need to use a structure or array but I'm not sure how. Any
help would be appreciated. I have attached what the form records look like
after populating, I need these to be inserted into seperate records at the same
time.
thanks again

Futureman

<td><input type="text" value="274" name="clientID" size="4" readonly
class="ro"></td>

<td><input type="text" size="4" name="projectiD" value="756"
class="ro"></td>
<td><input type="text" name="noteid" value="63" size="3"
class="ro"></td>
<td><input type="text" name="datedeposited" value="6/1/2005" size="3"
class="ro"></td>
<td><input type="text" name="dateposted" value="07/01/2005"
size="10"></td>
<td><input type="text" name="interestrate" value="0.11" size="8"
class="ro"></td>
<td><input type="text" name="sumtotal" value="$10" size="8"
class="ro"></td>


<td><input type="text" value="2" name="clientID"size="4" readonly class="ro">
<td><input type="text" size="4" name="projectiD" value="756"
class="ro"></td>
<td><input type="text" name="noteid" value="63" size="3"
class="ro"></td>
<td><input type="text" name="datedeposited" value="6/1/2005" size="3"
class="ro"></td>
<td><input type="text" name="dateposted" value="07/01/2005"
size="10"></td>
<td><input type="text" name="interestrate" value="0.11" size="8"
class="ro"></td>
<td><input type="text" name="sumtotal" value="$20" size="8"
class="ro"></td>
thanks!

[mxstu]

Use dynamically named form fields. By appending a row number to each set of
fields, your form field names should look like this ..

projectiD1
noteid1
datedeposited1
....

projectiD2
noteid2
datedeposited2
...

Then on your action page, loop from 1 to the total number of rows and use a
cfquery to insert each set of records into your database table. This example
needs validation but should demonstrate the idea.





<!--- form page --->
<form method="post" action="yourActionPage.cfm">
<table>
<cfoutput query="yourQuery">
<tr>
<!--- append "CurrentRow" number to field names. this will group each set of
fields together --->
<td><input type="text" size="4" name="projectID#CurrentRow#"
value="#projectID#" class="ro"></td>
<td><input type="text" name="noteid#CurrentRow#" value="#noteID#" size="3"
class="ro"></td>
<td><input type="text" name="datedeposited#CurrentRow#"
value="#datedeposited#" size="3" class="ro"></td>
<td><input type="text" name="dateposted#CurrentRow#" value="#dateposted#"
size="10"></td>
<td><input type="text" name="interestrate#CurrentRow#" value="#interestrate#"
size="8" class="ro"></td>
<td><input type="text" name="sumtotal#CurrentRow#" value="#sumtotal#"
size="8" class="ro"></td>
</tr>
</cfoutput>
</table>

<cfoutput>
<!--- must store total number of rows. this number is used on action page --->
<input type="hidden" name="numberOfRows" value="#yourQuery.RecordCount#">
</cfoutput>
<input type="submit">
</form>

<!--- action page --->
<cfoutput>
<!--- show the form values for each row --->
<cfloop from="1" to="#form.numberOfRows#" index="row">
<cfset projectID = form["projectID"& row]>
<cfset noteID = form["noteID"& row]>
<cfset dateposted = form["dateposted"& row]>
<cfset datedeposited = form["datedeposited"& row]>
<cfset interestrate = form["interestrate"& row]>
<cfset sumtotal = form["sumtotal"& row]>
<!--- ** perform any form field validation ** ... --->

<!--- ** create your cfquery insert here ** --->
<b>show values for form row [#row#]: </b> #projectID#, #noteID#,
#dateposted#, #datedeposited#, #interestrate#, #sumtotal#<br>
</cfloop>
</cfoutput>

[mxstu]

See [url]http://www.macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=3&threadid=1020558&enterthre ad=y[/url]

[futureman]

MXSTU,

Thanks for your post - I really appreciate it.

Here is what I have now based on your post,
<cfoutput>
<cfloop from="1" to="#form.numOfRows#" index="row">
<cfset dont_post = form["dont_post" & row]>
<cfset dont_post_prin = form["dont_post_prin" & row]>
<cfset clientID = form["clientID"& row]>
<cfset debit = form["debit"& row]>
<cfset prin_reduction2 = form["prin_reduction2" & row]>
<cfset projectID = form["projectID"& row]>
<cfset noteID = form["noteID"& row]>
<cfset dateposted = form["dateposted"& row]>
<cfset datedeposited = form["datedeposited"& row]>
<cfset interestrate = form["interestrate"& row]>
<cfset sumtotal = form["sumtotal"& row]>
<!--- ** perform any form field validation ** ... --->

<!--- ** create your cfquery insert here ** --->
<b>show values for form row [#row#]: </b> #projectID#, #noteID#,
#dateposted#, #datedeposited#, #interestrate#, #sumtotal#<br>
</cfloop>
</cfoutput>

I added the currentrow variables to the form field names but I get an error
saying "
The value
************************

"1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 ,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 ,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 ,1,1,1,1,1,1,1,1,1,1,1"
cannot be converted to a number
***************************************

Any thoughts?

[mxstu]

I'm not clear on where that error is coming from. Can you post your form code and your action page code. Also, do you understand what the sample code is doing? If it is not clear, let me know.

[futureman]

Ok, here is the form:
<cfquery here...>
<cfoutput query=...>
<tr>
<td><cfif #get_date.firstname# EQ "."><cfelse>#get_date.firstname#
</cfif>#get_date.lstname#</td>
<td><input type="text" value="#clientID#" name="clientID#currentrow#"
size="4" readonly class="ro"></td>
<td><input type="text" value="#dollarformat(total_to_post)#"
name="debit#currentrow#" readonly size="8"></td>
<td><input type="text" name="prin_reduction2#currentrow#"
value="#numberformat(prin_reduction1, "0.00")#" size="8"></td>
<td><input type="text" size="4" name="projectiD#currentrow#"
value="#get_date.ProjectID#" class="ro"></td>
<td><input type="text" name="noteid#currentrow#" value="#noteID#"
size="3" class="ro"></td>
<td><input type="text" name="datedeposited#currentrow#"
value="#dateformat(lastdate.dateposted, "m/d/yyyy")#" size="3" class="ro"></td>
<td><input type="text" name="dateposted#currentrow#"
value="#form.dateto2#" size="10"></td>
<td><input type="text" name="interestrate#currentrow#"
value="#creds_minus_debs.interestrate#" size="8" class="ro"></td>
<td><input type="text" name="sumtotal#currentrow#"
value="#dollarformat(creds_minus_debs.sumtotal)#" size="8" class="ro"></td>
<td><input type="checkbox" name="dont_post#currentrow#"
value="#get_date.lendorid#"></td>
<td><input type="checkbox" name="dont_post_prin#currentrow#"
value="#get_date.lendorid#"></td>
</tr></cfoutput>
<input type="hidden" name="numOfRows"
value="#creds_minus_debs.RecordCount#">

Here is the action page
<cfoutput>
<cfloop from="1" to="#form.numOfRows#" index="row">
<cfset dont_post = form["dont_post" & row]>
<cfset dont_post_prin = form["dont_post_prin" & row]>
<cfset clientID = form["clientID"& row]>
<cfset debit = form["debit"& row]>
<cfset prin_reduction2 = form["prin_reduction2" & row]>
<cfset projectID = form["projectID"& row]>
<cfset noteID = form["noteID"& row]>
<cfset dateposted = form["dateposted"& row]>
<cfset datedeposited = form["datedeposited"& row]>
<cfset interestrate = form["interestrate"& row]>
<cfset sumtotal = form["sumtotal"& row]>
<!--- ** perform any form field validation ** ... --->

<!--- ** create your cfquery insert here ** --->
<b>show values for form row [#row#]: </b> #projectID#, #noteID#,
#dateposted#, #datedeposited#, #interestrate#, #sumtotal#<br>
</cfloop>
</cfoutput>

If i understand correctly, the sample code is making each form field unique,
then on the action page it strips the currentrow number to leave the original
name of the form variable and then inserts it into a db. I admit however, that
I am not real familiar with index looping over lists....

thanks again.
Futureman

[mxstu]

It looks like you're using serveral different queries within your form page,
which may be causing a problem with the "CurrentRow" number, but it's hard to
tell without the complete code. Can you update your previous post and include
the complete code for both pages and the exact error message and which page it
occurred on (the form page.. or the action page)? Obviously minus any
passwords or confidential info. ;-)