Updating, Adding, or Deleting rows from a query

[Richard_Mossman]

So, I've got this page that originally was made of four different forms. It worked great. But, then the client asked that I "idiot proof" the page.

Originally, I had a form that enclosed a one-line table (with Add & Clear buttons on the end) that took seven fields and "added" a new record to the "tbljob" table.

That was followed with another form that created a table from the results of a cfquery and listed all the rows from tbljob (with Update/Delete buttons on the end of each row).

This worked fine when it was two separate forms. Now, I'm trying to use only one "form" while displaying the two tables. The buttons are now done with Javascript (so there are dialog boxes). The problem is that I am getting a CF error message because the insert, update, and delete commands are trying to pass the contents of the fields as comma-delimited lists rather than the single data item that is actually in each field. I am passing a hidden field with each row that contains the "job_id".

I know the problem is that I'm not identifying each row as being unique. I guess it's like I'm passing an "array" of data rather than just a "row".

Basically, I just want to be able to display a blank row for adding records followed by multiple rows from a query. The blank row needs to have "Add" and "Cancel" buttons at the end and the multi-row part has to have "Update" and "Delete" buttons on the end of each row and manage the appropriate records.

It's got me stumped. TIA.

[primalx2003]

What you can do is try using <cfloop> to insert, update and delete your information.

For instance deleting the information try this.

<!---- Delete Jobs---->
<cfloop index="JobIDs" list="#Job_ID#" delimiters=",">
<cfquery name="delteall" datasource="dbsource">
DELETE
FROM table
Where job_Id = #JobIDs#
</cfquery>
</cfloop>

Do the same for your inserting and updating.

Because what CF see is job_id = 1,2,3,4,5,6,7,8,9

SO you are looping over a list = #job_id# and delimiter is " , " and we are calling the index JobIDs. JobIDs are the values - 1 2 3 4 5 6 ....

I hope this helps. As for the displaying of the images, use if then statements:

<cfif isdefined("edittable") and edittable eq '"y">
Then display the query and outputs for the editing options
<cfelseif isdefined("deletetable") and addtable eq "y">
The dispaly the query and outputs for adding options
<cfelse>
Then display the query for outputing the delete options
</cfif>

So your link will have soemthing of this sort <a href="samepage.cfm?deletetable=y">Add Table</a>

Hope this helps,

Sevor Klu

[coderWil]

To 'idiot proof' a page. NEVER DELETE ANYTHING. The idiot will come to you and say 'I deleted something I needed to keep, can you turn it back on?' or more common 'What happened to the XYZ file? No, I didn't delete anything'

There are a number of ways to avoid deleteing a file. 1. Have only a certain authorized user allowed to do 'deletes'
2. use an 'active' column in the tabe and set it to 0 when the user deletes (can eventually lead to lots of unused entries in the database
3. use an 'active' column and a 'deleted on' date column. Have a scheduled transaction that runs nightly that will delete all entries in the table that is older than xx days (30 days?) and is active = 0

Doing this will do 2 things. Have the client see you as a Hero when the 'idiot' strikes AND allow you to find out when (and possibly who if you are set up that way) is deleting files they shouldn't. (I like the Hero part the best)

Hope this helps

[Richard_Mossman]

Thanks for your response, and for the most part, I agree with your comments. But, in this case, the people who are "paying the bills" overrode my concerns and told me to actually delete the data.

That is the reason I put javascript buttons (with a "challenge" dialog box) in the forms, rather than simple "type=submit" buttons. At least, now, the user has to acknowledge their decision.

Also, luckily, they only have to input seven fields to put it back.

To make matters worse, early in the process, the user can actually delete the entire request with one button press. I was able to mitigate that possiblility by changing the button at the end of the rows to "Remove" and only use "Delete" on the button that destroys the entire request. Now, the User Guide and Tech Support people only refere to "Delete" in the most drastic situation.

Yes, I am getting these things in writing from the design team.

[coderWil]

LOL. I would suggest not relyijng on Javascript for your pop-up box. If you have the added time and resources, do a CFM check. Users can turn off Javascript or use a browser that it doesn't run on and never get the challenge. Server-side challenge pages can't be avoided this way.

Just my 2 cents.