ASP Page: Mark a record as Active
Dear Experts,
I am working on parking permit web application. A user is directed to viewcar.asp page after login. I would like to have the user mark one of his/her car as an “Active” car.
- Viewcar.asp shows all the cars that associate with this user.
- Each car showed on veiwcar.asp has an Edit link. The Edit link brings the user to
editcar.asp page to edit car table.
- My car table has a filed call, Status.
My Question
This is what I want to do:
When the Status field is changed to “Active”, the Status of all other cars that are associated with this user must set to “Inactive”.
I wonder what is the most straight forward way to accomplish this task?
If I were to use a database Trigger, how would I do so?
I use Macromedia Dreamweaver MX 2004 for ASP pages and MS SQL Server 2000 Database.
Thank you very much!!!
I am working on parking permit web application. A user is directed to viewcar.asp page after login. I would like to have the user mark one of his/her car as an “Active” car.
- Viewcar.asp shows all the cars that associate with this user.
- Each car showed on veiwcar.asp has an Edit link. The Edit link brings the user to
editcar.asp page to edit car table.
- My car table has a filed call, Status.
My Question
This is what I want to do:
When the Status field is changed to “Active”, the Status of all other cars that are associated with this user must set to “Inactive”.
I wonder what is the most straight forward way to accomplish this task?
If I were to use a database Trigger, how would I do so?
I use Macromedia Dreamweaver MX 2004 for ASP pages and MS SQL Server 2000 Database.
Thank you very much!!!
ASKER
Hi Rouchie,
Thank you for your great idea. I managed to create a store procedure with both commands. However, how do I check for any errors before committing the transaction, then Rollback if something goes wrong?
The following is my codes for the stored procedure.
Thank you very much~~!!!
=======================
CREATE PROCEDURE setActive
@UserID varchar(200),
@CarID int
AS
UPDATE Car
SET Car.Status = 0 FROM Car, User
WHERE User.UserName =@UserID And User.UserID = Car.UserID;
UPDATE Car
SET Car.Status = 1 FROM Car, User
WHERE User.UserName =@UserID And Car.carID =@CarID And User.UserID = Car.CarID
GO
==========================
Thank you for your great idea. I managed to create a store procedure with both commands. However, how do I check for any errors before committing the transaction, then Rollback if something goes wrong?
The following is my codes for the stored procedure.
Thank you very much~~!!!
=======================
CREATE PROCEDURE setActive
@UserID varchar(200),
@CarID int
AS
UPDATE Car
SET Car.Status = 0 FROM Car, User
WHERE User.UserName =@UserID And User.UserID = Car.UserID;
UPDATE Car
SET Car.Status = 1 FROM Car, User
WHERE User.UserName =@UserID And Car.carID =@CarID And User.UserID = Car.CarID
GO
==========================
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Rouchie~
Thank you for the help~~~!!! & Sorry for the delay!
=)
Thank you for the help~~~!!! & Sorry for the delay!
=)
No problem :-)
1st part sets all of the person's cars to be inactive --> update myTable set active = 0 where personID = ???
2nd part sets the chosen car to be active -- > update myTable set active = 1 where carID = ???
You could put both commands in a stored procedure and check for any errors before committing the transaction. Rollback would undo all the updated data if anything went wrong.