Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 Why am I not getting the error messages to fire?

Author  Topic 

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2013-06-18 : 09:34:07
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-06-18 : 11:42:45
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

327 Posts

Posted - 2013-06-19 : 09:17:30
Thank you very much.

It is working now.
Go to Top of Page
   

- Advertisement -