?
Solved

Edit multiple rows at one time?

Posted on 2009-05-07
9
Medium Priority
?
171 Views
Last Modified: 2013-12-24
Experts,

I want to be able to edit multiple rows at one time.

I have a Select query that outputs the list of records from a DB table, and I would like to be able to change some values of each record that is displayed, all at one time.

In other words, I want to display the records, then change each records in the list, then click submit and update the table with all my edits.  I am using CF 5.0

Is this possible?
If so, can you supply a simple example for me?

Thanks
0
Comment
Question by:g118481
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 24326148
Append a "record number" to the end of each field name, this
number will associate fields of the same record.  

Also create a hidden field to hold the total number of records
(this isn't required but is helpful).

Here's a simple example:


 
<cfoutput query="getUsers">
   <cfset variables.ct = getProducts.currentRow>
 <tr>
   <td>First Name:</td><td><input type="text"   name="firstName#variables.ct#" value="#form.firstName#">
   <input type="hidden" name="userID#variables.ct#" value="#userID#">
   </td>
 </tr>
 <tr>
   <td>Last Name:</td><td><input type="text" name="lastName#variables.ct#" value="#form.lastName#"></td>
 </tr>
 <tr>
   <td>Login Name:</td><td><input type="text"   name="username#variables.ct#" value="#form.username#"></td>
 </tr>
</cfoutput>
<cfoutput>
<input type="hidden" name="totalRecords" value="#variables.ct#">
</cfoutput>
 
 
 
 
Processing Page
---------------
When you process it, you loop through the number of records from 1 to X 
and convert them back to regular variables and update them (you don't really need to convert
them back to variables without numbers, they're just easier to work with).
 
 
<cfloop index="ii" from="1" to="#form.totalRecords#">
  <cfset variables.firstName = form['firstName' & kk]>
  <cfset variables.lastName  = form['lastName' & kk]>
  <cfset variables.username  = form['username' & kk]>
  <cfset variables.userID    = form['userID' & kk]>
 
  <cfif len(variables.userID)> <!--- update --->
	  <cfquery name="UpdateUser" datasource="DSN">
	    update user
		  set firstName = '#variables.firstName#'
		    , lastName  = '#variables.lastName#'
			, userName  = '#variables.username#'
		 where userID = #val(variables.UserID)#
	  </cfquery>
  <cfelse> <!--- no ID, so insert --->
	  <cfquery name="UpdateUser" datasource="DSN">
	    insert into user (firstname, lastname, username)
		values ('#variables.firstName#','#variables.lastName#','#variables.username#')
	  </cfquery>
  </cfif>  
</cfloop>

Open in new window

0
 
LVL 1

Author Comment

by:g118481
ID: 24327163
I like your example.

However I have a couple of questions:
1.  How can I submit the first part of your example?
2.  Where in relationship to your part 1 example do I place the form tags, or does it matter?
0
 
LVL 1

Author Comment

by:g118481
ID: 24327472
I get this error when I run your example.
Should "& kk" be something else?

*****************************************
Error Occurred While Processing Request
Error Diagnostic Information

An error occurred while evaluating the expression:
 variables.id = form['id' & kk]
*****************************************


0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 39

Expert Comment

by:gdemaria
ID: 24327912
sorry, I used ii  in the  CFLOOP and  kk in the lines :)

In the CFLOOP switch to kk  :  index="kk"
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 24327949
Here's a full example, including form submit.    It is a different table/info but same concept.

This is a good example because if there is an error, it will drop back to the form, unsaved, and still show the user's entries



 
<cfset application.datasource = "YOUR DATASOURCE HERE">
 
<cfparam name="url.listingID" default="1">
<cfparam name="form.listingID" default="#url.listingID#">
 
<cfset variables.extraBlankRows = 3> <!---- number of extra blank records to add at the end for inserting ----->
<cfset variables.error = ""> <!---- will contain any error messages for display below later ----->
 
<!----- the action, will insert or update the record ----->
<cfif isDefined("form.btnHouseFeatures")>
  <cftry>
    <!---- loop through every record, add or update depending on whether they already had an ID ----->
    <cfloop index="kk" from="1" to="#val(form.totalRecords)#">
        <cfset variables.somethingEntered = true> <!---- assume some data was entered for each record ---->
    	<cfset form.photoname = form['photoname' & kk]>
    	<cfset form.description = form['description' & kk]>
    	<cfset form.photoID = form['photoID' & kk]>
        <cfif len(form.photoname) + len(form.description) eq 0>
          <cfset variables.somethingEntered= false> <!---- this record is blank ---->
        </cfif>
        <cfif val(form.photoID) eq 0> <!---- no primary key, so insert ---->
           <cfif variables.somethingEntered> <!--- don't save a blank record ---->
            <cfquery name="insertPhotos" datasource="#application.datasource#">
              insert into photos (photoname, description, listingID)
              values ('#form.photoname#','#form.description#',#form.ListingID#)
            </cfquery>
           </cfif>
        <cfelse> <!----- already exists, so update it ----->
           <cfif variables.somethingEntered> 
             <cfquery name="updatePhotos" datasource="#application.datasource#">
              update photos
                set photoname = '#form.photoname#'
                  , description = '#form.description#'
              where photoID = #val(form.photoID)#
            </cfquery>
           <cfelse> <!---- nothing entered, so delete the record ---->
             <cfquery name="deletePhotos" datasource="#application.datasource#">
              delete photos where photoID = #val(form.photoID)#
             </cfquery>
           </cfif>
        </cfif>
    </cfloop>
 
  <cfcatch type="Any">
     <cfset variables.error = cfcatch.message>
  </cfcatch>
  </cftry>
</cfif>
 
 
<!---- FETCH DATA
       Only fetch data from the database is there were no errors 
       If there were errors, show the error and the same data as entered, but not saved yet ------>
<cfif len(variables.error) eq 0>
    <!---- fetch the data from the database and convert into an array ----->
    <cfquery name="getData" datasource="#application.datasource#">
      SELECT ListingID, photoID, photoname, description
      FROM photos
      where ListingID = #val(form.ListingID)#
      order by photoName
    </cfquery>
    <cfloop query="getData">  <!---- create a form variable for every record, every column in the format:  form.columName1 ------>
      <cfloop index="aCol" list="#getData.columnList#">
        <cfset form[aCol & getData.currentRow] = getData[aCol][getData.currentRow]>
      </cfloop>
    </cfloop>
    <cfset form.totalRecords = getData.recordCount>
    <!----- create extra form variables that are blank, same format form.columnName2 ---->
    <cfloop index="kk" from="1" to="#variables.extraBlankRows#"> 
      <cfset form.totalRecords = form.totalRecords + 1>
      <cfloop index="aCol" list="#getData.columnList#">
        <cfset form[aCol & form.totalRecords] = "">
      </cfloop>
    </cfloop>
</cfif>
 
<cfif len(variables.error)> <!----- show the error ----->
 <div style="background-color:pink;color:maroon;padding:10px;font-weight:bold;">
  <cfoutput>Error: #variables.error#</cfoutput>
 </div>
</cfif>
 
<cfform name="myform" id="myform">
    <h1>ENTER .JPG NAMES &amp; DESCRIPTIVE TEXT HERE</h1>
    <cfoutput>
    <table width="525" border="0" cellpadding="2" cellspacing="2">
      <tr>
        <th width="144" scope="col">Photo Name</th>
        <th width="412" scope="col">Description</th>
      </tr>
      <!---- us a simple counter, not the ID to tack onto the variable names ----->
      <cfloop index="ii" from="1" to="#form.totalRecords#">
      <tr>
        <td valign="top"><input type="text" name="photoname#ii#" value="#form['photoname'&ii]#"/></td>
        <td><textarea name="description#ii#" cols="70" rows="3">#form['description' & ii]#</textarea>
            <input type="hidden" name="photoID#ii#" value="#form['photoID' & ii]#"> <!---- the primary key of the table ----->
        </td>
      </tr>
      </cfloop>
      <tr>
        <td>
        <cfoutput>
        <input type="hidden" name="listingID" value="#URL.ListingID#">
        <input type="Hidden" name="totalRecords" value="#form.totalRecords#">
        </cfoutput>
        </td>
        <td align="left"><cfinput type="submit" name="btnHouseFeatures" value="ADD PHOTOS"/></td>
      </tr>
    </table>
    </cfoutput>
</cfform>

Open in new window

0
 
LVL 1

Author Comment

by:g118481
ID: 24330225
I am not getting the update to write to the table.
It is as if it is by passing the update.  
I am not getting any errors, though.
I am working with just one record to try and get this working.

Here is my URL string, which is passing the variables needed.
I have attached the action/update code snippet for review, below.

http://myserver.com:8080/test_update_action.cfm?id1=2713&inPace1=YY&totalRecords=1&initedit_OK=Update
<cfset dsn = "mydsn">
<cfif isDefined("form.totalRecords")>
  <cftry>
    <!---- loop through every record, add or update depending on whether they already had an ID ----->
    <cfloop index="kk" from="1" to="#val(totalRecords)#">
        <cfset variables.somethingEntered = true> <!---- assume some data was entered for each record ---->
		<cfset form.id = form['id' & kk]>
    	<cfset form.inPace = form['inPace' & kk]>
    	  <cfif id eq 0> <!---- no primary key, so insert ---->
           <cfif variables.somethingEntered>
			no primary key, so insert
           </cfif>
        <cfelse> <!----- already exists, so update it ----->
           <cfif variables.somethingEntered> 
             <cfquery name="updatePhotos" datasource="#dsn#">
              update mytable
                set inPace = '#form.inPace#' 
                 where id = #form.id#
            </cfquery>
           <cfelse> 
			 nothing entered, so delete the record
           </cfif>
        </cfif>
    </cfloop>
 
  <cfcatch type="Any">
     <cfset variables.error = cfcatch.message>
  </cfcatch>
  </cftry>
</cfif>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 24330349
One tip, for now, add <CFRETHROW> to your CFCATCH to ensure you can see the full error and be sure any errors are not hidden...

  <cfcatch type="Any">
     <cfset variables.error = cfcatch.message>
       <cfrethrow>
  </cfcatch>


Just in case you have a variable scoped ID, I would add the form scope to this, along with a val() in case the value is empty instead of zero (i would think it would be empty if no ID)

<cfif id eq 0>

to

<cfif val(form.id) eq 0>


Otherwise, I may have to see the form part of the code..
0
 
LVL 1

Author Comment

by:g118481
ID: 24332539
Ok, I was able to get it working.

Thank you very much for your time and efforts!
0
 
LVL 1

Author Closing Comment

by:g118481
ID: 31578965
Ok, I was able to get it working.

Thank you very much for your time and efforts!
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Suggested Courses

765 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