Solved

ASP Page: Mark a record as Active

Posted on 2006-07-03
5
196 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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 …
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now