Zero being inserted in identity column

  • 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.

  • 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, MVP, M.Sc (Comp Sci)
    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
  • 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.

  • 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, MVP, M.Sc (Comp Sci)
    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
  • ramadesai108 (3/8/2010)


    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.

    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, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply