Solved

update the SQL Table

Posted on 2013-06-11
9
197 Views
Last Modified: 2013-06-13
Hello,

I have a form in coldfusion when I filled the form, it will add the entry to the database.  I would like to change it that it will replace my entry in the database.

for example:

user_id  ID_number   firstname lastname          contactme
11             ABC               Jim           Loyal                   NO
11             ABC               Jim           Loyal                   YES

So my first entry was no then yes... So i would like to have only one entry for Jim. How I can change the insert script below...
0
Comment
Question by:koila
  • 7
  • 2
9 Comments
 
LVL 4

Author Comment

by:koila
ID: 39238603
<cfquery name="logAnnualDues" datasource="#application.datasource#" username="#application.username#" password="#application.password#">
			INSERT INTO volunteers (user_id, id_number, firstname, lastname, NewProfileUpdated, contactme, ContactMeSpecify, CMTEE_PREF_NEW, ISSUES, PRACTICE_AREA_SPEC, YEARS_EXP, otherActVolWork, otherActVolWorkSpecify, otherNonActVolWork, otherNonActVolWorkSpecify, SPEC_INT, SPEC_SKILLS_NEW, ContactedYou, ContactedYouSpecify, PermissionProfile, PermissionProfileSpecify) 
                VALUES (
                <cfqueryparam value = "#form.user_id#" CFSQLType = "CF_SQL_INTEGER" />, 
                <cfqueryparam value = "#form.id_number#" cfsqltype="CF_SQL_INTEGER" />, 
                <cfqueryparam value = "#form.firstname#" CFSQLType = "CF_SQL_VARCHAR" /> , 
                <cfqueryparam value = "#form.lastname#" CFSQLType = "CF_SQL_VARCHAR" /> , 
              '#DateFormat(NOW())#',
                <cfqueryparam value = "#form.contactMe#" cfsqltype="cf_sql_char" />,              
                <cfqueryparam value = "#form.contactMeSpecify#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.CMTEE_PREF_NEW#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.ISSUES#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.PRACTICE_AREA_SPEC#" cfsqltype="cf_sql_varchar" />,                                                                
                <cfqueryparam value = "#form.YEARS_EXP#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.otherActVolWork#" cfsqltype="cf_sql_char" />,          
                <cfqueryparam value = "#form.otherActVolWorkSpecify#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.otherNonActVolWork#" cfsqltype="cf_sql_char" />,          
                <cfqueryparam value = "#form.otherNonActVolWorkSpecify#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.SPEC_INT#" cfsqltype="cf_sql_varchar" />,           
                <cfqueryparam value = "#form.SPEC_SKILLS_NEW#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.ContactedYou#" cfsqltype="cf_sql_char" />,          
                <cfqueryparam value = "#form.ContactedYouSpecify#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.PermissionProfile#" cfsqltype="cf_sql_char" />,          
                <cfqueryparam value = "#form.PermissionProfileSpecify#" cfsqltype="cf_sql_varchar" />
                )                                   
    </cfquery>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 39238739
I have a form in coldfusion when I filled the form, it will add the entry to the database.  I would like to change it that it will replace my entry in the database.

It sounds like you should have an add/edit form instead. Then present the user with a link to "ADD" a new user OR select an existing user from a list, and "EDIT" that record.

- If "ADD", display an empty form. When submitted, INSERT a new record.
- If "EDIT user" link, populate the form with the existing info from the db. When submitted, UPDATE the existing record based on the unique ID.  Something like

<cfquery ....>
    UPDATE   volunteers 
    SET          firstname = <cfqueryparam value = "#form.firstname#" CFSQLType = "CF_SQL_VARCHAR" />
                ,  lastname = <cfqueryparam value = "#form.lastname#" CFSQLType = "CF_SQL_VARCHAR" /> 
                , ... other columns 
                ,  PermissionProfileSpecify = <cfqueryparam value = "#form.PermissionProfileSpecify#" cfsqltype="cf_sql_varchar" />

    WHERE  ThePrimaryKeyCol = <cfqueryparam value = "#form.thePrimaryKeyValue#" CFSQLType = "CF_SQL_INTEGER" />

