Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Updating Multiple Rows with CFINSERT
- Thread starter tempoman
- Start date
TEMPLATE ONE:
<!--- Get many rows of data --->
<cfquery name="qryCustomer" datasource="aquatrac">
Select *
From tblCustomer
</cfquery>
<!--- Start a form out side a loop --->
<form action="rma.cfm" method="post">
<!--- Set a hidden variable with the amount of records shown --->
<cfoutput><input type="Hidden" name="RecordCount" value="#qryCustomer.RecordCount#"></cfoutput>
<!--- Start output INSIDE form tags. Lets say we have three form fields: ID, Desc, Status.
We name the form fields based off of what row they are in. If we have three rows, our fields would be:
id1, Desc1, Status1
id2, Desc2, Status2
id3, Desc3, Status3
--->
<cfoutput query="qryCustomer">
<input type="Hidden" name="id#CurrentRow#" value="#id#">
<input type="text" name="Desc#CurrentRow#" value="#Desc#">
<input type="text" name="Status#CurrentRow#" value="#Status#">
</cfoutput>
<!--- Have a single submit button outside the loop --->
<input type="Submit" value="Save All">
</form>
TEMPLATE TWO (Insert or Update Records):
<!--- Run loop: one for every row outputed earlier --->
<cfloop index="i" from="1" to="#form.RecordCount#">
<!---
Now all we need to do is create variables within the loop that is composed of a prefix ("form.formfield"
and a suffix (i).
It is important to note that the prefix is a static string value (Notice in the code it is wrapped in quotes. This means that the
variable is assigned a literal value of form.whatever. The suffix however is a dynamic value. The value will be equal to the loop number.
So the first time that this loop is fired, the locID variable will be given the value of form.ID1. The possible confusing part at this point is
that locID is simply equal to the string form.ID1. But that string value happens to be the name of the form field that we are trying to evaluate.
So all we have to do at this point is use the evaluate function to give us the value of the form field form.ID1.
The main 'trick' in this script is assigning a literal string value that is the same as the form field that you want to evaluate.
--->
<cfset locID = "form.ID" & i>
<cfset locDesc = "form.Desc" & i>
<cfset locStatus = "form.Status" & i>
<!--- Note: All dynamic form variables are here evaluated. --->
<cfquery name="qryAddList#i#" datasource="aquatrac">
INSERT INTO tblList
(ID, Desc, Status)
VALUES
('#Evaluate(locID)#', '#Evaluate(locDesc)#', '#Evaluate(locStatus)#')
</cfquery>
<!--- Loop to next row --->
</cfloop>
<!--- Get many rows of data --->
<cfquery name="qryCustomer" datasource="aquatrac">
Select *
From tblCustomer
</cfquery>
<!--- Start a form out side a loop --->
<form action="rma.cfm" method="post">
<!--- Set a hidden variable with the amount of records shown --->
<cfoutput><input type="Hidden" name="RecordCount" value="#qryCustomer.RecordCount#"></cfoutput>
<!--- Start output INSIDE form tags. Lets say we have three form fields: ID, Desc, Status.
We name the form fields based off of what row they are in. If we have three rows, our fields would be:
id1, Desc1, Status1
id2, Desc2, Status2
id3, Desc3, Status3
--->
<cfoutput query="qryCustomer">
<input type="Hidden" name="id#CurrentRow#" value="#id#">
<input type="text" name="Desc#CurrentRow#" value="#Desc#">
<input type="text" name="Status#CurrentRow#" value="#Status#">
</cfoutput>
<!--- Have a single submit button outside the loop --->
<input type="Submit" value="Save All">
</form>
TEMPLATE TWO (Insert or Update Records):
<!--- Run loop: one for every row outputed earlier --->
<cfloop index="i" from="1" to="#form.RecordCount#">
<!---
Now all we need to do is create variables within the loop that is composed of a prefix ("form.formfield"
and a suffix (i).It is important to note that the prefix is a static string value (Notice in the code it is wrapped in quotes. This means that the
variable is assigned a literal value of form.whatever. The suffix however is a dynamic value. The value will be equal to the loop number.
So the first time that this loop is fired, the locID variable will be given the value of form.ID1. The possible confusing part at this point is
that locID is simply equal to the string form.ID1. But that string value happens to be the name of the form field that we are trying to evaluate.
So all we have to do at this point is use the evaluate function to give us the value of the form field form.ID1.
The main 'trick' in this script is assigning a literal string value that is the same as the form field that you want to evaluate.
--->
<cfset locID = "form.ID" & i>
<cfset locDesc = "form.Desc" & i>
<cfset locStatus = "form.Status" & i>
<!--- Note: All dynamic form variables are here evaluated. --->
<cfquery name="qryAddList#i#" datasource="aquatrac">
INSERT INTO tblList
(ID, Desc, Status)
VALUES
('#Evaluate(locID)#', '#Evaluate(locDesc)#', '#Evaluate(locStatus)#')
</cfquery>
<!--- Loop to next row --->
</cfloop>
mokaplanjr
Technical User
I am have a similar problem. I created a page that pulls up a list of records inside a form, with a text box that is filled with a number from the AbOrder field (used to rank results).
The idea is that the user can use this form to enter new numbers in the rank text box, which should update the individual records, allowing query results to have a new rank order.
I used your method above, but all it does is create NEW records, with only the two fields I am using in the form being filled, one of which is the StoryID field, which is "AutoNumber" and the primary key, and the AbOrder field.
Here is the code on the action page:
<cfloop index="i" from="1" to="#form.RecordCount#">
<cfset locStoryID = "form.StoryID" & i>
<cfset locAbOrder = "form.AbOrder" & i>
<cfquery name="Update_Abs#i#" datasource="MyDb">
INSERT INTO Stories
(StoryID, AbOrder)
VALUES
('#Evaluate(locStoryID)#', '#Evaluate(locAbOrder)#')
</cfquery>
</cfloop>
I want only to UPDATE the records that are returned inside the form. I tried changing the query on the action page to an UPDATE, but I get an error.
The query looks like this:
<cfloop index="i" from="1" to="#form.RecordCount#">
<cfset locStoryID = "form.StoryID" & i>
<cfset locAbOrder = "form.AbOrder" & i>
<cfquery name="Update_Abs#i#" datasource="MyDb">
UPDATE Stories
(StoryID, AbOrder)
VALUES
('#Evaluate(locStoryID)#', '#Evaluate(locAbOrder)#')
</cfquery>
</cfloop>
And the error looks like this:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (5:1) to (5:66).
The line position numbers refer to the query name, datasource line.
Look forward you your answer.
Mo
The idea is that the user can use this form to enter new numbers in the rank text box, which should update the individual records, allowing query results to have a new rank order.
I used your method above, but all it does is create NEW records, with only the two fields I am using in the form being filled, one of which is the StoryID field, which is "AutoNumber" and the primary key, and the AbOrder field.
Here is the code on the action page:
<cfloop index="i" from="1" to="#form.RecordCount#">
<cfset locStoryID = "form.StoryID" & i>
<cfset locAbOrder = "form.AbOrder" & i>
<cfquery name="Update_Abs#i#" datasource="MyDb">
INSERT INTO Stories
(StoryID, AbOrder)
VALUES
('#Evaluate(locStoryID)#', '#Evaluate(locAbOrder)#')
</cfquery>
</cfloop>
I want only to UPDATE the records that are returned inside the form. I tried changing the query on the action page to an UPDATE, but I get an error.
The query looks like this:
<cfloop index="i" from="1" to="#form.RecordCount#">
<cfset locStoryID = "form.StoryID" & i>
<cfset locAbOrder = "form.AbOrder" & i>
<cfquery name="Update_Abs#i#" datasource="MyDb">
UPDATE Stories
(StoryID, AbOrder)
VALUES
('#Evaluate(locStoryID)#', '#Evaluate(locAbOrder)#')
</cfquery>
</cfloop>
And the error looks like this:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (5:1) to (5:66).
The line position numbers refer to the query name, datasource line.
Look forward you your answer.
Mo
mokaplanjr
Technical User
GOT IT! Here's the query for the action page:
<cfloop index="i" from="1" to="#form.RecordCount#">
<cfset locStoryID = "form.StoryID" & i>
<cfset locAbOrder = "form.AbOrder" & i>
<cfquery name="Update_Abs#i#" datasource="MyDb">
UPDATE Stories
SET AbOrder = #Evaluate(locAbOrder)#
WHERE StoryID = #Evaluate(locStoryID)#
</cfquery>
</cfloop>
Yippee,
Mo
<cfloop index="i" from="1" to="#form.RecordCount#">
<cfset locStoryID = "form.StoryID" & i>
<cfset locAbOrder = "form.AbOrder" & i>
<cfquery name="Update_Abs#i#" datasource="MyDb">
UPDATE Stories
SET AbOrder = #Evaluate(locAbOrder)#
WHERE StoryID = #Evaluate(locStoryID)#
</cfquery>
</cfloop>
Yippee,
Mo
- Status
Similar threads
- Locked
- Question