Identity_Insert Error - SQL Server Forums

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

Identity_Insert Error

Author

Topic

bhushan1980
Starting Member

7 Posts

Posted - 2008-04-21 : 19:42:27

Hi all. This is my first post to these forums and I am a newbie. I am using SQL server 2005 and I have written a procedure to insert the data in new row. My procedure runs and executes well, but does not insert a record in my DB table. Instead I get a message in the debugger window that I need to turn ON the Identity Insert property for the tables. But after searching for a while in the IDE, I could not find any such property. So I need to know what went wrong here. Also, I am using ASP.NET to connect to the database and I found out an article on web explaining the way to set the property to ON through my ASP.NET application. But, I would also like to know how to set that property to ON through SQL management studio. Also, my primary key is not null; I am also passing the primary key value and it is unique. Please let me know a remedy for this.

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-21 : 19:47:51

You've got a coding problem. You should not be supplying a value for the identity column, but rather let SQL Server manage it for you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

X002548
Not Just a Number

15586 Posts

Posted - 2008-04-21 : 20:09:36

Tara, Tara, Tara

Step 1: Turn computer on

Step 2:....just kidding

We need basics

IF you have a table with an IDENTITY Column, it will supply the next value...I don't think you want to mess around with SET IDENTITY_INSERT <tablename> ON at this point

Let's start here: What's your background?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

bhushan1980
Starting Member

7 Posts

Posted - 2008-04-21 : 21:13:01

Hi tkizer/X002548
I am a software developer with a considerable understanding of C++/C#/VB. My exposure to databases has not been good, when it comes to substantial development. I have worked with ODBC, DAO, ADO and OLE DB to interface with Visual C++ and and VB6(DAO) to an extent .NET (VB.NET and C#). So all I know is a little bit of SQL queries and stored procedures as those are all we need to use. Also, I have to tell you that I would be eager to know a few more things as I mature with databases. So as tkizer said, is my identity column, which happens to be my primary key in this case, giving me a problem?

Here is the code for the stored procedure...


ALTER PROCEDURE dbo.insertUpdateRegisteredUser_sproc 
	/*
	(
	@parameter1 int = 5,
	@parameter2 datatype OUTPUT
	)
	*/
	@ssnID					int,
	@fName					varchar,
	@mName					varchar,
	@lName					varchar,
	@userID					varchar,
	@password				varchar,
	@roleID					tinyint,
	@sa1					varchar,
	@sa2					varchar,
	@sa3					varchar,
	@mothersMaiden			varchar,
	@dob					datetime,
	@priAddress				varchar,
	@secAddress				varchar,
	@priPhone				int,
	@secPhone				int,
	@email					varchar
AS
	DECLARE @returnValue	int
	
	IF(@ssnID IS NOT NULL)
		INSERT INTO
			CustomerLoginInfo
			(
				SSN_ID,
				FirstName,
				MiddleName,
				LastName,
				UserID,
				Password,
				RoleID,
				SecretA1,
				SecretA2,
				SecretA3,
				Mother_Maiden,
				DateOfBirth,
				PrimaryAddress,
				SecondaryAddress,
				PrimaryPhone,
				SecondaryPhone,
				Email
			)
			VALUES
			(
				@ssnID,
				@fName,
				@mName,
				@lName,
				@userID,
				@password,
				@roleID,
				@sa1,
				@sa2,
				@sa3,
				@mothersMaiden,
				@dob,
				@priAddress,
				@secAddress,
				@priPhone,
				@secPhone,
				@email
			)
		SET @returnValue = @ssnID	

	RETURN @returnValue

And the error I am getting...


Cannot insert explicit value for identity column in table 'CustomerLoginInfo' when IDENTITY_INSERT is set to OFF.

I know the solution is something very simple, but I can't figure it out. Please help with the issue.

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-22 : 00:57:26

Which column is your identity column? You need to exclude it from your INSERT statement so that SQL Server manages it. If you don't want SQL Server to manage it, then you need to remove the identity open.