</cfquery>

Open in new window




Is that what you're trying to do? (BTW - What's your database type: MS SQL 2005, mySQL5, ....?)
0
 
LVL 4

Author Comment

by:koila
ID: 39238934
Hello Agx,

It's a SQL Database Table.   The form is working fine and the data is being insert. The only problem is if I filled the form again an new entry will be inserted.

I don't know the new entry to be insert it as a second entry.  I would like to update the existant entry.

So if there is any entry for this user_id update if not insert.

how I can change that script below.

So on this confirmation, I have only this code where all the information are being transmited via a online form and it's process.

<cfquery name="logAnnualDues" datasource="#application.datasource#" username="#application.username#" password="#application.password#">
			INSERT INTO volunteers (user_id, id_number, firstname, lastname, NewProfileUpdated, contactme, ContactMeSpecify, CMTEE_PREF_NEW, ISSUES, PRACTICE_AREA_SPEC, YEARS_EXP, otherActVolWork, otherActVolWorkSpecify, otherNonActVolWork, otherNonActVolWorkSpecify, SPEC_INT, SPEC_SKILLS_NEW, ContactedYou, ContactedYouSpecify, PermissionProfile, PermissionProfileSpecify) 
                VALUES (
                <cfqueryparam value = "#form.user_id#" CFSQLType = "CF_SQL_INTEGER" />, 
                <cfqueryparam value = "#form.id_number#" cfsqltype="CF_SQL_INTEGER" />, 
                <cfqueryparam value = "#form.firstname#" CFSQLType = "CF_SQL_VARCHAR" /> , 
                <cfqueryparam value = "#form.lastname#" CFSQLType = "CF_SQL_VARCHAR" /> , 
              '#DateFormat(NOW())#',
                <cfqueryparam value = "#form.contactMe#" cfsqltype="cf_sql_char" />,              
                <cfqueryparam value = "#form.contactMeSpecify#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.CMTEE_PREF_NEW#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.ISSUES#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.PRACTICE_AREA_SPEC#" cfsqltype="cf_sql_varchar" />,                                                                
                <cfqueryparam value = "#form.YEARS_EXP#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.otherActVolWork#" cfsqltype="cf_sql_char" />,          
                <cfqueryparam value = "#form.otherActVolWorkSpecify#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.otherNonActVolWork#" cfsqltype="cf_sql_char" />,          
                <cfqueryparam value = "#form.otherNonActVolWorkSpecify#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.SPEC_INT#" cfsqltype="cf_sql_varchar" />,           
                <cfqueryparam value = "#form.SPEC_SKILLS_NEW#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.ContactedYou#" cfsqltype="cf_sql_char" />,          
                <cfqueryparam value = "#form.ContactedYouSpecify#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.PermissionProfile#" cfsqltype="cf_sql_char" />,          
                <cfqueryparam value = "#form.PermissionProfileSpecify#" cfsqltype="cf_sql_varchar" />
                )                                   
    </cfquery>

Open in new window

0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 4

Author Comment

by:koila
ID: 39238937
SQL 2008.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 39239122
(Edit) You can't do it in a single statement.  You need to lookup the table's PK, first.  If the record exists, run the UPDATE. Otherwise, run the INSERT code.  To be thread safe it needs locking, but here's the general idea:

           <cfquery name="findRecord" ...>
                     SELECT  ThePrimaryKey
                     FROM    YourTable
                     WHERE  ThePrimaryKey = <cfqueryparam value="#form.ThePrimaryKey#" ...>
           </cfquery>

            <cfif findRecord.recordCount>
                    record found. run UPDATE code here
            <cfelse>
                    not found. run INSERT code here.
            </cfif>

I'm not using SQL Server 2008, but I've read that you can use the MERGE command to do something similar in one statement.

