Skip to main content
P
pixelerium
Participating Frequently
March 3, 2009
Question

Updating db via Form

  • March 3, 2009
  • 6 replies
  • 1738 views
Currently we have 1 person updating staff profiles from our company. Recently they asked me to add a new field titled: Global Public Health Experience. Basically I added the section and radio buttons indicating a Yes or No answer. Everything on that page is saved onto a MySQL database, which I respectively also created a new section for the new field.

Once I did everything and ran a test I realized it was reading the database fine but not updating it if the answer changed from Yes to No or vice-versa. I changed the answer in the database itself and it updated perfectly but when i try to update it via the form, event though I don't get any error messages, it doesn't apply the updates onto the database.

Here's everything I have.

First I run the SQL query that updates database (what you see in bold is the section for this new field):
<cfquery name="bioupdate" datasource="sph_Edit">
UPDATE tblpersonnel
SET tblPers_gphe='#gphe#'
WHERE tblPers_ID_pk='#URL.tblPers_ID_pk#'
</cfquery>


Then I have a query reading the database (the bold is for the new field):
<cfquery name="bio" datasource="sph_Read">
SELECT * from tblpersonnel WHERE tblPers_ID_pk='#URL.tblPers_ID_pk#'
</cfquery>

<!-- Set Paramaters -->
<cfset bioStruct=StructNew()>
<cfset bioStruct.gpheY="no" >
<cfset bioStruct.gpheN="no" >


Then I created conditions to select the correct answer on the form:
<cfif #bio.tblPers_gphe# EQ 'y'>
<cfset bioStruct.gpheY="yes">
<cfelse>
<cfset bioStruct.gpheN="yes">
</cfif>



Finally here's the field itself (nothing bold here, it's all for the new field):

<div class="inputtitlelong">Global Public Health Experience</div>
<div align="center" class="htmlinputlong2" id="gphexperience">
<cfinput name="gphe" type="radio" required="yes" message="Please indicate if this member has Global Public Health Experience" value="y" checked="#bioStruct.gpheY#" />Yes
<cfinput name="gphe" type="radio" value="n" checked="#bioStruct.gpheN#" />No
</div>


Please, if you notice something's wrong let me know. I've been struggling with this issue for some time now and still can't figure out what the heck is causing it not to work. Thanks for all your help in advance!
This topic has been closed for replies.

6 replies

A
Anonymous
March 16, 2009
Pixelerium,
You should put a set of CFtry and CFcatch tag around this section, and dump out the CFcatch.sql to see what is actually going on.
Also, this is okay for testing, but , due to the risk of SQL injection, you should never us a URL arugument without the cfqueryparam tag.

<cftry>
<cfcatch>
<cfoutput> <br />
Message =
#cfcatch.message#<br />
ExtendedInfo =
#cfcatch.ExtendedInfo# <br />
Detail =
#cfcatch.detail# <br />
SQL =
#cfcatch.Sql#
</cfoutput>
</cfcatch>
</cftry>
P
pixeleriumAuthor
Participating Frequently
March 26, 2009

Sorry for wasting everyone's time. It was a human error causing it not to work. The form action property was pointed to the original page and not the "test" one I was updating. That's why it wasn't capturing any updates.

Thanks for your time through.
D
Dan_Bracuk
Inspiring
March 4, 2009
You answered the question correctly. I don't work with mysql so I must now defer to those who do.

D
Dan_Bracuk
Inspiring
March 4, 2009
What is the datatype? What is the value coming from your radio button?
P
pixeleriumAuthor
Participating Frequently
March 4, 2009
Well as far as I know you don't "need" to set a data type in Coldfusion because it does it on it's own. But in mysql the data type is ENUM.

• MySQL: enum('n', 'y')
• Data coming from radio button is: y & n

Did I answer your question correctly?
D
Dan_Bracuk
Inspiring
March 3, 2009
First, what was the value of gphe?

Next, if you run a select query immediately after your update, and dump it, what do you see?

Your sentence, "The form is still reflecting the data in the database but no updating it." indicates that maybe the database really is being updated but that the problem lies in displaying the most recent data.
P
pixeleriumAuthor
Participating Frequently
March 4, 2009
Dan Bracuk, I have admin access to the database and each time I submit the form for testing purposes I also look at the data in the database to verify if it's working, but it doesn't. However, when I update the database directly and refresh the form, it reflects the new updates. Something I might have not mentioned initially is that when I make changes to other fields on the form the database is updated successfully.

That's why I copied/paste other radio button code and just changed the coding respectively but its not working. I don't know if this makes a difference but in the actual database I used - enum('n', 'y') - for the no/yes answer to the new field.

Thanks for all your help, please advise.
D
Dan_Bracuk
Inspiring
March 3, 2009
cfdump is your freind.
Before this query
<cfquery name="bioupdate" datasource="sph_Edit">
UPDATE tblpersonnel
SET tblPers_gphe='#gphe#'
WHERE tblPers_ID_pk='#URL.tblPers_ID_pk#'
</cfquery>

cfdump the gphe variable to see what it is. What's the scope of that variable anyway? Also, where do the url variables come from if you are submitting a form? Are you using method = "get"?
P
pixeleriumAuthor
Participating Frequently
March 3, 2009
Dan Bracuk: I did what you suggested and didn't get any error message but it didn't fix the issue. The form is still reflecting the data in the database but no updating it.

This is what I did (please let me know if I did something wrong):
<cfdump var="#gphe#">
<cfquery name="bioupdate" datasource="sph_Edit">
UPDATE tblpersonnel
SET tblPers_gphe='#gphe#'
WHERE tblPers_ID_pk='#URL.tblPers_ID_pk#'
</cfquery>
M
Mi-ul-vmijiI
Known Participant
March 3, 2009
Try

<cfinput name="gphe"
type="radio"
required="yes"
message="Please indicate if this member has Global Public Health Experience" value="y"
<cfif bioStruct.gpheY eq "yes">checked="checked"</cfif>
/>Yes
P
pixeleriumAuthor
Participating Frequently
March 3, 2009
I tried your suggestion Mi-ul but it didn't work. I got an error (below):

Invalid token 'c' found on line 507 at column 2.
The CFML compiler was processing: * a cfinput tag beginning on line 503, column 18.

The error occurred in D:\sphweb\services\hr\faculty.staff.edit_test.cfm: line 507
505 : required="yes"
506 : message="Please indicate if this member has Global Public Health Experience" value="y"
507 : <cfif bioStruct.gpheY eq "yes">checked="checked"</cfif>
508 : />Yes
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices