?
Solved

Edit multiple rows at one time?

Posted on 2009-05-07
9
Medium Priority
?
173 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
  • 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 Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
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…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

850 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