Solved

Updating data with Case or IF statements

Posted on 2007-11-19
21
189 Views
Last Modified: 2013-12-16
Hi exerts,

I have an application that people use to submit request for web development inside my company. In this form there is a section call Budget Hours and the value is stored in a table in the database. As of now this value is not updateable once a value has been entered.

What my company wants to do, is to only to be able to update if the budget hours has increased...  Other wise not update. Once somebody assigns it an amount of hours you can't take any away you can only add hours to it.

thanks..

I'll start with 250 points but If I/you feel like it should be more, I'm always open to add more points if it needs to.

-r
0
Comment
Question by:vthunder70
  • 10
  • 8
  • 2
  • +1
21 Comments
 
LVL 4

Expert Comment

by:DavidTMoore
Comment Utility
If you could elaborate more on the environment in which this is being coded in it would be helpful but the general code would look something like this:


X = budget hours from database
Y = new budget hours
if(X < Y)
{
write Y to the database;
}


Sorry for the simple answer but without knowing more about the environment I can only give you pseudo-code.
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Here are two options.  I don't know your column types so I've used "cf_sql_integer" for example. Change as needed.
UPDATE YourTable

SET    BudgetHours =  

	   	CASE 	WHEN BudgetHours < <cfqueryparam value="#form.NewBudgetHours#" cfsqltype="cf_sql_integer">

                   THEN <cfqueryparam value="#form.NewBudgetHours#" cfsqltype="cf_sql_integer">

	     		ELSE 	BudgetHours 

		END

WHERE  RecordID = <cfqueryparam value="#form.RecordIDToUpdate#" cfsqltype="cf_sql_integer">
 
 

UPDATE 	YourTable

SET    	BudgetHours = <cfqueryparam value="#form.NewBudgetHours#" cfsqltype="cf_sql_integer">

WHERE  	RecordID 	= <cfqueryparam value="#form.RecordIDToUpdate#" cfsqltype="cf_sql_integer">

AND		BudgetHours < <cfqueryparam value="#form.NewBudgetHours#" cfsqltype="cf_sql_integer">

Open in new window

0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
you can consider adding a trigger to block any decreases

create trigger tg_upd_project on project
for update
as
if not update([Budget Hours]) return
if (select [Budget Hours] from inserted) < (select [Budget Hours] from deleted)
 raiserror('Budget amount cannot be decreased', 16, 1)
GO
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
assuming you are already handling database errors and displaying to the user, they will see the error message from the trigger when their update is blocked
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Yes, a trigger is another option.  You could catch the error with a cftry/cfcatch and display a message that the update is not allowed.
0
 

Author Comment

by:vthunder70
Comment Utility
well I don't want to use a trigger. I'm going to explore agx solution since I'm working with coldfusion.

my column data type is real
0
 

Author Comment

by:vthunder70
Comment Utility
i am going to up the points because I dont' think this is going to be easy

here is my code for the update statement and I can't seem to place the CASE on the right spot.

I believe I have to write the CASE somewhere around this area:
fltBudget = <cfif fltBudget IS "">NULL<cfelse>#fltBudget#</cfif></cfif>

am I right?
or way way off?
<cfquery name="qryUpdatePriority" datasource="#MYDATABASE#">

UPDATE tblContactUs

SET txtContact = N'#txtCCEContent#'

	         , strSubject = N'#strSubject#'

		<cfif IsDefined("fltBudget")>, 

		fltBudget = <cfif fltBudget IS "">NULL<cfelse>#fltBudget#</cfif></cfif>

			, dtmModified = GETDATE()

			<!---	if the group has changed then set the priority to 99 to put it at the bottom of the new group's list	--->

			<cfif fGroupChanged>, intPriority = 99</cfif>

			<cfif IsDefined("intLoggedByID")>, intLoggedByID = <cfif intLoggedByID IS "">0<cfelse>#intLoggedByID#</cfif></cfif>

			<cfif IsDefined("intAssignToID")>, intAssignToID = <cfif intAssignToID IS "">NULL<cfelse>#intAssignToID#</cfif></cfif>

 			<cfif IsDefined("intTestToID")>, intTestToID = <cfif intTestToID IS "">NULL<cfelse>#intTestToID#</cfif></cfif>

			, intGroupID = <cfif intGroupID IS "">NULL<cfelse>#intGroupID#</cfif>

			<cfif fPublish>

				<!---	publish/unpublish the webdev	--->

				<cfif btnPublish IS "publish">

					<cfset msg = ListAppend(msg, "published CCE:#intCCEID# - <b>#StringMax(strSubject,40)#</b>","|")>

					, dtmPublished = GETDATE()

				<cfelse>

					<cfset msg = ListAppend(msg, "unpublished CCE:#intCCEID# - <b>#StringMax(strSubject,40)#</b>","|")>

					, dtmPublished = NULL

					-- 30/01/06 TB get rid of unpublished priority

					, intPriority = NULL

				</cfif>

			<cfelseif fCompleted>

				<!---	mark the webdev as completed	--->

				<cfset msg = ListAppend(msg, "completed CCE:#intCCEID# - <b>#StringMax(strSubject,40)#</b>","|")>

				, dtmWhenDealtWith = GETDATE()

			</cfif>

		WHERE intContactUsID = <cfqueryparam value="#intCCEID#" cfsqltype="CF_SQL_INTEGER">;

	</cfquery>

Open in new window

