Display and Calculate a number between two different fields stored in my database

Hi Im not sure if i posted this in right section or not becuase im not sure what section my query falls under but basically i would like to:

Calculate a number between two different fields stored in my database and store that number back in.

For example. Employee has 20 days holday in Days_Left Feild and in Holiday_Table they have Days_Requested as 6 days and that status has been approved.

I am now wondering as soon as the status becomes approved, how i can get 20 - 6 and put 14 back into Days_Left field. It needs to be done just for that employee.


Im not sure how to go about doing this so if anyone can guide me how i can do this, ill really appreciate it.
 

p.s 

Im using SQL Server 2005,  ASP.NET VB Script. in Visiual Studio 2005. 

0
djslick
2/15/2008 4:15:27 PM
asp.net.object-datasource 16182 articles. 0 followers. Follow

6 Replies
647 Views

Similar Articles

[PageSpeed] 22
Get it on Google Play
Get it on Apple App Store

If you are using stored procedures (which I would recommend) you can do that calculation right in the procedure. 

create procedure UpdateDays
@PK int 
@Days_Requested float 
as 
Update myTable set Days_Left = DaysLeft - @Days_Requested Where PrimaryKey = @PK
Of course, replace PK with your primary key, and myTable with your table name.  You can also update other fields as well at this time.
 

 


Rachel Appel
MVP - ASPInsider
0
RJA
2/15/2008 4:42:47 PM

Thanks for the quick reply.

 I just done that and it works perfect. The only problem is that i only want it to update if the Status = Approved

this is how it looks like at the moment:

(
@AnnualLeave_ID int,
@EmpAnnualLeave_ID int,
@AL_Status nvarchar(50),
@Days_Requested float
)

UPDATE Annual_Leave_Request

SET AL_Status = @AL_Status

WHERE AnnualLeave_ID = @AnnualLeave_ID

UPDATE Employee

SET AnnualLeave_Left = AnnualLeave_Left - @Days_Requested

WHERE Emp_ID = @EmpAnnualLeave_ID

Can i put an IF statement in there wher only update employee table if STATUS = Approved ? 

0
djslick
2/15/2008 4:56:48 PM

djslick:
Can i put an IF statement in there wher only update employee table if STATUS = Approved ? 
 

You can just add that to the where clause (see bold text)

(
@AnnualLeave_ID int,
@EmpAnnualLeave_ID int,
@AL_Status nvarchar(50),
@Days_Requested float
)

UPDATE Annual_Leave_Request

SET AL_Status = @AL_Status

WHERE AnnualLeave_ID = @AnnualLeave_ID

UPDATE Employee

SET AnnualLeave_Left = AnnualLeave_Left - @Days_Requested

WHERE Emp_ID = @EmpAnnualLeave_ID AND AL_Status = 'Approved'


 


Rachel Appel
MVP - ASPInsider
0
RJA
2/15/2008 5:29:35 PM

The AL_Status Field is in the Holiday_Table so it does not recognise it when i update the employee table.

Is there anyway i can iniate the update employee only if the AL_Status  = 'approved' ? 

 Else do nothing


 

0
djslick
2/15/2008 5:44:05 PM

 Sorry, I read that as if the status were in the employee table.  This should work:

(
@AnnualLeave_ID int,
@EmpAnnualLeave_ID int,
@AL_Status nvarchar(50),
@Days_Requested float
)

UPDATE Annual_Leave_Request

SET AL_Status = @AL_Status

WHERE AnnualLeave_ID = @AnnualLeave_ID

IF @Al_Status = 'Approved'

BEGIN

UPDATE Employee

SET AnnualLeave_Left = AnnualLeave_Left - @Days_Requested

WHERE Emp_ID = @EmpAnnualLeave_ID

END
 

If you need to check the status as it is in the table, not the parameter that's passed in then do this:

 

(
@AnnualLeave_ID int,
@EmpAnnualLeave_ID int,
@AL_Status nvarchar(50),
@Days_Requested float
)

UPDATE Annual_Leave_Request

SET AL_Status = @AL_Status

WHERE AnnualLeave_ID = @AnnualLeave_ID

DECLARE @STATUS nvarchar(50) 

SET @Status = (SELECT AL_Status from annual_leave_request where annual_leave_id = @annualleave_id)

IF @Status = 'Approved' 

BEGIN

UPDATE Employee

SET AnnualLeave_Left = AnnualLeave_Left - @Days_Requested

WHERE Emp_ID = @EmpAnnualLeave_ID

END
Rachel Appel
MVP - ASPInsider
0
RJA
2/15/2008 5:54:27 PM

 Thank you sooo much, i used the second example and that worked like a treat. i was thinking that what i was doing was so complicated but infact its so simple.

