Solved

ASP Page: Mark a record as Active

Posted on 2006-07-03
5
225 Views
Last Modified: 2010-04-25
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
Comment
Question by:cuconsortium
  • 3
  • 2
5 Comments
 
LVL 25

Expert Comment

by:Rouchie
ID: 17032472
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
 

Author Comment

by:cuconsortium
ID: 17044599
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
 
LVL 25

Accepted Solution

by:
Rouchie earned 500 total points
ID: 17048444
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
 

Author Comment

by:cuconsortium
ID: 17235319
Hi Rouchie~

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

=)
0
 
LVL 25

Expert Comment

by:Rouchie
ID: 17237058
No problem :-)
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question