Here is an example to show SQL Server managing it, make sure you understand what's going on here:

CREATE TABLE Table1 (Colunm1 int IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Column2 varchar(5))

INSERT INTO Table1 VALUES('Tara')
INSERT INTO Table1 VALUES('Kizer')

SELECT * FROM Table1

DROP TABLE Table1

Notice how I've got an identity column defined in my table, Column1. I also set it to be the primary key. I then don't reference it in my insert, yet I get values for it anyway. Run the code as is and see how it works.

Let us know if you have any questions.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

bhushan1980
Starting Member

7 Posts

Posted - 2008-04-22 : 12:13:00

Hi Tkizer,
The problem is, I am not able to find the code view for the table. How am I suppose to find it? As I told you, I am absolutely new. I would appreciate if you could please tell me if I can edit the code and modify the table.

Shall I go ahead and write an alter procedure query? Or shall I create a new table altogether?

Here is the table definition code that I was able to view using SELECT in the new query view:


SELECT [SSN_ID]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[User_ID]
      ,[Password]
      ,[SecretA1]
      ,[SecretA2]
      ,[SecretA3]
      ,[Mother_Maiden]
      ,[DateOfBirth]
      ,[PrimaryAddress]
      ,[SecondaryAddress]
      ,[PrimaryPhone]
      ,[SecondaryPhone]
      ,[Email]
  FROM [CreditCardsInfo].[dbo].[CustomerLoginInfo]

Here is the INSERT INTO code:


INSERT INTO [CreditCardsInfo].[dbo].[CustomerLoginInfo]
           ([SSN_ID]
           ,[FirstName]
           ,[MiddleName]
           ,[LastName]
           ,[User_ID]
           ,[Password]
           ,[SecretA1]
           ,[SecretA2]
           ,[SecretA3]
           ,[Mother_Maiden]
           ,[DateOfBirth]
           ,[PrimaryAddress]
           ,[SecondaryAddress]
           ,[PrimaryPhone]
           ,[SecondaryPhone]
           ,[Email])
     VALUES
           (<SSN_ID, int,>
           ,<FirstName, varchar(50),>
           ,<MiddleName, varchar(50),>
           ,<LastName, varchar(50),>
           ,<User_ID, varchar(50),>
           ,<Password, varchar(50),>
           ,<SecretA1, varchar(150),>
           ,<SecretA2, varchar(150),>
           ,<SecretA3, varchar(150),>
           ,<Mother_Maiden, varchar(50),>
           ,<DateOfBirth, datetime,>
           ,<PrimaryAddress, varchar(250),>
           ,<SecondaryAddress, varchar(250),>
           ,<PrimaryPhone, varchar(50),>
           ,<SecondaryPhone, varchar(50),>
           ,<Email, varchar(50),>)

Everything seems fine to me. However, as you mentioned in your previous code I might have to change some table code.

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-22 : 12:39:55

In Management Studio, navigate to your table, right click, select Script Table as, select CREATE to, select Clipboard. Paste it here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-22 : 12:40:54

Well given you code it appears that SSN_ID is your IDENTITY column. But, it is rather disturbing that you do not know anything about your schema. Does the SSN_ID get created in another table based on the Users actual SSN? If so then SSN_ID should not be an IDENTITY. However, if the column name is just misleading and you actually want to do an insert and pass that SSN_ID back, then you would need to do something like this:

	IF(@ssnID IS NULL)
		INSERT INTO
			CustomerLoginInfo
			(
				FirstName,
				MiddleName,
				LastName,
				UserID,
				Password,
				RoleID,
				SecretA1,
				SecretA2,
				SecretA3,
				Mother_Maiden,
				DateOfBirth,
				PrimaryAddress,
				SecondaryAddress,
				PrimaryPhone,
				SecondaryPhone,
				Email
			)
			VALUES
			(
				@fName,
				@mName,
				@lName,
				@userID,
				@password,
				@roleID,
				@sa1,
				@sa2,
				@sa3,
				@mothersMaiden,
				@dob,
				@priAddress,
				@secAddress,
				@priPhone,
				@secPhone,
				@email
			)
		SET @returnValue = @SCOPE_IDENTITY()	

	RETURN @returnValue

