Solved

Edit multiple rows at one time?

Posted on 2009-05-07
9
168 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
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 
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

Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

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…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
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.…

691 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