While Access may be a performance hog here, it doesn't necessarily mean the end of the app. This kind of form isn't normally used by thousands of people simultaneously. SELECT queries are where the performance is normally critical.
It all depends on the needs of the customer. If this is an admin form, then performance isn't necessarily such a high priority. Tom -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, April 08, 2002 10:05 AM To: [EMAIL PROTECTED] Subject: RE: Multiple row update 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 ==^================================================================
