RE: Multiple updates on a single submit
Something similar to this was discussed yesterday. Check out http://www.houseoffusion.com/cf_lists/index.cfm?method=messagesthreadid=179 60forumid=4refresh=0 -Original Message- From: Kennerly, Rick H CIV [mailto:RHKennerly;rroads.med.navy.mil] Sent: Tuesday, October 22, 2002 11:05 AM To: CF-Talk Subject: Multiple updates on a single submit a bit intimidated by the level of discourse on this list, but here goes. (You should start a CF-Talk_Training_Wheels for guys like me.) Anyway, just getting started with CF and one thing I never see are examples of multiple record updates into a database with a single submit button. What we're noodling over is an attendance process for a staff of about 500 in 14 departments. The idea is that an administrator would click a button that would query the entire SQL db for employee names by department. The query would dump the results into a form that also had check boxes or radio buttons (on duty, day off, holiday, training, leave, sick). We've gotten that far. But we can't figure out how to code so that the submit button would then update all the queried records in the database. When we try the obvious--to us--code, all the buttons on the entire update are applied to very first record only. I can see how to do this one record at a time. I just don't see how to do it five at a time. Is it possible in ASP/CF? Thanks Rick ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
RE: Multiple updates on a single submit
Rick,
There's lots of ways to do this. Each approach has it's own strengths and
weaknesses. Here's one:
On the form using radio, create a separate form name for each employee using
a label and the PK from the table (let say it's a column named employeeID):
input type=radio name=status_#employeeId# value=onduty
input type=radio name=status_#employeeId# value=offduty
input type=radio name=status_#employeeId# value=training
.etc
...if you need to know the PK's on the handler page include them hidden in
the form:
input type=hidden name=employeeids
value=#valuelist(myquery.employeeid)#
.. if not, you can run a query on the handler page to get them back
Then, on the form handler (where you want to do the update) do the
following:
Cfloop list=#employeeids# index=item
cfset FormElement = 'status_' item')
Cfif IsDefined('Form.#formElement#')
cfquery name=update datasource=blah
UPDATE EmployeeTable
SET Status = cfqueryparam cfsqltype=CF_SQL_CHAR
value=#form[formElement]#
Updated = getdate()
WHERE Employeeid = cfqueryparam cfsqltype=CF_SQL_INTEGER
value=#val(item)#
/cfquery
/cfif
/cfloop
Of course you could loop through all the form elements and look for
Listfirst(formitem,'_') IS 'status', then take the number right off the
label. That way you wouldn't have to pass a list of primary keys OR get them
from the db. But if the form has a lot of other items on it this may be
more work for the server.
There are a number of other ways. I've used this one successfully, but
remember - it does expose the PK of your table in the client code.
-mk
-Original Message-
From: Kennerly, Rick H CIV [mailto:RHKennerly;rroads.med.navy.mil]
Sent: Tuesday, October 22, 2002 10:05 AM
To: CF-Talk
Subject: Multiple updates on a single submit
a bit intimidated by the level of discourse on this list, but here goes.
(You should start a CF-Talk_Training_Wheels for guys like me.)
Anyway, just getting started with CF and one thing I never see are examples
of multiple record updates into a database with a single submit button.
What we're noodling over is an attendance process for a staff of about 500
in 14 departments. The idea is that an administrator would click a button
that would query the entire SQL db for employee names by department. The
query would dump the results into a form that also had check boxes or radio
buttons (on duty, day off, holiday, training, leave, sick). We've gotten
that far. But we can't figure out how to code so that the submit button
would then update all the queried records in the database. When we try the
obvious--to us--code, all the buttons on the entire update are applied to
very first record only.
I can see how to do this one record at a time. I just don't see how to do
it five at a time. Is it possible in ASP/CF?
Thanks
Rick
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Re: Multiple updates on a single submit
If I'm understanding you , there are actually two ways of doing this. If you're certain that your data won't contain any commas, the easiest way is to build the form by looping over each queried item. Each item in the form will have the same name (ie employeeID, attendanceStatusID). This will send a comma delimited list pairs (ie employeeID:23,45,64,23,87 attendanceStatusID:4,2,3,3,1,2) Then, to submit each record into the database, loop over the lists. The key here is that each position in each list corresponds to the data in the same position in the other lists. If you're going to do this, you MUST make sure that some value is submitted for every single item on the form. Otherwise, the length of the lists is thrown off and thus, the values for each record, not to mention that an error will be thrown. If your data may contain commas or you don't want to force the user to submit a value for each item in the form, you can build the form by looping over each queried item, but instead, this time give each and every form item a unique name, usually a number (ie employeeID1,employeeID2, attendanceStatus1,attendanceStatus2). In this way, all 1's go together and all 2's go together, etc. You can then break apart the form item names and insert them via a loop. If you'd like to see code examples of either form elements or data insert sequences, let me know. I have code samples of either approach. hth, ~Val - Original Message - From: Kennerly, Rick H CIV [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, October 22, 2002 11:04 AM Subject: Multiple updates on a single submit a bit intimidated by the level of discourse on this list, but here goes. (You should start a CF-Talk_Training_Wheels for guys like me.) Anyway, just getting started with CF and one thing I never see are examples of multiple record updates into a database with a single submit button. What we're noodling over is an attendance process for a staff of about 500 in 14 departments. The idea is that an administrator would click a button that would query the entire SQL db for employee names by department. The query would dump the results into a form that also had check boxes or radio buttons (on duty, day off, holiday, training, leave, sick). We've gotten that far. But we can't figure out how to code so that the submit button would then update all the queried records in the database. When we try the obvious--to us--code, all the buttons on the entire update are applied to very first record only. I can see how to do this one record at a time. I just don't see how to do it five at a time. Is it possible in ASP/CF? Thanks Rick ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
