Invalid UserGroup error in SQL server

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

General SQL Server Forums

New to SQL Server Programming

Invalid UserGroup error in SQL server

Author

Topic

shaker
Starting Member

3 Posts

Posted - 2011-08-06 : 03:55:28

Hi all,

when iam running my app i am getting sqlserver exception error which is "Invalid UserGroup".Please help me..below is my SP.

USE [AZFuelDistributors]
GO
/****** Object: StoredProcedure [dbo].[CreateUser] Script Date: 08/06/2011 13:09:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[CreateUser]
@loginName varchar(20),
@firstName varchar(50) = null,
@lastName varchar(50) = null,
@password varchar(255) = null,
@userGroup varchar(50) = null,
@address1 varchar(50) = null,
@address2 varchar(50) = null,
@city varchar(50) = null,
@state char(2) = null,
@country varchar(50) = null,
@zipCode varchar(10) = null
as
begin

/*=============================================================================
* Constants
*============================================================================*/
declare
@ERROR_SEVERITY smallint,
@ERROR_STATE1 smallint,
@theErrorMsg nvarchar(4000),
@theErrorState int,
@userGroupId int

select
@ERROR_SEVERITY = 11,
@ERROR_STATE1 = 1,
@userGroupId = -1

begin try

if (@userGroup is not null)
begin
select @userGroupId = UserGroupId
from UserGroup
where Name = @userGroup
end

if (@userGroupId = -1)
raiserror(N'Invalid User Group (%s)', @ERROR_SEVERITY, @ERROR_STATE1, @userGroup)

insert into AppUser
(LoginName, Password, FirstName, LastName, Address1, Address2, City, State, Country, Zip, UserGroupId, InsertDate, LastPasswordChanged)
values
(@loginName, @password, @firstName, @lastName, @address1, @address2, @city, @state, @country, @zipCode, @userGroupId, getdate(), getdate())
end try

begin catch
set @theErrorMsg = error_message()
set @theErrorState = error_state()
raiserror (@theErrorMsg, @ERROR_SEVERITY, @theErrorState)

end catch
end
select * from AppUser

Nmooody

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-06 : 07:41:06

This is because the variable @userGroupId is -1 (see (1) below that I have highlighted in red). And that is -1 because of the select statement just before that.(see (2) that I have highlighted in red)

So in your UserGroup table, for the @userGroup that you are passing in as parameter, the UserGroupId is -1. From the way the stored proc is written, that is an invalid condition.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CreateUser]
	@loginName VARCHAR(20),
	@firstName VARCHAR(50) = NULL,
	@lastName VARCHAR(50) = NULL,
	@password VARCHAR(255) = NULL,
	@userGroup VARCHAR(50) = NULL,
	@address1 VARCHAR(50) = NULL,
	@address2 VARCHAR(50) = NULL,
	@city VARCHAR(50) = NULL,
	@state CHAR(2) = NULL,
	@country VARCHAR(50) = NULL,
	@zipCode VARCHAR(10) = NULL
AS
BEGIN
	/*=============================================================================
	*	Constants
	*============================================================================*/
	DECLARE @ERROR_SEVERITY  SMALLINT,
	        @ERROR_STATE1    SMALLINT,
	        @theErrorMsg     NVARCHAR(4000),
	        @theErrorState   INT,
	        @userGroupId     INT
	
	SELECT @ERROR_SEVERITY = 11,
	       @ERROR_STATE1 = 1,
	       @userGroupId = -1
	
	BEGIN TRY
		IF (@userGroup IS NOT NULL)
		BEGIN
		    SELECT @userGroupId = UserGroupId --(2)
		    FROM   UserGroup
		    WHERE  NAME = @userGroup
		END
		
		IF (@userGroupId = -1) --(1)
		    RAISERROR(
		        N'Invalid User Group (%s)',
		        @ERROR_SEVERITY,
		        @ERROR_STATE1,
		        @userGroup
		    )
		
		INSERT INTO AppUser
		  (
		    LoginName,
		    PASSWORD,
		    FirstName,
		    LastName,
		    Address1,
		    Address2,
		    City,
		    STATE,
		    Country,
		    Zip,
		    UserGroupId,
		    InsertDate,
		    LastPasswordChanged
		  )
		VALUES
		  (
		    @loginName,
		    @password,
		    @firstName,
		    @lastName,
		    @address1,
		    @address2,
		    @city,
		    @state,
		    @country,
		    @zipCode,
		    @userGroupId,
		    GETDATE(),
		    GETDATE()
		  )
	END TRY
	
	BEGIN CATCH
		SET @theErrorMsg = ERROR_MESSAGE()
		SET @theErrorState = ERROR_STATE()
		RAISERROR (@theErrorMsg, @ERROR_SEVERITY, @theErrorState)
	END CATCH
END 
SELECT *
FROM   AppUser

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources