SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 Why am I not getting the error messages to fire?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

simflex
Constraint Violating Yak Guru

315 Posts

Posted - 06/18/2013 :  09:34:07  Show Profile  Reply with Quote
Hi experts,

In the stored below, I am trying to accomplish 3 things.

1, if a user attempts to sign up, check to see if the user has already signed up.

If yes, fire up a message that 'This user has already registered'

If not, then register the user with insert statement.

2, If registration is successful, fire up a message that says, "Registered successfully"

3, If registration is full, put users in the waitingList with INSERT into waitingList.
If registration into waitingList is successully, give a message, "You have been placed on waiting list"

None of this is working.

Your great expert assistance is greatly appreciated.

USE [POLLWORKER_TRAINING]
GO
/****** Object:  StoredProcedure [dbo].[sp_Register]    Script Date: 06/17/2013 16:00:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Register]
	@cosID int,
	@locid int,
	@dat int,
	@UserName varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
declare @error varchar(max)
  SET NOCOUNT ON;

		if exists(SELECT * FROM tblTrainings WHERE Username = @UserName AND CourseID = @cosID  AND LocationID = @locid AND dateId = @dat)
begin
set @error = 'You have already signed up for this training'
raiserror(@ERROR, 12, 1)
end
		else
  DECLARE @seatsAvailable int
   SET @seatsAvailable = 0;
  SELECT @seatsAvailable =
       (select Seating_Capacity - (select count(*) from tblTrainings WHERE CourseId=@cosID )
        FROM tblLocations
        WHERE LocationId = @locID )
     
  if @seatsAvailable > 0
begin
    INSERT INTO tblTrainings (CourseId, LocationId, dateId, username) VALUES (@CosID, @LocID, @dat, @Username)
set @error = Registration is successful'
raiserror(@ERROR, 13, 1)

end
 else
begin
    INSERT INTO tblWaitingList (CourseId, LocationId, dateId, username) VALUES (@CosID, @LocID, @dat, @Username)
set @error = 'You have been placed on the waiting list. You will be immediately notified if a seat is available'
raiserror(@ERROR, 14, 1)
end
END

Thanks a lot in advance

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 06/18/2013 :  11:42:45  Show Profile  Reply with Quote
I see a couple things with the code you posted:

              set @error = 'You have already signed up for this training'
              raiserror(@ERROR, 12, 1)
       end
       else
missing a BEGIN here
       DECLARE @seatsAvailable int


Also you are missing a beginning quote here:
set @error = Registration is successful'


Fix those things and then try to run call this procedure in a query window to make sure it is working as expected under the various scenarios. If it is the you'll need to look closer at your .net code error handling.

Be One with the Optimizer
TG
Go to Top of Page

simflex
Constraint Violating Yak Guru

315 Posts

Posted - 06/19/2013 :  09:17:30  Show Profile  Reply with Quote
Thank you very much.

It is working now.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000