Thank you once again 

0
djslick
2/15/2008 6:09:11 PM
Reply:

Similar Artilces:

displaying database fields in different objects
hi, i have a listbox that is bound to a database and shows all enteries under TITLE. I have a label next to listbox that i want to display a SOLUTION (from same database table as TITLE)  what i want to happen is that when a user clicks on a TITLE it puts the corresponding solution into the label any ideas?www.the-web-shed.com Very briefly... you would crete a query that retrieves the Solution, and that takes a Title as a parameter. In the Click event of the Listbox (or you could use a separate button for this), get the selected Title, and run the query, passin...

Automatically caculate two values that are entered into two different fields and displayed in a Third Field
 Hello, I use the following code in a GridView page to caculate two values that are entered into two different fields and the result is displayed in the TotalHoursPerUnit field.  I am trying to convert the Code Behind The Page to work under the Protected Sub FormView1_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles FormView1.DataBound, but no luck.  Please suggest a way to modify the statements to work with text boxes in a FormView control. Thank you in advance, ASPX Page <script language ="javascript"  type="text/javascript&qu...

Cannot display a graphic from a path stored in a database field in .Net using Crystal Reports XI
I'm using Crystal Reports XI which will display a graphic from a path stored in a database field.  So you can display a different path for each item code for instance.  Trouble is the images are stored in a windows directory on a different server to the code.  It works in crystal alone but when you try otn display the image through .Net and Crystal the image doesn't appear unless it points to my own hard drive.  E.g. c:\image.jpg works but not \\anetworkdrive\image.jpg  We've tried changing the authority and set up a specific .Net account with full access to the...

Two Queries from Two different Databases = ONE Display/Table
What is the best approach to handle this situation?  I have three different databases, which has it's own stored procedure.  I need to call them all at page load and piece together the data.  The common demoninator is the date.  2007 JAN FEB MAR APR row 1 50 60 89 63 row 2 44 21 62 46 2006 JAN FEB MAR APR row 1 60 90 65 41 row2 984 650 452 762 Row 1 and Row 2 come from two different databases and stored procedures.  How can I query the data and present it as it's shown above? Thank you!  &qu...

Comparing objects in two different databases
While we're limited beta release, our data structure and/or stored procedures and functions are updated frequently. Right now, all the developers share an 'update.sql' file to which we all post our ALTER statements. Each time we update the beta sites, we send this file along with a batch file to execute it through ISQL. There has got to be a better way! Often, our developers don't include EVERYTHING necessary to get the beta site up to speed. Therefore, the structure of the beta site and the development site diverge. Does anybody know of a utility that will comp...

calculate number of months between two dates
Hi, I was wondering if you can help. I have two date strings and I wish to be able to calculate the number of months between them. Note the dates are for different years. Many thanks Mark :) I suppose you can cast them to DateTime structures. Then you can do something like the following //d2 and d are your datetime structures //we suppose that each month has 30 daysTimeSpan t = d2 - d; double months = Math.Round(t.TotalDays /30); It's not exact however (since not all months have thirty days) Plese, do not forget to click "Mark as Answer" on the post that helped you....

Displaying related fields with an SQL database and ADO.NET
Okay, so I know how to display a singular table from an SQL databse into a datagrid, but I was never shown how to display related fields into one table. Here is the problem: I need to display student information for an online college solution. The information I need to display is First Name, Last Name, and some information from the course they are currently taking. Now - the student information is in a Student table and the course information is in (you guessed it) the Course table. The student table contains a foreign key, the courseID, which is obviously the key in the course table....

Dynamicaly displaying the images according to the different values for the field in the database
 <asp:TemplateColumn><ItemTemplate><asp:Image id=Image2 runat="server" Visible='<%# DataBinder.Eval(Container, "DataItem.INVENTORY_PO_ATTN").ToString() == "1" %>' ImageUrl="../Images/ingate_mini.gif" AlternateText="ATTN: Operations">           </asp:Image><asp:Image id=Image3 runat="server" Visible='<%# DataBinder.Eval(Container, "DataItem.INVENTORY_PO_ATTN").ToString() == "2" %>' ImageUrl="../Images/phone_mini.gif" AlternateText="ATTN: Sales">        ...

Select three fields in SQL datasource but only display two in datalist
How would I do this?  I have to select the First and last name to show up in the datalist, but I also need to select the key because I am updating another column in the selected row.  How would I display only the first and last name? You can edit the html in the aspx page and remove that item from the templates.BrucePlease remember to click "Mark as Answer" on the posts that helped solve your issue. The problem is the template doesnt know the name yet because the sql select is selecting from a "' + table + '" so that wouldnt work.  Sorry I forgo...

