Solved

Update one or more rows in one step.

Posted on 2011-03-04
7
328 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CFdocumnet font issue 5 39
<cfinsert> (coldfusion) fails to insert record in SQL database. 21 62
CFLogin explained 10 85
expandPath erroring 15 57
Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

867 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

20 Experts available now in Live!

Get 1:1 Help Now