Solved

Updating data with Case or IF statements

Posted on 2007-11-19
21
190 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
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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Search combo error "Data Type Mismatch in Criteria Expression" 2 51
report returning null 21 79
Update cached table in H2 database 6 42
Need a starter for ETL protocol? 4 39
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
The purpose of this video is to demonstrate how to insert an Iframe into WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Open Page or Post…

911 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

24 Experts available now in Live!

Get 1:1 Help Now