asked on

Updating data with Case or IF statements

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

DavidTMoore

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.

_agx_

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

imitchie

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

imitchie

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

_agx_

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.

vthunder70

ASKER

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

vthunder70

ASKER

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

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vthunder70

ASKER

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

_agx_

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

vthunder70

ASKER

how would I use the cfqueryparam in this situation?

_agx_

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

vthunder70

ASKER

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

_agx_

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?

vthunder70

ASKER

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

_agx_

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


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

vthunder70

ASKER

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"

_agx_

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

_agx_

Ugh. Watch out for the Ugly line wrapping.

vthunder70

ASKER

thanks for taking the time to go beyond the question to help me understand things a bit better.

I appreaciate all your help!!

_agx_

You're welcome :-)