vijay_wv
asked on
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,primary key"> 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.coursety pe#',
coursename='#form.coursena me#',
WHERE id='#ID#'</cfquery>
and its working fine but courserate is not updating in the database. Any help????????
Thanks.
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
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.coursety
coursename='#form.coursena
WHERE id='#ID#'</cfquery>
and its working fine but courserate is not updating in the database. Any help????????
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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",C ourseName) >
<CFSET variables.courseRate = 60>
</cfif>
<cfquery name="update" datasource="databasename">
Update tablename
Set coursetype='#form.coursety pe#',
coursename='#form.coursena me#'
<CFIF VAL(variables.courseRate)>
,courseRate = #variables.courseRate#
</CFIF>
WHERE id='#ID#'
</cfquery>
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,
<CFSET variables.courseRate = 60>
</cfif>
<cfquery name="update" datasource="databasename">
Update tablename
Set coursetype='#form.coursety
coursename='#form.coursena
<CFIF VAL(variables.courseRate)>
,courseRate = #variables.courseRate#
</CFIF>
WHERE id='#ID#'
</cfquery>
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.
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.
Try
<cfquery name="update" datasource="databasename">
Update tablename
Set coursetype='#form.coursety
coursename='#form.coursena
WHERE id=#ID#</cfquery>