asked on 3/23/2004

cfupdate and sql update

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.

Web Servers

Last Comment

vijay_wv

3/23/2004

ASKER CERTIFIED SOLUTION

danrosenthal

3/23/2004

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

mrichmon

3/23/2004

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>

vijay_wv

3/23/2004

ASKER

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?

danrosenthal

3/23/2004

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:


<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>

vijay_wv

3/23/2004

ASKER

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.