Hi,
SQL primary key IDENTITY problem
I have created a MS-SQL table as follows;
CREATE TABLE [dbo].[UserRoles](
[UserRolelD] [int] IDENTITY(1,1) NOT NULL,
[Date_Time] [datetime] NOT NULL DEFAULT (getdate()),
[UserID] [int] NOT NULL DEFAULT ((1)),
[RegDate] [datetime] NOT NULL DEFAULT (getdate()),
[Active] [bit] NOT NULL DEFAULT ((1)),
[RoleName] [nvarchar](100) NOT NULL,
[RoleDescription] [nvarchar](1000) NULL,
[ModulerID] [int] NOT NULL,
CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
(
[UserRolelD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
PROCEDURE for date insert
CREATE PROCEDURE [dbo].[UserRoles_Insert]
@LoginName nvarchar(20) = 'sampath',
@RoleName nvarchar(100) = 'xxx',
@RoleDescription nvarchar(1000) = 'xxx',
@ModulerID int = 5
AS
BEGIN
SET NOCOUNT ON;
DECLARE @UserID int
SET @UserID = (SELECT UserID FROM [SATHUTA-ERP].[dbo].[ErpUsers] WHERE LoginName = @LoginName)
DECLARE @RoleDescription1 nvarchar(1000)
SET @RoleDescription1 = (SELECT CASE WHEN LTRIM(RTRIM(@RoleDescription)) = '' THEN NULL ELSE LTRIM(RTRIM(@RoleDescription)) END)
IF NOT EXISTS(SELECT UserID FROM [SATHUTA-ERP].[dbo].[ErpUsers] WHERE RoleId = 1 AND ActiveUser = 1 AND UserID = @UserID)
BEGIN
SELECT -1
END
ELSE
IF EXISTS(SELECT UserRolelD FROM UserRoles WHERE ModulerID = @ModulerID and RoleName = LTRIM(RTRIM(@RoleName)))
BEGIN
SELECT -2
END
ELSE
BEGIN
INSERT INTO UserRoles (
RoleName,
RoleDescription,
ModulerID,
Date_Time,
UserID,
RegDate,
Active)
VALUES ( LTRIM(RTRIM(@RoleName)),
@RoleDescription1,
@ModulerID,
GETDATE(),
@UserID,
GETDATE(),
1)
END
END
GO
After
My problem was;
[UserRolelD] [int] IDENTITY(1,1) is not 1,2,3,4,5,….n
Thanking you
samsmuthu