Mass-update table data with a single query?

[SQL_n00b]

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!

[martincarter71]

Hi SQL_n00b,

You might be able to change <cfif isDefined("FormSubmitted")> to <cfif isDefined("Form.Submit")> where Form.Submit is the name of the submit button.

Thanks
MC

[cheapterp]

Hi SQL_n00b,

You might be able to change <cfif isDefined("FormSubmitted")> to <cfif isDefined("Form.Submit")> where Form.Submit is the name of the submit button.

Thanks
MC

The text field values need to be stored using #ListGetAt(FORM.txtValue, getIDs.CurrentRow)# instead of just 'FORM.txtValue'

[arrgh]

The text field values need to be stored using #ListGetAt(FORM.txtValue, getIDs.CurrentRow)# instead of just 'FORM.txtValue'

That is not a good idea. If the form field contains comma's the script will error out. Better to keep the fields separate by dynamically naming them txtValue1, txtValue2, etc...

[SQL_n00b]

That is not a good idea. If the form field contains comma's the script will error out. Better to keep the fields separate by dynamically naming them txtValue1, txtValue2, etc...

I am validating user input by allowing them to only enter numbers. Any input other than the numbers 0-9 and a decimal point is caught (using cftry-cfcatch) and an error is thrown.

That said, I would really like to know how to dynamically name the text fields as 'txtValue1', 'txtValue2', 'txtValue3' etc.
and how I will then need to modify the 'INSERT' SQL statement. Presently, I am using the following SQL query:

<cfloop query="getIDs">
    <cfquery name="UpdateCalculations" datasource="#myDB#">
	INSERT INTO myTable (ID, Name, Age)
	VALUES ('#getIDs.ID#', '#getIDs.Name#', <cfqueryparam value="#ListGetAt(txtValue, getIDs.CurrentRow)#" cfsqltype="cf_sql_float">)
    </cfquery>
</cfloop>

P.S.: The IDs are varchar type, not plain numbers. Hence surrounded by quotes in the INSERT query