Solved

cfupdate and sql update

Posted on 2004-03-23
5
279 Views
Last Modified: 2013-12-24
Hello,

cfupdate is working fine with MS ACCESS but when I changed my database to SQL SERVER 2000 for some reason its not working. Is there any way to update the database with cfupdate?
I tried <cfupdate datasource="datasourcename" tablename="tablename" formfields="field1,primarykey"> and working in SQL SERVER 2000 but when I add more form fields its not working.

I tried typical update sql command too but one of my formfield is depend upon the cfif statement like:
<cfif #CourseType# CONTAINS "student" AND #courseName# IS "Onsite">
<cfset form.courseRate="80">
</cfif>

with this kind of loops cfupdate is working fine but when i use
<cfquery name="update" datasource="databasename">
Update tablename
Set coursetype='#form.coursetype#',
coursename='#form.coursename#',
WHERE id='#ID#'</cfquery>

and its working fine but courserate is not updating in the database. Any help????????
Thanks.

0
Comment
Question by:vijay_wv
[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
  • 2
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
danrosenthal earned 500 total points
ID: 10661973
you can put CFIF statements directly in your CFQUERYs....

<cfquery name="update" datasource="databasename">
   Update tablename
   Set coursetype='#form.coursetype#',
   coursename='#form.coursename#'
  <cfif CourseType CONTAINS "student" AND courseName IS "Onsite">
      ,courseRate=80
  </cfif>
   WHERE id='#ID#'
</cfquery>
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 10662008
I think the problem is that id is most likely a numeric field and you have it in single quotes.

Try
<cfquery name="update" datasource="databasename">
Update tablename
Set coursetype='#form.coursetype#',
coursename='#form.coursename#',
WHERE id=#ID#</cfquery>
0
 

Author Comment

by:vijay_wv
ID: 10662263
but what if I have 7 courses and 3 registration types = 21 loops. I can't write 21 times is there any way to write it in one loop?
0
 
LVL 15

Expert Comment

by:danrosenthal
ID: 10662499
You can use
CFSWITCH/CFCASE or CFIF/CFELSE to drill down all the possibile combinations of course rates and set a page variable (Not a form variable) for instance "VARIABLES.COURSERATE"  Then insert that into your database.
Basic Example:

<!--- SET DEFAULT VALUE --->
<CFSET variables.courseRate = 0>
<cfif CourseType CONTAINS "student" AND courseName IS "Onsite">
   <CFSET variables.courseRate = 80>
<cfelseif LISTFIND("accounting,math,science",CourseName)>
   <CFSET variables.courseRate = 60>
</cfif>

<cfquery name="update" datasource="databasename">
   Update tablename
   Set coursetype='#form.coursetype#',
   coursename='#form.coursename#'
  <CFIF VAL(variables.courseRate)>
   ,courseRate = #variables.courseRate#
   </CFIF>
   WHERE id='#ID#'
</cfquery>
0
 

Author Comment

by:vijay_wv
ID: 10662635
Dan,
you are really great........its just working perfect.

Could you please answer one more last question? after i submit the above form, i have a payment form field and its data type is money in sql server 2000. But for some reason its giving me an error The value "" cannot be converted to a number. Actually, i already posted this question.
0

Featured Post

Don't Miss ATEN at InfoComm 2017!

Visit booth #2167 to see the  new ATEN VM3200 32 x 32 Modular Matrix Switch. Other highlights include the VE8950 4K HDMI Over IP Extender, VS1912 12-Port DP Video Wall Media Player  and VK2100 ATEN Control System. Register now with Free Pass Code ATEN288!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem to go to Web page 2 140
cookies analysis tools 2 111
htaccess block all countries but the US 4 97
how should I go about configuring this in IIS 4 15
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 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