Pls help. Dynamic Field Name and DB Insert using ColdFusion

well something can help you here...
https://www.experts-exchange.com/questions/20583155/FORM-formfields-and-lists-problem.html
https://www.experts-exchange.com/questions/20974047/Updating-multiple-text-fields-of-a-dynamic-web-page-that-have-the-same-name.html
https://www.experts-exchange.com/questions/20087571/retrieving-form-fields-with-dynamic-names.html
Regards,
---Pinal

u cld do ...

<!--- Form page --->
<CFOUTPUT>
<Form NAME="frm" ACTION="Actionpage.cfm" METHOD="post">      
      <CFLOOP INDEX="i" FROM="1" TO="5">
            <input TYPE="FieldName_#i#" VALUE="#i#"><BR>
      </CFLOOP>
      <input TYPE="Submit">      
</FORM>
</CFOUTPUT>

<!--- Action page --->
<CFOUTPUT>
      <CFLOOP INDEX="j" FROM="1" TO="5">
            #Evaluate('FieldName_#j#')#<BR>
      </CFLOOP>      
      <!--- u cld use these values to be inserted in the DB or output them on a page - depends how u wanna use them --->      
</CFOUTPUT>

lemme know incase u need more help ...

K'Rgds
Anand

well...all credit goes to Anandkp as the links I have posted have similar answers. The first link's answer is also from Anandkp.
Regards,
---Pinal

Hi,

Thanks for response..

My problem seems to be more related to sql I think...

I am trying to update data in the db

<cfquery name="qInsertData" datasource="#myDsn#">
UPDATE tblName
SET columnName = '#FORM.fieldvalue#'
WHERE UserID = '#FORM.UserID#'
</cfquery>

I get an error because there is no field named "fieldvalue".  The name of the field I want to update is created dynamically.  So I do not know the name of the field

See below:

<cfloop query="qGetData" startrow="#startrow#" endrow="#endrow#">
      <cfoutput>
        <tr>
          <td><input name="#fieldname#" type="text" value="#key#"></td>
          <td>
           <input name="#fieldvalue#" type="text" value="#Value#">
         </td>
          <td><input type="submit" name="updateValue" value="Update Value" title="Update Value"></td>
        </tr>
        </cfoutput>
</cfloop>

Hope that clarifies things..

thx
-ws

hello,
I know you already have told us before that your filedvalue is dynamic...but I am still wondering what is the requirement of making it dynamic...?
I write many queries like this but ... let me write down the archi below...as I control my issue though mssql like this...


suppose I have four values... first, second, third, fourth

now... my form have like this value...

<input name="#fieldvalue#" type="text" value="#Value#">same as yours...
now when they go to get inserted...
now I have to know the name of the fields atleat ...what is there in table.. .i will write down my query like this...

<cfquery name="qInsertData" datasource="#myDsn#">
UPDATE tblName
<cfif isdefined(form.first)>
SET columnName = '#form.first#'
</cfif>
<cfif isdefined(form.second)>
SET columnName = '#form.second#'
</cfif>
more more...
WHERE UserID = '#FORM.UserID#'
</cfquery>

now if you have more then one value then you can use cfelse also...
if you havemore then one then you can also use comma to separete them... also...
Regards,
---Pinal

i dont see how this code is working for u

<cfloop query="qGetData" startrow="#startrow#" endrow="#endrow#">
     <cfoutput>
        <tr>
          <td><input name="#fieldname#" type="text" value="#key#"></td>
          <td>
           <input name="#fieldvalue#" type="text" value="#Value#">
         </td>
          <td><input type="submit" name="updateValue" value="Update Value" title="Update Value"></td>
        </tr>
        </cfoutput>
</cfloop>

as it wld result in multiple submit buttons in ur form ...

in ne-case - i think this shld give u some idea on how to proceed ...
since ur using a loop to generate the form fields - u'll need to use the same loop - to manage ur inserts as well

<!--- Form page --->
      <CFLOOP QUERY="qGetData" STARTROW="#startrow#" ENDROW="#endrow#">
            <CFOUTPUT>
               <INPUT NAME="#fieldname#" TYPE="text" VALUE="#key#">
               <INPUT NAME="#fieldvalue#" TYPE="text" VALUE="#Value#">
            </CFOUTPUT>
      </CFLOOP>
         <INPUT TYPE="submit" NAME="updateValue" VALUE="Update Value" TITLE="Update Value">
      
      
      <!--- Action page --->      
      <CFLOOP QUERY="qGetData" STARTROW="#startrow#" ENDROW="#endrow#">
            <CFQUERY NAME="qInsertData" DATASOURCE="#myDsn#">
                  UPDATE             tblName
                  SET             columnName       = '#FORM.fieldname#'
                  WHERE             UserID             = '#FORM.UserID#'
            </CFQUERY>
      </CFLOOP>

that shld help ...

Hi Pinal,

Thanks for response.

I get the following error with your code:

"Element FIRST is undefined in FORM"

