Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

update the SQL Table

Posted on 2013-06-11
9
Medium Priority
?
216 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
Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

 
LVL 4

Author Comment

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

Accepted Solution

by:
_agx_ earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Integration Management Part 2
Suggested Courses

972 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