How to update a field

[vanisudha2000]

Hello,
I have written a stored procedure where I have set the status = open, this stored procedure runs on click of the insert button , it inserts the record taking all the fields entered from the detailsview.

in the data base i have currentdate that is the systemdate and datewhich is 2 hours later than the currentdate.

I have to change the status in the database from Open to Close if the current time is greater than the timestamp when 2 hours were added to the sysdate earlier.

But how can I make that check as this insert stored procedure is only executed when the insert button is clicked. I dont know where, how and what to do to make that time check and change the status to Close depending on the time check.

I am calling this stored procedure using
InsertCommand = "Insertproc" InsertCommandtype = "storedprocedure"
in ASP.net2.0

 

[tperri]

Do your time checking inside of your stored proc. That would be the easiest solution for this, IMO.

 

[vanisudha2000]

CREATE PROCEDURE [dbo].[CalculateIssueExpiryTime]
-- Add the parameters for the stored procedure here
@alias nchar(10),
@title ntext,
@description ntext,
@completetime int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

Declare @expireat datetime,
@createdat datetime,
@status nchar(10),
Set @createdat = GetDate();
Set @expireat = DateAdd(minute, @completetime * 60, GetDate())
Set @status = 'Open'



Insert into Issues values
(@alias,@title,@description,@expireat,@createdat,@status,@completetime)

END

This is the stored proc which is called on insert button. But when the @createdat is greater than @expireat the @status should be changed to 'CLOSED',

Now where should I write the code ????
Help me please..

Thanks in advance

 

[tperri]

Replace your declared variables with this (I just changed the set @status line)

Declare @expireat datetime,
@createdat datetime,
@status nchar(10),
Set @createdat = GetDate();
Set @expireat = DateAdd(minute, @completetime * 60, GetDate())
if (@createdat > @expireat)
begin
Set @status = 'Open'
end
else
begin
Set @status = 'Closed'
end

 

[vanisudha2000]

Thanks for the reply, I got your point but @status would be changed only if the storedprocedure will be called.

I want the @status to be changed automatically once the @createat > @expireat .

I have a datagrid to display all the records with status = open only. Can I write a stored proc on display of the data in the datagrid ???

 

[tperri]

Well, that isn't what I understood from your first post, sorry for the misunderstanding.

I'm not SQL guru, but you have to be able to write DTS or something that will run constantly and compare your createat and expireat columns and set the status = closed when your criteria is met. I think this is a more suitable question for the SQL group here on tek-tips (unless one of those people reads this too and can answer )

HTH

 

[jbenson001]

I agree with tperri, you would have to have a job running on the sql server which would have to run at certain invervals, say every minute to check then update the table as necessary. This could become resorce intensive.

Jim

 

[vanisudha2000]

I have created a stored procedure to update the status fied like this
Declare
@expireat datetime,
@createat datetime,
@status varchar(10)

Select expireat
From Issues
Where expireat < getdate()


Update Issues Set status = 'Closed'
Where expireat < getdate()

I want to call this stored procedure on the Page_load and bind it to the datagrid, and show only those records whose status = "Open". And refresh the page every 60 seconds,
But I dont know how to write code for this, Can you please help me in writing code for my requirement,

Thanks in advance

 

[jbenson001]

http://www.knowdotnet.com/articles/asppagerefresh.html

 

[vanisudha2000]

Thanks for your reply, Can you please help me with the code , how to call the stored procedure on the page_load and bind the data to the datagrid with the selected records.

Can you please help me with code, thanks in advance.