Zero being inserted in identity column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Zero being inserted in identity column

Zero being inserted in identity column

Rate Topic

Display Mode

Topic Options

Author

Message

ramadesai108

Posted Monday, March 08, 2010 10:32 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 1:38 PM
Points: 370, Visits: 670

Hi,
I have an identity column where the identity seed is 1 and identity increment is 1. When i insert a record from a web application, it enters zero in the identity column. This is a brand new table which i created using the following script. I have also used the exact same script in the past without any problems. Any ideas?

Table Definition

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[customers](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [nvarchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ContactPhone] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Email] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Street] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StateID] [int] NULL,
[Created] [datetime] NOT NULL CONSTRAINT [DF_clients_dteCreate] DEFAULT (getdate()),
CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Stored Procedure to insert

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_insCustomers]
-- Add the parameters for the stored procedure here
(@Name nvarchar(50)
,@Description nvarchar(300)
,@ContactPhone nvarchar(12)
,@Email nvarchar(75)
,@Street nvarchar(75)
,@City nvarchar(50)
,@Zip nvarchar(12)
,@StateID int)

AS
BEGIN

SET NOCOUNT ON;

INSERT INTO [dbo].[customers]
([Name]
,[Description]
,[ContactPhone]
,[Email]
,[Street]
,[City]
,[Zip]
,[StateID])
VALUES
(@Name
,@Description
,@ContactPhone
,@Email
,@Street
,@City
,@Zip
,@StateID)

select @@Identity

END

Here is another user who is complaining the same thing:

http://www.generation-nt.com/us/instead-insert-inserted-identity-column-0-help-116980361.html

Thanks.

Thanks.

Post #878768

GilaMonster

Posted Monday, March 08, 2010 10:48 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 37,679, Visits: 29,935

Please don't start a new thread when there's still an active discussion in the previous one you started.

No replies to this thread please. Direct replies to the existing active thread at http://www.sqlservercentral.com/Forums/Topic876907-146-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #878783

ramadesai108

Posted Monday, March 08, 2010 11:20 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 1:38 PM
Points: 370, Visits: 670

I Apologize. The reason why i started this thread is because the previous thread is under Administration. I have not received any solution from there so i thought a T-SQL developer may know rather than an administrator.

Post #878811

GilaMonster

Posted Monday, March 08, 2010 12:16 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 37,679, Visits: 29,935

There are 14 total replies to the other thread, most by people who know a lot (and I mean a lot) about T-SQL development. If there's no solution yet it's because the problem is difficult, not because they are not developers.

Many of the regulars read all the forums. Cross posting is frowned on in most internet forums, it just wastes people's time as they answer stuff that's been answered elsewhere.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #878844

SQLRNNR

Posted Monday, March 08, 2010 12:23 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 18,733, Visits: 12,332

ramadesai108 (3/8/2010)

Please go back to the other thread and respond to the latest queries I posted.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #878849

« Prev Topic | Next Topic »

Permissions