Update of selected table values results in creation of database records for all rows

[paul_durrant]

Objective

------------

Creating a new page which allows you to amend dates in a table on the display, which then creates a “change log” in a SQL database table. 

How it should work

--------------------------

Page displays a variable list of rows, and a field to allow entry of a new date value on each row (as each row may have a different value, or indeed no changes).  Each time a value is entered, we want to create a new “change log” record for that change.

                example layout :

                key     current value    data entry field to allow new value

                key     current value    data entry field to allow new value - optional 2nd row

                key     current value    data entry field to allow new value - optional 3rd row, etc

Problem

-----------

But when I enter a single value it creates log records for ALL the displayed rows.

(eg. enter a new value for row 2, and we get records created for rows 1, 2 and 3).                              

If I enter more than one date on different rows, it returns error “Error Executing Database Query. 

[Macromedia][SQLServer JDBC Driver][SQLServer]Conversion failed when converting date and/or time from character string”.

This looks like the data entry field is not unique to the row it is displayed on.

I’ve seen examples of code where you enter the details on one page and pass them to another page to be actioned, but not for a table of values where one or more row are updated.  I was also hoping to keep all the code on one page, otherwise I’ll end up duplicating code in both pages.

Code

------

The form in the cfm page submits to the same page (CGI.SCRIPT_NAME) – it is its own action-page.

The variable newValue is a form field.

When you press the submit button, the form posts to the current page.

The insert query should only run when the form submits (but does not)

<cfquery “table1” reads the master dataset for a specific case reference>

<cfoutput>

<cfform action="#CGI.SCRIPT_NAME#"  method="POST" name="FormX">   

<table width="200" border="1" cellspacing="0" cellpadding="2">

<cfloop query="table1">

                <tr>

                                <td nowrap="nowrap">#field#</td> ....  <!--- display fields --->

                                <td>New value</td><td><cfinput type="date" name="NEWVALUE"></td>

                                <cfif isDefined("FORM.NEWVALUE") and FORM.NEWVALUE is not ''>      

                                                 <cfquery >       

                                                                INSERT INTO table2 ….. VALUES … '#FORM.NEWVALUE#'

                                                 </cfquery>                                                                                                                                                  

                                 </cfif>                                                                    

                 </tr>

 </cfloop>

 </table> 

 <cfinput type="submit" name="submit" value="Submit">                        

</cfform>

</cfoutput>

[BKBK · Accepted solution]

From the description of "How it should work", I understand that you want the "NEWVALUE" for each data-row to be distinct. That is, if there are three rows, then you want there to potentially be three distinct values of  "NEWVALUE".

If I understand it correctly, then it is clear why you're not getting the result you expect. It is because you're using one form-field name (NEWVALUE) for all the rows. In that case, if there are 3 rows, and you enter 21/10/2025 in the form field, only one value will be posted, namely, "21/10/2025,21/10/2025,21/10/2025".

Suggestion for a solution: give each form field a distinct name corresponding to the row. Ideally, use the primary key to distinguish the names.

Here's a fully worked out example to illustrate the idea:

<cfset table1 = queryNew("key,logDate,data","Integer,Date,Varchar", 
[ 
	{key=123,logDate="19/10/2025",data="Data in 1st row"}, 
	{key=456,logDate="22/10/2025",data="Data in 2nd row"}, 
	{key=789,logDate="21/10/2025",data="Data in 3rd row"} 
])>
	
<cfdump var="#form#" label="Dump of form scope">
	
<cfoutput>
	<cfform action="#CGI.SCRIPT_NAME#"  method="POST" name="FormX">  
<table>
	<cfloop query="table1">
	<tr>
		<td>key: </td><td>#key#</td> 
	 	
		<cfif isDefined("FORM.NEWVALUE#Key#") and FORM['NEWVALUE#key#'] is not ''>   
			<td>New value: </td><td><cfinput type="date" name="NEWVALUE#key#" value="#FORM['NEWVALUE#key#']#"></td>
			<b style="color:purple"> 
			<!---<cfquery> --->      
		          INSERT INTO table2 (id,dateValue)
		          VALUES (#key#, '#FORM['NEWVALUE#key#']#');	
		     <!---</cfquery>---> 
			</b>     
		<cfelse>
			<td>New value: </td><td><cfinput type="date" name="NEWVALUE#key#"></td>
		</cfif>
	</tr>
	</cfloop>
<table>
	<cfinput type="submit" name="submit" value="Submit">                        
	</cfform>
</cfoutput>

Copy-paste the code and launch it as a CFM page. Test it in several ways, then extract the parts that apply to your situation.

[paul_durrant]

Thanks !!  It worked like a dream.

[BKBK]

It's a pleasure. Have fun with the rest of the project.