[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

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!!!
0
cuconsortium
Asked:
cuconsortium
  • 3
  • 2
1 Solution
 
RouchieCommented:
To be honest I'd break it down into 2 parts.
  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.
0
 
cuconsortiumAuthor Commented:
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

================================
0
 
RouchieCommented:
Morning cuconsortium,

Here's your procedure with the error checking added on:
__________________________________________________________________

CREATE PROCEDURE setActive

@UserID varchar(200),
@CarID int

AS

BEGIN TRAN

      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

      IF @@ERROR <> 0
            BEGIN
                  ROLLBACK TRAN
                  RAISERROR('An error occurred while updating the CAR database, the transactions have been rolled back.', 10, 1)            
      
            END
      ELSE
            BEGIN
                  COMMIT TRAN
            END

GO
0
 
cuconsortiumAuthor Commented:
Hi Rouchie~

  Thank you for the help~~~!!! & Sorry for the delay!

=)
0
 
RouchieCommented:
No problem :-)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now