want to display 2 different outputs based on a database field........
Hello Everyone, i have a query in MS Access 2003 based on 2 tables. the query includes all the customer information and their order details. i retrieve these values pass them to a gridview and send them a order confirmation email. works perfect. but what i want to do is send a special thank you note to repeat customers. this note should be sent only to repeat customers not the first time buyers. i have cust_id which keep tracks of the customers, i am hoping if theres a way to use the cust_id to acheive this. basicall this is what i am looking for if its a repeat customer then send a spe...

database blob object to display bitmap stored in Oracle blob??
How do I display a bitmap stored in an Oracle blob data type column? Do I use a database blob object? Can someone explain to me how it works? Ive read the help file but Im missing something? Please help? How do I insert a bitmap in an Oracle blob data type column? Thanks, Stephane Viau Ottawa, Canada To select a blob do a selectblob, all this is in the examples help. Stephane Viau wrote: > How do I display a bitmap stored in an Oracle blob data type column? Do I > use a database blob object? Can someone explain to me how it works? Ive read > the help file b...

Need to display data from two different fields in a single drop down list
Hi everyone,I am working in an application where I will be displaying a drop down list that lists employee names, the problem is that these employee names are stored in two different columns (last_name) and  (first_name) and I need to display the full name in my drop down list. Changes to the database structure is not an option.Thanks,r.Ricardo D. SanchezGDL Technologieswww.gdltec.netwww.ricardodsanchez.comPlease remember to click "Mark as Answer" on this post if it helped you. Take a look @ sampleSushila Bowalekar PatelVisual ASP/ASP.NET MVPhttp://weblogs.asp.net/sushilasb ...

Reconciliating two datasources and need to extract the reconcliation differences from two data sources and keep in one datasource and should generate the report in UI ?
Hi,       I have question as how to generate a report in UI which should show the reconciliation differences between two datasources (extract this reconciliation  data from 1 datasource which is the reconciliation diff  between two data sources ) . How many store procs will be necessary for this and how to write store proc for this? I am new to dotnet ,could anyone ps help me out as to how to start this task ,mainly how to write the store proc and awhat all needed for this task?   Thanks You could do this multiple ways but since you say your New t...

how can display a picture stored in sql sever database field on the same page with a gridview
I have employee pictures stored in the same sql sever database  table with other employee data like employee names and numbers. I want to have a dropdownlist of employee numbers on a page and then i would like to select an employee number from this dropdownlist and have that employee picture dispalyed on the same page. At the moment iam able to display the picture on a totally new page but i would like to have the picture displayed on the same page with employee numbers dropdownlist as well as textboxes to display the names and whatever on the same page. Which sever control shoul...

Web resources about - Display and Calculate a number between two different fields stored in my database - asp.net.object-datasource

‘Time’ Helps Users Calculate How Much Time They Have Spent On Facebook
Have you ever wondered exactly how much time you have spent on Facebook over the years? This calculator from Time may be able to answer that. ...

Calculate Marketing (@calculatemktg) on Twitter
Sign in Sign up To bring you Twitter, we and our partners use cookies on our and other websites. Cookies help personalize Twitter content, tailor ...

Startup and Private Valuations : How do you calculate the valuation in a startup with no need for funds ...
... ratio is appropriate for your business. But there's no standardized way to establish that, and certainly no formula by which you could calculate ...

Run Speeds - Track and log your workouts and calculate time, pace, speed and distance on the App Store ...
Get Run Speeds - Track and log your workouts and calculate time, pace, speed and distance on the App Store. See screenshots and ratings, and ...

calculate - Flickr - Photo Sharing!
Explore jekert gwapo's photos on Flickr. jekert gwapo has uploaded 7967 photos to Flickr.

Corbell calculates the costs of doubt
Corbell calculates the costs of doubt

Salvation Army developed 'matrix' to calculate payouts to abuse victims, commission hears
The Salvation Army took a "largely litigious approach" towards victims of horrific physical and sexual abuse, especially those who sought compensation ...

Nest-egg needs hard to calculate
Retirement saving is often like the federal budget. We target a reasonable number and hope it works out.

Calculate your income tax based on the ATO personal tax scale
JavaScript disabled. Please enable JavaScript to use My News, My Clippings, My Comments and user settings. New feature Personalise your news, ...

YouTube - How to use 12C Financial Calculator to calculate cash flow NPV and IRR
Hochgeladen am 12.12.2010 Vicinno 12C Financial Calculator app best emulates HP 12C Financial Calculator for iPhone, iPad, Mac, and Android, ...

Resources last updated: 11/29/2015 10:54:21 AM