Edit multiple rows at one time?

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
LVL 1
g118481Asked:
Who is Participating?
 
gdemariaConnect With a Mentor Commented:
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
 
gdemariaCommented:
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
 
g118481Author Commented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
g118481Author Commented:
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
 
gdemariaCommented:
sorry, I used ii  in the  CFLOOP and  kk in the lines :)

In the CFLOOP switch to kk  :  index="kk"
0
 
g118481Author Commented:
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
 
gdemariaCommented:
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
 
g118481Author Commented:
Ok, I was able to get it working.

Thank you very much for your time and efforts!
0
 
g118481Author Commented:
Ok, I was able to get it working.

Thank you very much for your time and efforts!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.