Solved

Edit multiple rows at one time?

Posted on 2009-05-07
9
167 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
Don't Miss ATEN at InfoComm 2017!

Visit booth #2167 to see the  new ATEN VM3200 32 x 32 Modular Matrix Switch. Other highlights include the VE8950 4K HDMI Over IP Extender, VS1912 12-Port DP Video Wall Media Player  and VK2100 ATEN Control System. Register now with Free Pass Code ATEN288!

 
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 500 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

732 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