0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
Comment Utility
Try updating the value only if "fltBudget" is both defined and numeric.  

But you should use cfqueryparam and scope all variables.
...

, strSubject = N'#strSubject#'
 

<cfif IsDefined("fltBudget") AND IsNumeric(fltBudget)>

   , fltBudget = CASE WHEN fltBudget < #fltBudget# THEN #fltBudget#

                      ELSE fltBudget

                 END

</cfif>
 

, dtmModified = GETDATE()

...

Open in new window

0
 

Author Comment

by:vthunder70
Comment Utility
OK.. so it works thanks, but I have a few questions. What does the IsNumeric do that can't be done without it?

also at first wasn't letting take away hours (which is what I want) but then I tryed to add hours or add hours to something that didn't have hours to beggin with so I made this change:

I added the following line:

WHEN fltBudget IS NULL THEN #fltBudget#

and it seems to be working fine now, but before I give you the points can you elaborate a little more. I'm really knew to coldFusion(I come from a year and half experience with asp.net 2.0 before that nothing)

thanks alot!
-r
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
> What does the IsNumeric do that can't be done without it?

The main reason is there is no point including "fltBudget" in the UPDATE if the variable value is an empty string "" or it isn't numeric.  But I wouldn't say that's the only way to do it.  Just one of the simpler options.

But again, I would recommend using cfqueryparam for your queries (and scoping variables).  Cfqueryparam provides some built-in validation, protection against sql injection. It also uses bind variables which can help boost performance in some cases, especially for statements executed multiple times.

> I tryed to add hours or add hours to something that didn't have hours to beggin with

Yes.  You could do either that or use ISNULL or COALESCE

     CASE WHEN ISNULL(fltBudget, 0) < #fltBudget# THEN ....
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:vthunder70
Comment Utility
how would I use the cfqueryparam in this situation?
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
I'll post an example, but what scope are these variables in

fltBudget
intAssignToID
..etc

ie Are they from a <form method="post" > ?  Also, I noticed you're using "IsDefined".  Are all of the fields really optional? I'm just wondering if there is a way to simplify the code..
0
 

Author Comment

by:vthunder70
Comment Utility
yes they are from a <form method="post">.

fltBudget is Real
intAssigntoID is ID.. yea thy are optional just because if a user donesn't want to assign time or what dpt. is going into she/he can update the rest of the form leaving empty values

thanks,
-r
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Right, but if the form fields are text boxes they will always exist on the action page.  Meaning IsDefined() is not needed.  You only need to use IsDefined for things like checkboxes, radio buttons, etc..

So are any of the form fields _not_  a text box?
0
 

Author Comment

by:vthunder70
Comment Utility
yes, they are a few dropdownlist menus besides that they are all txtboxes. The fields that we are working in this problem are all txtboxes
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Drop down lists always exist too.  _Multiple_ option select lists do not.  

Since IsDefined() will always be true for text fields,  if the user left the intLoggedByID field blank, the query would set  intLoggedByID = 0.  Is that what you want to happen?

<cfif IsDefined("intLoggedByID")>
, intLoggedByID = <cfif intLoggedByID IS "">0<cfelse>#intLoggedByID#</cfif>
</cfif>

I'm wondering if what you want to do is more like this

<!--- update the fields only if they entered a number maybe ..?--->
<cfif IsNumeric("intLoggedByID")>
, intLoggedByID = <cfif intLoggedByID IS "">0<cfelse>#intLoggedByID#</cfif>
</cfif>
0
 

Author Comment

by:vthunder70
Comment Utility
oh well I think we are going of track... what you mean by cfqueryparam, is how I otain the right data?

like for expample you say
>>intLoggedByID = 0
tha would never happen cause we have hidden fields with data to make the query for selecting, deleting and updating data that belongs to the selected item.
I just didn't want anyone to take away hours from a project.. just to add, and your solution works.. I was just wondering what is "'cfqueryparam"
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
The logic still seems wrong to me, but .. you know your form best ;-)

Like I said, cfqueryparam is used to pass values/parameters to a query.  It several benefits like type checking, protection against sql injection, bind variables, the ability to pass NULL's, etc.  Adobe recommends using it for all query parameters. You can read more about it here
http://livedocs.adobe.com/coldfusion/7/htmldocs/00000317.htm

Here is an example of how you might use it for your #form.fltBudget# variable.  Just pass it a value and cfsqltype.  




UPDATE 	tblContactUs

SET 	
 
 

txtContact = <cfqueryparam value="#form.txtCCEContent#" 

                    cfsqltype="cf_sql_varchar">

, strSubject = <cfqueryparam value="#form.strSubject#" 

                 cfsqltype="cf_sql_varchar">

<!--- skip the update if #form.fltBudget# is not a number --->

<cfif IsNumeric(form.fltBudget)>

, fltBudget = CASE WHEN ISNULL(fltBudget, 0) < 

                   <cfqueryparam value="#form.fltBudget#" 

                      cfsqltype="cf_sql_real">

                   THEN <cfqueryparam value="#form.fltBudget#" 

                           cfsqltype="cf_sql_real">

                   ELSE fltBudget

              END

</cfif>

...

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Ugh.  Watch out for the Ugly line wrapping.
0
 

Author Comment

by:vthunder70
Comment Utility
thanks for taking the time to go beyond the question to help me understand things a bit better.

I appreaciate all your help!!
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
You're welcome :-)  
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now