To reiterate, its not only the value of the field that is dynamic, but the NAME of the field, so form.first does nothing, because in reality, when this page is displayed

<input name="#fieldvalue#" type="text" value="#Value#">  the variable #fieldvalue# will actually have a value such as "firstName" or whatever...

Hope that makes sense..

Thanks for continued help.. hopefully I can get this solved...

Thx

-WS

hello Anandkp,
You are correct.
I was just wondering will this work...
 <!--- Action page --->    
     <CFLOOP QUERY="qGetData" STARTROW="#startrow#" ENDROW="#endrow#">
          <CFQUERY NAME="qInsertData" DATASOURCE="#myDsn#">
               UPDATE           tblName
               SET           columnName      = '#FORM.fieldname#'
               WHERE           UserID           = '#FORM.UserID#'
          </CFQUERY>
     </CFLOOP>
the reason is that fieldname is not passed thorugh form. it is just name of the field... form.anything has the value of that field not the name...
so many be it will fail...what is your opinion...
i think, the solution will be looping over query as I have suggested...
what is  your opinion... .here...
I will be going to sleep soon... anyway,,ikt is quite late here now...
Regards,
---Pinal

okey...westside...
let us start from beginning as Anandkp is with us too... I hope to get it fixed before I go to sleep.
 
Q1) what is the table field where you want to insert the form values... no need to complite value... only two or three values will do...
Q2) where the fieldvalue are coming from ... what is the result of the query where you are getting the value and what is the table value... only two or three
Q3) what are you doing this late... ( just kidding)

Regards,
---Pinal

Pinal : <CFLOOP QUERY="qGetData" STARTROW="#startrow#" ENDROW="#endrow#">
this wld work in the action page as well - since the query / start row & max rows will have to be used there as well to complete the logic ... & get the same set - to update as was used to display in the form

lemme know ...

Hi,

Anand:

Yes, your right, it does result in multiple submit buttons..that is what we want.  

Basically I output a row in an html table for each record in the query that has 2 text boxes and one submit button.

This allow me to hopefully update each individual text box as opposed to all at once.

So if my query returns 3 records for example.  I will get 3 rows (<tr>'s) and EACH row will have 2 text boxes and 1 submit button

So if the user wants to change what we have stored in the database in row 1 and 3, but not row 2, they would enter a new value in the text box and click the submit button that corresponds to that row/record.

This line is my problem:    
SET           columnName      = '#FORM.fieldname#'

Normally that is what I would do, but I get an error, saying FORM.fieldname is undefined

Since I am dynamically generating text boxes, I need to dynamically generate the name of the text box as well so I can refer to it in scripting...

thx

-WS

Hope that makes sense...

hello Anand,
I agree... well that is true buddy.
Though, the name of the columns are dynamic... like
UPDATE           tblName
               SET           columnName      = '#FORM.fieldname#'
               WHERE           UserID           = '#FORM.UserID#'
here the columnName is dynamic...it can be different ...
so I was thinking ... that may be we can pass them... from the previous page... or we hardcode them and put the condtion of cfif around...
what do you say? Mya be you can hybrid our suggestions and make it roll...
WestSide, we are waiting for your answer.
Regards,
---Pinal

very stale window.
Regards,
---Pinal

glad that it worked...
Good night,
---Pinal

Hi,

Was going to start a new post, but was hoping you could help...

Everything works cool, but the loop/query below updates all rows even if there was not a change made by the user.  This is fine and I am happy with this, but thought I would ask if there was a way to identify which record the user was updating...... if not, no biggie.. thx!

I was thinking down the line, if the record count grew to maybe 40, which is a bit high for this piece of functionality, but if it did, and the user made a change in one text box, and there were 39 others that did not change, all records would be updated if you know what I mean..

<CFLOOP QUERY="qGetData" STARTROW="#startrow#" ENDROW="#endrow#">
          <CFQUERY NAME="qInsertData" DATASOURCE="#myDsn#">
               UPDATE           tblName
               SET           columnName      = '#Evaluate('FORM.#qGetData.fieldname#')#'
               WHERE           UserID           = '#FORM.UserID#'
          </CFQUERY>
     </CFLOOP>

thanks again!

-WS

You would have to have a unique ID for the record that was updated and tehn add it to the where clause like below :

(Note I also made the columnName part more efficient)

<CFLOOP QUERY="qGetData" STARTROW="#startrow#" ENDROW="#endrow#">
          <CFQUERY NAME="qInsertData" DATASOURCE="#myDsn#">
               UPDATE           tblName
               SET           columnName      = '#FORM[qGetData.fieldname]#'
               WHERE           UserID           = '#FORM.UserID#'
                AND             RecordID = #Form.RecordID_that_changed#
          </CFQUERY>
     </CFLOOP>

Or at least some way to uniquely identify the record that was changed versus those that were not....

Makes Sense.

Thanks for that mrichmon..

-WS

Didint realise u wld have multiple records for one user ...