I have a table that has various rows of data grabbed from a table in my DB. 3 of the columns display the queried data. The last column, however, contains text fields for the user to hand-enter values. When the user hits the Submit button, all of the values entered in these text fields should be saved to another table.
How do I do this mass-update using one query? I have tried the below code, but for some reason (that I cannot figure out), the cfloop doesn't seem to work.
<cfif isDefined("FormSubmitted")> <!---If form is submitted---> <cfloop query="getIDs" startrow="1" endrow="#getIDs.RecordCount#"> <cfquery name="UpdateCalculations" datasource="myDB"> INSERT INTO myTable (ID, Name, Age) VALUES ('#getIDs.ID#, '#getIDs.Name#', #FORM.txtValue#) </cfquery> </cfloop> </cfif>
'getIDs' is the query that works on another table (a different one from the one being updated) to retrieve all of the IDs+Names presently in the DB. What I cannot figure out is what is the best way to insert all the text field values using just one query! (Just FYI, the values entered in the text boxes are numbers)
Also, could someone please explain to me why the <cfloop> inside of the <cfif> doesn't work?
I am a CF-SQL newbie; any help is appreciated!