http://www.databasejournal.com/features/mssql/article.php/3739131/UPSERT-Functionality-in-SQL-Server-2008.htm
0
 
LVL 4

Author Comment

by:koila
ID: 39242133
Hello Agx,

You are the best, thank you, I did the first part of the script and it's working the only thing i'm missing is the update code here.

record found. run UPDATE code here


How can you switch this script from insert to update.

<cfquery name="findRecord" datasource="#application.datasource#" username="#application.username#" password="#application.password#">
                     SELECT  user_id 
                     FROM    volunteers
                     WHERE  volunteers.user_id= <cfqueryparam value="#form.user_id#" CFSQLType = "CF_SQL_INTEGER" />
           </cfquery>


           <cfif findRecord.recordCount>
                   hello 

             <!-- I want an update code here right now nothing happens, no second data is coming to the table which is good -->

            <cfelse>
                    <cfquery name="logAnnualDues" datasource="#application.datasource#" username="#application.username#" password="#application.password#">
			INSERT INTO volunteers (user_id, id_number, firstname, lastname, NewProfileUpdated, contactme, ContactMeSpecify, CMTEE_PREF_NEW, ISSUES, PRACTICE_AREA_SPEC, YEARS_EXP, otherActVolWork, otherActVolWorkSpecify, otherNonActVolWork, otherNonActVolWorkSpecify, SPEC_INT, SPEC_SKILLS_NEW, ContactedYou, ContactedYouSpecify, PermissionProfile, PermissionProfileSpecify) 
                VALUES (
                <cfqueryparam value = "#form.user_id#" CFSQLType = "CF_SQL_INTEGER" />, 
                <cfqueryparam value = "#form.id_number#" cfsqltype="CF_SQL_INTEGER" />, 
                <cfqueryparam value = "#form.firstname#" CFSQLType = "CF_SQL_VARCHAR" /> , 
                <cfqueryparam value = "#form.lastname#" CFSQLType = "CF_SQL_VARCHAR" /> , 
              '#DateFormat(NOW())#',
                <cfqueryparam value = "#form.contactMe#" cfsqltype="cf_sql_char" />,              
                <cfqueryparam value = "#form.contactMeSpecify#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.CMTEE_PREF_NEW#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.ISSUES#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.PRACTICE_AREA_SPEC#" cfsqltype="cf_sql_varchar" />,                                                                
                <cfqueryparam value = "#form.YEARS_EXP#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.otherActVolWork#" cfsqltype="cf_sql_char" />,          
                <cfqueryparam value = "#form.otherActVolWorkSpecify#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.otherNonActVolWork#" cfsqltype="cf_sql_char" />,          
                <cfqueryparam value = "#form.otherNonActVolWorkSpecify#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.SPEC_INT#" cfsqltype="cf_sql_varchar" />,           
                <cfqueryparam value = "#form.SPEC_SKILLS_NEW#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.ContactedYou#" cfsqltype="cf_sql_char" />,          
                <cfqueryparam value = "#form.ContactedYouSpecify#" cfsqltype="cf_sql_varchar" />,
                <cfqueryparam value = "#form.PermissionProfile#" cfsqltype="cf_sql_char" />,          
                <cfqueryparam value = "#form.PermissionProfileSpecify#" cfsqltype="cf_sql_varchar" />
                )                                   
    </cfquery>
            </cfif>

Open in new window


thank you for your help!!
0
 
LVL 4

Author Comment

by:koila
ID: 39242472
Excellent I figure it out, I will have another question later on how I will be alble to display data.
0
 
LVL 4

Author Closing Comment

by:koila
ID: 39242473
Super !!
0
 
LVL 4

Author Comment

by:koila
ID: 39245840
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Coldfusion session variables in scheduled job 1 357
dynamic form fields and inserting into database 3 47
Fixing Old Legacy Code 1 41
Coldfusion - rename files in a folder 4 22
The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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