Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Posts
    34

    Unanswered: IDENTITY column problems...

    Hi, I have a problem with my IDENTITY column (item_id) and my INSERT statement into my 'inventory' table.

    The stored procedure is executed from VB6, if the user enters an error, and the INSERT statement does not complete the IDENTITY column still increments. i.e. The 'amount' field is NOT NULL, if the user forgets to enter an amount, an error occurs in VB and the row is not inserted, but the IDENTITY column still increments.

    I am NOT worried about filling in IDENTITY column gaps if a record is deleted, but I do want IDENTITY values to be in sequence (NO GAPS) when inserting records.

    I have looked at DBCC CHECKIDENT, but dont understand how to use the values returned from it.

    Here is the SP I am using:


    CREATE PROCEDURE insert_inventory
    @item_id int,
    @item_name varchar(20),
    @description varchar(100),
    @notes varchar(255),
    @amount char(8)
    AS
    SET NOCOUNT ON
    DECLARE @transaction_date datetime

    BEGIN TRANSACTION

    IF (@item_name = '') SET @item_name = NULL
    IF (@description = '') SET @description = NULL
    IF (@notes = '') SET @notes = NULL
    IF (@amount = '') SET @amount = NULL

    SET @transaction_date = GETDATE()
    INSERT INTO inventory (item_name, item_description, notes) VALUES (@item_name, @description, @notes)
    IF @@ROWCOUNT = 0 OR @@ERROR <> 0
    BEGIN
    RAISERROR('insert_inventory SP FAILED', 16, 1)
    ROLLBACK TRANSACTION
    RETURN
    END
    SET @item_id = @@IDENTITY
    INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@item_id, @transaction_date, CAST(@amount AS money))
    IF @@ROWCOUNT = 0 OR @@ERROR != 0
    BEGIN
    RAISERROR('insert_inventory SP FAILED', 16, 1)
    ROLLBACK TRANSACTION
    RETURN
    END
    COMMIT TRANSACTION

  2. #2
    Join Date
    Aug 2002
    Location
    SHANGHAI,CHINA
    Posts
    20
    I think u have to make a sequence table with which u can control the increment of the sequence number.

    Examples:

    1 Sequnce Table

    CREATE TABLE [SEQUENCES] (
    [seq_name] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [seq_start] [int] NOT NULL ,
    [seq_step] [int] NOT NULL ,
    [seq_curval] [int] NOT NULL ,
    [maxvalue] [int] NOT NULL ,
    [ifcycle] [bit] NOT NULL ,
    [remark] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_SEQUENCES_remark] DEFAULT (''),
    [Status] [int] NOT NULL CONSTRAINT [DF_SEQUENCES_Status] DEFAULT (0),
    CONSTRAINT [PK__SEQUENCE__76CBA758] PRIMARY KEY CLUSTERED
    (
    [seq_name]
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    2 The sp get the incremetal sequence number

    CREATE PROCEDURE dbo.sp_GetSequenceNo
    (@SequenceName varchar(255),@seqno int output)
    AS
    BEGIN
    set nocount on
    BEGIN TRAN
    select @seqno=0
    UPDATE dbo.Sequences SET seq_curval=seq_curval+seq_step
    WHERE seq_name=@SequenceName
    IF @@error!=0
    BEGIN
    ROLLBACK tran
    return 0
    END
    SELECT @seqno=seq_curval FROM dbo.Sequence
    WHERE seq_name=@SequenceName
    COMMIT TRAN

    select @seqno

    set nocount off
    END

    3 make a function which call the sp in step 2

    create function dbo.fn_default_seqno(@SequenceName varchar(255))
    RETURNS int
    AS
    BEGIN
    declare @Seqno int

    EXEC dbo.sp_GetSequenceNo @SequenceName,@Seqno output

    RETURN @Seqno

    END

    4 u can set the function as the defaut value of your table's id column

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't have a solution, but i would like to ask a question
    I do want IDENTITY values to be in sequence (NO GAPS) when inserting records
    i'm very curious: why?

    what are you doing that depends on no gaps? counting records by subtracting first id number assigned from last id number assigned?

    i've seen this problem many times, and i'm always interested in what people try to get identity columns to do for them

    rudy
    http://r937.com/

  4. #4
    Join Date
    Sep 2003
    Posts
    34
    I just dont want wasted records if there is no need. With my problem, if a user, when inserting one record happened to enter invalid values 10 times, then there would be 10 wasted (non retrievable) records in the system.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if the inserted records have invalid values and in fact did not get inserted, then they don't exists, right?

    so the only thing "wasted" are numbers that did not get assigned

    you could just as easily worry about the ten numbers between 423475345 and 423475355 -- those didn't get assigned, either


  6. #6
    Join Date
    Sep 2003
    Posts
    34
    thats the problem, they DONT get inserted, but IDENTITY value still increments

    ID values are shown to user, given to clients as Customer ID's, so would look better to be as close together as possible, I think anyway
    Last edited by raydenl; 09-23-03 at 09:00.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ID values should not be shown to user, that's not what they are for

    if you must do so, consider using random numbers, not sequential

    do you do anything to ensure that a user can access only her own data via the ID?

    and how would a user know that the ID number after his number isn't really there? and why would he care?

    sorry to be so persistent, i'm just curious


    rudy

  8. #8
    Join Date
    Sep 2003
    Posts
    34
    Yeah u r right, random would be better, so...

    How would I go about generating a RANDOM UNIQUE five (could be more or less, but 5 would be best) digit number for use as a primary key for my database table?

    I want to end up with keys looking like this, I can append the 3 char code to the number:

    Clients table:

    CLT45245
    CLT27441
    etc

    Volunteers table

    VOL26734
    VOL29063
    etc

  9. #9
    Join Date
    Sep 2003
    Posts
    34
    Just to clarify, the only user of the system is the owner of the system, the clients and volunteers mentioned would never see the DB, they are only stored on it, but they would be sent there client/volunter ID once entered into it.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    see RAND

    since you want only 5 digits, you might have to generate a number, attempt the insert, and if you hit a dupe, generate another, and attempt the insert again

    a stored proc would be best for this


    rudy

  11. #11
    Join Date
    Sep 2003
    Posts
    34
    SET @RANDOM = ((99999 - 10000) * Rand() + 10000)

    Thats my 5 digit random number, but how would I implement this using a SP?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •