Solved

update the SQL Table

Posted on 2013-06-11
9
201 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
populate select menu with array from another select menu 3 84
Comparing 2 Lists Coldfusion 8 63
Update cached table in H2 database 6 97
Combining Queries 7 54
This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 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