Solved

update the SQL Table

Posted on 2013-06-11
9
195 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 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