Solved

Edit multiple rows at one time?

Posted on 2009-05-07
9
165 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
sorry, I used ii  in the  CFLOOP and  kk in the lines :)

In the CFLOOP switch to kk  :  index="kk"
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ok, I was able to get it working.

Thank you very much for your time and efforts!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now