Solved

ASP Page: Mark a record as Active

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DreamWeaver publishing with two users 1 231
Adobe Web Premium CS3 5 374
Saving Form Data when Leaving Page 3 356
Need graphics tool to create card game 2 119
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

920 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

15 Experts available now in Live!

Get 1:1 Help Now