Solved

Pls help. Dynamic Field Name and DB Insert using ColdFusion

Posted on 2004-08-30
19
502 Views
Last Modified: 2013-12-24
Hi,

I have this page where I am using my database to store field names for my user interface. I have reasons for this.

The one problem I am facing is with my database insert.

I have a text box where the user can "update" a value

I am trying to write an update statement that will handle a field name that is dynamic??

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

My problem is the dynamic part. I can't SET the someValue = '#FORM.fieldvalue#' because the #fieldvalue# is dynamic. Since the page is dynamically created, I do not know the name of the field....

Can anyone help?

-WS
0
Comment
Question by:Westside2004
  • 8
  • 5
  • 5
  • +1
19 Comments
 
LVL 21

Expert Comment

by:pinaldave
ID: 11938922
0
 
LVL 17

Expert Comment

by:anandkp
ID: 11938928
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
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11939016
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
0
 
LVL 1

Author Comment

by:Westside2004
ID: 11939027
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
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11939061
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
0
 
LVL 17

Expert Comment

by:anandkp
ID: 11939082
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 ...
0
 
LVL 1

Author Comment

by:Westside2004
ID: 11939108
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
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11939114
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
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11939141
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
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 17

Expert Comment

by:anandkp
ID: 11939163
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 ...
0
 
LVL 1

Author Comment

by:Westside2004
ID: 11939169
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...
0
 
LVL 17

Accepted Solution

by:
anandkp earned 250 total points
ID: 11939187
this shld do : [i overlooked the form.variable name earlier ...]

<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>
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11939188
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
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11939194
very stale window.
Regards,
---Pinal
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11939229
glad that it worked...
Good night,
---Pinal
0
 
LVL 1

Author Comment

by:Westside2004
ID: 11939374
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
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11943115
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....
0
 
LVL 1

Author Comment

by:Westside2004
ID: 11947188
Makes Sense.

Thanks for that mrichmon..

-WS
0
 
LVL 17

Expert Comment

by:anandkp
ID: 11950834
Didint realise u wld have multiple records for one user ...
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure (http://www.axure.com/). It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
The purpose of this video is to demonstrate how to connect a WordPress website to Google Analytics. This will be demonstrated using a Windows 8 PC Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php :…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now