Solved

Update one or more rows in one step.

Posted on 2011-03-04
7
329 Views
Last Modified: 2012-05-11
First page queries the database for open invoices, the user selects one or more invoices and presses submit.

The second page displays the invoice numbers and the total for all invoices selected.

The user then selects the payment form and submits.

Im having trouble figuring out how to update the invoice status to paid.

UPDATE invoice
SET invoice_status = '1'
SET balance = '0'
WHERE inv_id = '#FORM.invoice_id#'

How can i make it loop through all the selected invoices and update them all.

Thanks.
0
Comment
Question by:aka_FATCAT
  • 4
  • 3
7 Comments
 
LVL 10

Accepted Solution

by:
js_vaughan earned 500 total points
ID: 35042032
Below is some sample code.  Basically, make sure all your invoice fields are the same name.  When the form posts, the result will be a comma delimited list of all of the values.  Then, it is a simple matter of using SQL's IN() to accept multiple values.
<cfif structKeyExists(FORM,"invoice_id")>
	<cfdump var="#FORM.invoice_id#" />
	<cfquery name="myQuery" datasource="#application.db#">
		UPDATE invoice
		SET invoice_status = <cfqueryparam cfsqltype="cf_sql_integer" value="1" />
		SET balance = <cfqueryparam cfsqltype="cf_sql_integer" value="0" />
		WHERE inv_id IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#FORM.invoice_id#" list="yes" />
	</cfquery>
</cfif>

<form name="myForm" method="post">
	<input type="checkbox" name="invoice_id" value="1" /> Invoice 1 <br />
	<input type="checkbox" name="invoice_id" value="2" /> Invoice 2 <br />
	<input type="checkbox" name="invoice_id" value="3" /> Invoice 3 <br />
	<input type="submit" />
</form>

Open in new window

0
 

Author Comment

by:aka_FATCAT
ID: 35042047
I can't us cfdump on my shared hosting account.
0
 
LVL 10

Expert Comment

by:js_vaughan
ID: 35042050
i had it there for testing, the line is not needed.  sorry for the confusion
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:aka_FATCAT
ID: 35042126
That sort of works.

The problem is that the form is on one page that when submitted displays the information on a conformation page. The user then chooses the payment method and submits the form.

So I will need to store the invoice_id's somewhere for it to be used when the second form is submitted.

Thanks
0
 
LVL 10

Expert Comment

by:js_vaughan
ID: 35042176
put a hidden formfield on your confirmation page.
<input type="hidden" name="invoice_id" value="#FORM.invoice_id#" />

Open in new window

0
 

Author Comment

by:aka_FATCAT
ID: 35042184
Ahhh, my level of ignorance has been upgraded to master level.

Thank you.
0
 

Author Closing Comment

by:aka_FATCAT
ID: 35042186
Simply elegant.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Coldfusion: Datatable does not return all checkeded checkboxes 2 88
Database set up 5 73
ColdFusion Rereplace 3 78
REGEX HELP 11 40
This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

775 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