But, you might want to take a few moments to understand your schema as I think that would help a lot!

bhushan1980
Starting Member

7 Posts

Posted - 2008-04-22 : 15:31:20

Hi Tkizer, Here is the code:


USE [CreditCardsInfo]
GO
/****** Object:  Table [dbo].[CustomerLoginInfo]    Script Date: 04/22/2008 13:42:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CustomerLoginInfo](
	[SSN_ID] [int] NOT NULL,
	[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[MiddleName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[UserID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Password] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SecretA1] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SecretA2] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SecretA3] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Mother_Maiden] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[DateOfBirth] [datetime] NOT NULL,
	[PrimaryAddress] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SecondaryAddress] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[PrimaryPhone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SecondaryPhone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_CustomerLoginInfo] PRIMARY KEY CLUSTERED 
(
	[SSN_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is the primary key of the table that uniquely determines the customer. An individual can have only one SSN!' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CustomerLoginInfo', @level2type=N'COLUMN',@level2name=N'SSN_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The ID used by an individual to login' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CustomerLoginInfo', @level2type=N'COLUMN',@level2name=N'UserID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The password used by the user to login' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CustomerLoginInfo', @level2type=N'COLUMN',@level2name=N'Password'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Answer to the first secret question to verify a registered user''s login credentials' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CustomerLoginInfo', @level2type=N'COLUMN',@level2name=N'SecretA1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Answer to the second secret question to verify a registered user''s login credentials' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CustomerLoginInfo', @level2type=N'COLUMN',@level2name=N'SecretA2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Answer to the third secret question to verify a registered user''s login credentials' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CustomerLoginInfo', @level2type=N'COLUMN',@level2name=N'SecretA3'

Now, I would like to make things more clear to Lamprey and ease my questioning and your answering. Let me ask a few questions before hand:
<1> Can I enter a value into a Primary Key Field? In my application, it is not auto-generated.
<2> What exactly does identity property of a field do? Simply in lay-man's terms, why do u set a identity property to true?
<3> How do the relationships that have been assigned affect my results in the table where a column is assigned a primary key? In other words, even if I have assigned SSNID field a primary key, what difference does it make if I am using it in any other table as a foreign key?

I hope that I am more clear this time...

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-22 : 15:42:53

Your table is not using an identity column, so now I'm thoroughly confused as to why you are getting that error. Are there any triggers on this table?

Have you tried adding a record to the table via the stored procedure from Management Studio rather than from your application? This will help determine where the problem is.

1. Yes you can.
2. Various reasons. One reason though is when you don't have a natural key and instead need a surrogate key.
3. You must insert the primary key data before the foreign key data, otherwise you will get an error. On deletes, it happens in the other direction.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

bhushan1980
Starting Member

7 Posts

Posted - 2008-04-22 : 16:09:49

No Tkizer. I don't have any trigger as of yet. Yes, I am trying to do it through a stored procedure. Is it that I have to use some other application to call that stored procedure? I am just executing it through the Visual Studio IDE. You can open the tables in the Visual Studio IDE itself, so I am doing it. I can execute the stored procedure and it runs fine but for the fact that it does not insert the row in the table. Is the part [SSN_ID] ASC from the table code creating a problem for me? Obviously, I do not need the entries sorted in the ascending order according to the ascending order of the primary key. So please let me know if I need to change anything in the code.

Thank you.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-22 : 16:37:52

You said the error was: Cannot insert explicit value for identity column in table 'CustomerLoginInfo' when IDENTITY_INSERT is set to OFF.

However, the DDL you posted does not have an IDENTITY defined on that table. So, we are all confusued as to how that error is being generated or where it is coming from. Just as an example the DDL would like like this if it had an IDENTITY defined on it:

CREATE TABLE [dbo].[CustomerLoginInfo](
	[SSN_ID] [int] NOT NULL IDENTITY(1, 1),
	[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[MiddleName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[UserID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Password] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SecretA1] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SecretA2] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SecretA3] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Mother_Maiden] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[DateOfBirth] [datetime] NOT NULL,
	[PrimaryAddress] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SecondaryAddress] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[PrimaryPhone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[SecondaryPhone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_CustomerLoginInfo] PRIMARY KEY CLUSTERED 
(
	[SSN_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Is there anyone else there (I assume at work) that can take a look at this for you? The information you have provoed thus far does not seem to jive with the issue you are having.

I'm still curious about the SSN. Do you have a table of SSNs that generates an ID or are you just storing the 9 digit SSN as an integer?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-22 : 16:47:10

The ASC part in your DDL is not creating the problem. For a clustered index, the data has to be stored in DESC or ASC order. ASC is the default.

Please try executing the stored procedure in Management Studio and not in VS IDE. It is very important that we debug this in small steps since you are so new.

Here's an example of how to execute it in SSMS:
EXEC StoredProcName @var1 = 1, @var2 = 'Tara', @var3 = 'Kizer'

Let us know what happens when you do this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

bhushan1980
Starting Member

7 Posts

Posted - 2008-04-22 : 17:09:03

Hi Tkizer. This might sound a bit crazy. But tell u what. The this time I just opened the table and I saw all the records that I had previously entered. I do not understand why and how it happened, but I am happy that it is working. The only thing is, they are inserted anywhere in the table as the table is sorted in ascending manner by default. But I am having another stupid problem here. I just changed a few datatypes. It gave me a message mentioning that the data might be lost during the transition and since I was ok with it, let it happen and it went through. But, there are some rows I want to delete that I am not able to. It is giving me a message a repeatedly saying that:

"
The row values updated and deleted either donot make row unique or they alter multiple rows (2 rows)."
"
Correct the errors and attempt to delete the row again or press escape to cancel the changes."

I just don't want that data and it is strange that deleting the data is so difficult or rather cryptic. The main problem, I guess, is the primary key column has a same entry for each of the rows i.e., 1. It happened when I was trying different things with the stored procedure. So now please tell me how to get rid of those two rows?

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-22 : 17:11:18

Post the code that you tried to use to delete the data.

You need to provide an ORDER BY to your queries if you want the data returned in a particular order. The clustered index only stores the data in a particular data. It does not sort the data for you when you display it. Only an ORDER BY can do that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

bhushan1980
Starting Member

7 Posts

Posted - 2008-04-22 : 18:00:13

Hi. I am not using any code to delete it. I am just selecting a row and then right-click on it and then selecting delete. I know that people usually confident go on to write the code to do these things. But, I have not achieved that kind of proficiency as of yet. Also, I did not know what a clear command does and on executing it, all the data was cleared. Hence, what I did is, I deleted the entire table. I am creating a new table now. Now I know where I went wrong. But, setting it aside, I would like you to let me know the code to insert or update records into the table. Here is a bit of code I have. Please correct it, if wrong. Also, please let me know if anything is wrong here, keeping in mind that in either case (insert or update) I am entering the primary key, that is ssnID in this case.


/*
-- All input parameters defined...
*/

AS

DECLARE @returnValue int

IF (@id IS NULL) -- Insert new row. Please suggest me a condition in my case as I am not passing a null value.
BEGIN
  INSERT INTO
    TableName
    ( columns....    )
    VALUES
    ( values.....    )
    SET @returnValue = SCOPE_IDENTITY()   -- Please tell me if this is required in my case.
END
ELSE
BEGIN
  UPDATE 
    TableName
  SET
     Column = value
  WHERE
    Id = @id
  SET @returnValue = @id
END

RETURN @returnValue

Please do tell me if I am missing anything.

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources