Updating more than one record at a time

[nsukari]

I have a query that updates the record in the DB with the new information the user submits. What I want to do is allow the users to update all of their records at once, click submit, and then have the correct records update in the DB. Is there a way to di this? I tried using CFLOOP, but it was unsuccessful.
Here is my query, the ID record is the unique identifier for each record.
<CFQUERY NAME=&quot;updatedb&quot; DATASOURCE=&quot;K&quot;>
UPDATE Newtable
SET absorbed='#Absorbed#', NameN='#NameN#', Name2='#Name2#'
Where ID=#ID#
</CFQUERY>

Thanks

 

[csteinhilber]

Not quite sure what your record fields relate to... but the UPDATE command will update all records it finds that fit the given criteria.

So:

<CFQUERY NAME=&quot;updatedb&quot; DATASOURCE=&quot;K&quot;>
   UPDATE Newtable
   SET absorbed='#Absorbed#', NameN='#NameN#', Name2='#Name2#'
   WHERE NameN = 'Joe Smith'
</CFQUERY>

will update all records (0 - n) which currently have Joe as the NameN.
You just have to write your WHERE clause accordingly.

Of course... you want to be careful. With the wrong where clause, you could very easily update every record in the database with the exact same information... which probably isn't what you wanted to do.



-Carl

 

[CidMatrix]

Another solution would be to use CFGRID, which is what I did. By using this, all the records retrieve look like a spreadsheet. The user updates everything he wants and then when submitted, CFGRID will recognize the changes and updates the records accordingly. If you need more information on this let me know. You may have to upgrade the Java on the client and/or server, I sort of remember having to do something like this.

 

[nsukari]

CidMatrix, I want to use CFGRID, but I keep getting the message that my browser does not support Java2. How can I update?

 

[CidMatrix]

OK, first I'm not sure what browser you are using. I'm using IE 5.5. Next the version I'm using for Java is &quot;Java 2 Runtime Environment Standard Edition v1.3.1_07&quot; (at least that is what is being displayed in my &quot;Add/Remove Programs&quot;. As far as updating it, if I remember correctly I just went to Sun's website, looked for the download section, then JRE then find either the latest or the version I listed above. I believe the install is pretty straight forward. If you need anymore assistance, please don't hesitate to post and I'll see what can I do.

 

[csteinhilber]

nsukari's last question is the number one reason you don't use CFGRID in anything but an environment in which you can dictate who's using the page and the minimum browser requirements.

Using CFGRID in a open, customer-facing internet environment is suicide... either for your app, or for your tech support line... often both.

Not only is it dependent on the version of JRE you have, but it can even get hung out to dry when it runs up against some firewalls and even OSes. And even when it does work in your particular setup, it throws up a java security message that would send scitty users packing.

If you have a page that you know only one or two people are going to access, and you can physically sit at their workstation and ensure that they have the proper browser and JRE, then it's possible that CFGRID can save you some time. But frankly, it's fairly easy to replicate CFGRID's functionality using straight CFML. Wrap it all up in your own custom tag and/or CFC, and you'll have far less problems and support issues. And if you're doing any browser sniffing and can serve up IE5+ and NN6+ specific pages, calling the custom tag within an IFRAME makes it look exactly like the CFGRID.


-Carl

 

[CidMatrix]

Carl, you are absolutely right. I took for granted that the my users are right in front of my office AND that they run everything through a Citrix ICA client,so, I have complete control of the environment. Sorry about that nsukari.

 

[sgdragon78]

Hi nsukari,
What's wrong with your query? Seems ok to me~
<CFQUERY NAME=&quot;updatedb&quot; DATASOURCE=&quot;K&quot;>
UPDATE Newtable
SET absorbed='#Absorbed#', NameN='#NameN#', Name2='#Name2#'
Where ID=#ID#
</CFQUERY>

 

[nsukari]

The problem is that I want to be able to update multiple records at once and it won't allow me to do that. I get an error that says cannot update ID 5,8,45, etc. although Ik did not add any commas to ythe query. One record at a time updates fine.