My Update Query basically adds the numerical values in two columnstogether when the page is loaded. This means whenever the page isRefreshed the Update query is fired.
This is my Update Query (which is in an Stored Procedure):
UPDATE Rental
SET TotalFee = ExtraFee + TotalFee
WHERE DaysOverdue >= 0
I have declared my query in the 'Page_Load' part of the coding, becauseI want the query to run automatically. Not manually by a button.
My main question is that how can I get the query to run only once a day, no matter how many times the page is loaded.
While I wouldn't recommend putting an update query in your page_Load,you can test for a postback before running the query by querying thePage.IsPostBack property: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemWebUIPageClassIsPostBackTopic.asp
|||The query will run whenever your page runs but you can force SQL Server to keep the compiled version of the query in the procedure cache by using the auto start option with sp_procoption system stored proc in the Master database. Run a search for sp_procotion in SQL Server BOL (books online). Hope this helps.|||
I would not have the query run by any ASP.NET page. What happens if no one visits the page in question some day? What happens when the site restarts?
Use DTS on the SQL Server and schedule a job to run once a day.
|||I think Doug is offering the best advice. It looks like you arecalculating late fees so you'd definitely want to run it every day.
If it doesn't matter whether or not it runs every day, and you simplywant it to run no more than once a day, I would add a datetime columnflagging the date on which it was last run. Then you could changeyour query like this:
DECLARE @.Today datetime
SELECT @.Today = GETDATE()
UPDATE Rental
SET TotalFee = ExtraFee + TotalFee,
DateFlagged = @.Today
WHERE DaysOverdue >= 0 AND
DateFlagged < CONVERT(char(8),@.Today,112)
Another option is to calculate the TotalFee on the fly,something like this, rather than updating it every day. Thiswould be safer and not dependent upon a process running every day (andI am guessing that DaysOverDue is also being populated by a process...):
SELECT TotalFee = TotalFee + (ExtraFee * DaysOverDue)
FROM Rental
|||Thanks for all your input.
But I think the best solution to my problem would be using this query:
DECLARE @.Today datetime
SELECT @.Today = GETDATE()
UPDATE Rental
SET TotalFee = ExtraFee + TotalFee,
DateFlagged = @.Today
WHERE DaysOverdue >= 0 AND
DateFlagged < CONVERT(char(8),@.Today,112)
But for some reason I cannot seem to get it to work. I have made anextra column in my table called 'DateFlagged'. I have tried to make itwork by creating this column data type as Date and char. But either wayit is not calculating the Total Fee.
I would appreciate any more info to make this query work.
|||DateFlagged should definitely be a datetime data type. Thiscolumn will initially contain a NULL if you do not set a default valuefor it, so any comparison against a value will return aFALSE. Therefore you'll need to check it for a NULL valueas well.
See if this helps:
WHERE DaysOverdue >= 0 AND
(DateFlagged < CONVERT(char(8),@.Today,112) OR DateFlagged IS NULL)
|||Yep! You solved my problem.
Thanks alot for all your help!
