RemoveChars and update database

I'm updating a form_action.cfm page to update a sql table... the numbers after RANKING_PL could be 0-400+ (see paste #1 below). I need help with the routine that will remove RANKING_PL leaving just the number = number (e.g. RANKING_PL65 = 0 would be come 65 = 0). 65 is the unique id for the sql table and the 0 is the value submitted from the form. This value could be 0-4 only. The second part which I assume would be just like the first one is YEARUSED65=0... after the manipulation would end up being 65=0. This value would be 0 or 2001-2007.

I'm not very good with loops but I'm assuming the values could be put into a comma delimited list and assigned a variable name and that variable name woudl be used in teh SQL query as in...

In summary, I need the above list (which is just a snippet of data <!--- paste ##1 --->) converted to an SQL query like this with minimal database hits. The list could contain any number from 1 - 400 values being submitted.

the following solution makes use of sql server's xml capabilities, uses the minimum number of database executions necessary, and wraps the entire database call with coldfusion's transaction tags to handle

Daniel, you are amazing! I've never used the cfloop collection tag before. I'll have to read up on that ...

This works perfectly for updating if the values already exists in the database. After looking at the database results more closely, some assessments (rankings) have not been "ranked"; for example if an employee was just added, he/she will not have any rankings for the skills in the system and they will be unable to make any updates.

What is your recommendation for...

1) Updating values that are NOT 0 (the web page default values are 0). Can I add a select statement (eg Select * from irp_ranking where form.ranking_pl <> 0 and form.yearused <> 0) and update the other form values submitted?
OR
2) Updating values that exists and inserting ones that do not exists?

Option 2 sounds more logical, but not being familiar with the cfloop collection, I'm not even sure if either option is plausible.

Okay, I've made some head way here... but you I can't have nested <cfquery> statements. I put in the cfoutput in the code so it would write out the query to the web page for me. From there, I can copy the output and paste it into SQL and run the query and it does so perfectly. How do I get around the nested <cfquery> statements? Below is my code...

the following solution makes use of sql server's xml capabilities, uses the minimum number of database executions necessary, and wraps the entire database call with coldfusion's transaction tags to handle any database errors.

it loops through the form variables to create an xml string that is sent to sql server to create a parsed xml document that is used to insert the results into a table variable. records in the table variable that exists in IRP_RANKING are updated, while records that don't are inserted into IRP_RANKING.

the entire database transaction is wrapped with cf's transaction tags, that allow the database call to be an all or nothing affair, meaning either the entire database transaction is committed, or the entire thing is rolled back.

you can take a look at SQL Server's Books On Line and CF's documentation for more detailed info, but let me know if anything is not clear.

I'm speachless. I'll give it a shot and let you know. I am entering uncharted territory with cftransaction. One quick question... the last cfif statements... is this where I would put "Selections have been saved/updated" or "An error has occurred, please go back and try again." type of message?

One thing that worries me about your code is that I might not be able to edit it later if a change is needed. :) Maybe after inspecting it closer, it will make more sense. :) Give me a few days to look this over and I'll let you know. :)

the cftransaction tag allows you to indicate a block of database statements that should all be done, or not be done (or rolled back, if some of the statements have already been executed) if there is an error. the cfquery tag is enclosed within a cftry tag, meaning, if something goes wrong, go to the code in the cfcatch block. you'll notice that I set the variable "transactionAction" to "commit", and only if there is an error and it goes to the cfcatch block, will it be set to "rollback". The <cftransaction> tag is funny in that it used for the entire block without any attributes, but it also used within a block of <cftransaction> tags to indicate a database action:

"commit" in the inner <cftransaction> tag indicates that all the database statements executed should be left as is; "rollback" indicates that any database statements that were executed should be reversed so that the data in the database is left as it was prior to the transaction being executed. in essence, it allows your database statements be an all or nothing affair.

the section after the <cftransaction> block is indeed where you can determine if the database statements were executed succesfully, and if not, to indicate that there has been a database error of some kind.

in regards to the actual database statements, it may be helpful for you to have the database return a recordset at different points in the code to see what is going on. please let me know what questions you have about any part of the code. this really is the most efficient way to insert and update the form values into the database, and allows you to handle any errors that may occur. if you haven't already, i'd definitely turn the cf debugging options on in your development of this page, as there is some info that is generated that helps clarify exactly what database transactions are going on.

My apologies on the delay in getting back to you, I had another issue I had to tend to and now I can focus on this again. I read through the code and I <em>think</em> I understand it but I could no way come up with that on my own... or at least not yet. :)

Thanks for the GREAT information and the explanation and examples. If you have teammates, ther are lucky to have a talented guy like you.

Featured Post

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ? If yes, then this article is for you !
First we need a table user_email with columns user_id , email , sub…