Solved

Updating data with Case or IF statements

Posted on 2007-11-19
21
199 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
[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
  • 10
  • 8
  • 2
  • +1
21 Comments
 
LVL 4

Expert Comment

by:DavidTMoore
ID: 20315851
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_
ID: 20315883
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
ID: 20315988
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 25

Expert Comment

by:imitchie
ID: 20316005
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_
ID: 20316341
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
ID: 20319825
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
ID: 20320180
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
ID: 20321468
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
ID: 20322103
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_
ID: 20322211
> 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
 

Author Comment

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

Expert Comment

by:_agx_
ID: 20322344
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
ID: 20322392
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_
ID: 20322420
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
ID: 20322458
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_
ID: 20322589
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
ID: 20322699
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_
ID: 20322899
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_
ID: 20322924
Ugh.  Watch out for the Ugly line wrapping.
0
 

Author Comment

by:vthunder70
ID: 20322940
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_
ID: 20322959
You're welcome :-)  
0

Featured Post

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What Is an Error? 2 59
sql views 3 55
Data architecture learning. 17 43
Need definitions 2 17
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

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