1

i have a sql table with an Auto increment column, i set the identity increment = 1. i add my records with peta poco classes as bellow:

[Scope("ProductID")]
    [TableName("TblColors"]
    [PrimaryKey("ProductColorID", AutoIncrement = true)]
    [Cacheable("ProductColorInfo", CacheItemPriority.Default, 20)]
    public class ProductColorInfo
    {
        public int ProductColorID { get; set; }
        public int ProductID { get; set; }
        public int ColorID { get; set; }
        public string Name { get; set; }
        public string Caption { get; set; }
        public string ImageName { get; set; }
        public float Cost { get; set; }
    }

public int AddColor(ProductColorInfo item)
    {
        using (IDataContext ctx = DataContext.Instance())
        {
            try
            {
                ctx.BeginTransaction();

                var rep = ctx.GetRepository<ProductColorInfo>();
                rep.Insert(item);

                // get last Color ID from a stored procedure
                var LastID = ctx.ExecuteScalar<int>(System.Data.CommandType.StoredProcedure, "spGetLastRecordID", "ProductColorID");
                if (LastID < 1)
                    throw new Exception("Can not Get Last ProductColorID");

                ctx.Commit();
                return LastID ;
            }
            catch
            {
                ctx.RollbackTransaction();
                return -1;
            }
        }
    }

it works well. but when i check my table, sometimes my auto increment column "ProductColorID", increase more than 1. i know if i delete some records i will have gap. but the increase between ProductColorIDs are more than just some deleted recordeds. i mean if my last ProductColorID was 120, the next time i add a record the new ProductColorID become 100065!!! I'm sure that i have never add 100065 record and then delete them. it happened couple of time and i can not find when, where and why?

what is the problem with my auto identity column?

maryam mohammadi
  • 694
  • 3
  • 14
  • 27
  • 3
    possible duplicate of [SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry](http://stackoverflow.com/questions/14162648/sql-server-2012-column-identity-increment-jumping-from-6-to-1000-on-7th-entry). or http://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database – Misha Zaslavsky Jun 01 '14 at 05:34

0 Answers0