Solved

ASP Page: Mark a record as Active

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
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 …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

815 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

8 Experts available now in Live!

Get 1:1 Help Now