update the SQL Table

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...
LVL 4
koilaAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
(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
 
koilaAuthor Commented:
<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
 
_agx_Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
koilaAuthor Commented:
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
 
koilaAuthor Commented:
SQL 2008.
0
 
koilaAuthor Commented:
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
 
koilaAuthor Commented:
Excellent I figure it out, I will have another question later on how I will be alble to display data.
0
 
koilaAuthor Commented:
Super !!
0
 
koilaAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.