koila
asked on
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...
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...
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>
Is that what you're trying to do? (BTW - What's your database type: MS SQL 2005, mySQL5, ....?)
ASKER
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.
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>
ASKER
SQL 2008.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
thank you for your help!!
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>
thank you for your help!!
ASKER
Excellent I figure it out, I will have another question later on how I will be alble to display data.
ASKER
Super !!
ASKER
Hello Agx,
Could you please assist me on this questions.
https://www.experts-exchange.com/questions/28156857/How-do-you-display-a-checked-a-radio-button-data-from-the-table-into-a-form-when-the-field-is-'YES'.html
Could you please assist me on this questions.
https://www.experts-exchange.com/questions/28156857/How-do-you-display-a-checked-a-radio-button-data-from-the-table-into-a-form-when-the-field-is-'YES'.html
ASKER
Open in new window