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 TRANSACTIONI 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|||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 recordsi'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/|||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.|||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

;)|||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|||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|||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|||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.|||see RAND (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp)

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|||SET @.RANDOM = ((99999 - 10000) * Rand() + 10000)

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