Ah-ha - this is why I said it depends on your database. Access is the
only one I'm aware of that doesn't support multiple statements in one
CFQuery. Also, you'll want to lose the semi-colon at the end. So as
was mentioned, each iteration, you need to do a cfquery:
<cfloop...>
<cfquery...>
....
</cfquery>
</cfloop>
Keep in mind that this is one of those instances where Access is
absolutely *killing* your performance.
---
Billy Cravens
-----Original Message-----
From: Schweitzer,Dan [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 08, 2002 9:51 AM
To: [EMAIL PROTECTED]
Subject: RE: Multiple row update
I get this error:
[Microsoft][ODBC Microsoft Access Driver] Characters found after end of
SQL
statement.
SQL = "UPDATE Newsletter SET Sortorder = '1' WHERE ID = 20; UPDATE
Newsletter SET Sortorder = '2' WHERE ID = 37; UPDATE Newsletter SET
Sortorder = '3' WHERE ID = 38; UPDATE Newsletter SET Sortorder = '4'
WHERE
ID = 39;"
Dan Schweitzer
Web Content Developer
Enterprise Systems Administration
[EMAIL PROTECTED]
512.438.4365
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, April 08, 2002 8:52 AM
To: [EMAIL PROTECTED]
Subject: RE: Multiple row update
Seems like Hal has an article on this over at his site, but here's how I
do it:
Assuming you're populating your form from a query, I passed the primary
key in as a hidden form field, and dynamically build the other form
fields with the value of my primary key:
<cfoutput query="qryNames">
<input type=hidden name=id value=#id#>
<input type=text name="fname_#id#">
<input type=text name="lname_#id#">
</cfoutput>
On my action page, I loop over the primary key form field (eg, form.id)
which will be a list, and each iteration, I use evaluate() to determine
the form field values I need. Depending on your database, you can
update it all at once, by putting your loop inside of a cfquery.
<cfquery datasource="#request.dsn#">
<cfloop index="i" list="#form.id#">
UPDATE tblUsers
SET fname = '#evaluate("form.fname_#i#")#',
lname = '#evaluate("form.fname_#i#")#'
WHERE id = #i#;
</cfloop>
</cfquery>
---
Billy Cravens
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Sent: Monday, April 08, 2002 8:31 AM
To: [EMAIL PROTECTED]
Subject: Multiple row update
Does anyone know how to update multiple rows at the same time from one
form? We have titles with a sort order and the users needs to be able
to change the order and submit the changes from one form.
==^================================================================
This email was sent to: [email protected]
EASY UNSUBSCRIBE click here: http://topica.com/u/?bUrFMa.bV0Kx9
Or send an email to: [EMAIL PROTECTED]
T O P I C A -- Register now to manage your mail!
http://www.topica.com/partner/tag02/register
==^================================================================
