auto increment a number

[kanuri1]

hi frnd
this is raghuram
i had got a problem
when iam working with a form ,,,,in a textbox it will automatically increment a serial number and it will be stored in sqlaerver2005 database at evey time when form is loaded.....
pls give me reply as early as possible.....

[sknake]

Why not have SQL Server generate the auto increment number for you? Create a table with an identity column:

Create Table Tbl
(
  ID int identity(1000, 1) PRIMARY KEY,
  NameFirst varchar(50),
  NameLast varchar(50)
)

Then insert data:

Insert Into Tbl (NameFirst, NameLast) Values ('Scott', 'Knake')
Select Cast(SCOPE_IDENTITY() as int) as Result

SCOPE_IDENTITY() will return the auto increment number from the insert operation.

[kanuri1]

Why not have SQL Server generate the auto increment number for you? Create a table with an identity column:

Create Table Tbl
(
  ID int identity(1000, 1) PRIMARY KEY,
  NameFirst varchar(50),
  NameLast varchar(50)
)

Then insert data:

Insert Into Tbl (NameFirst, NameLast) Values ('Scott', 'Knake')
Select Cast(SCOPE_IDENTITY() as int) as Result

SCOPE_IDENTITY() will return the auto increment number from the insert operation.

but here the table is already created but the problen is when the button is clicked it will automaticalyy display the auto increment of serial number....
so give me the code for auto increment of serial number when button is clicked and it will be saved in the database....here the duplicate values of serial number is not occurs...........

[sknake]

That is a dangerous design and will eventually fail. However you shouldn't maintain the "most recent" identity in your application. What you should do is grab the identity right before you insert a record. You could also make this "more-failsafe" by locking the table before you grab the identity value and then release the lock after your insert finishes and you commit the changes.

Here is how you can grab the ID on the fly when inserting a record:

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table
Create Table #Table
(
  ID int PRIMARY KEY,
  NameFirst varchar(10),
  NameLast varchar(10)
)

DECLARE @id int
Set @ID =
IsNull((
  Select Max(ID) + 1
  From #Table (NOLOCK)
), 1000) --1000 is the `identity seed`. It will start counting from 1000 and go up

Insert Into #Table (ID, NameFirst, NameLast) Values (@id, 'Scott1', 'Knake')
--Return the record number for what you just inserted
PRINT 'Inserted Record: ' + CAST(@id as varchar)


--Second record

Set @ID =
IsNull((
  Select Max(ID) + 1
  From #Table (NOLOCK)
), 1000)

Insert Into #Table (ID, NameFirst, NameLast) Values (@id, 'Scott2', 'Knake')
PRINT 'Inserted Record: ' + CAST(@id as varchar)

--Third record

Set @ID =
IsNull((
  Select Max(ID) + 1
  From #Table (NOLOCK)
), 1000) 

Insert Into #Table (ID, NameFirst, NameLast) Values (@id, 'Scott3', 'Knake')
--Return the record number for what you just inserted
PRINT 'Inserted Record: ' + CAST(@id as varchar)

PRINT ''
PRINT 'Selecting...'
PRINT ''

Select *
From #Table

Results:

Inserted Record: 1000
Inserted Record: 1001
Inserted Record: 1002
 
Selecting...
 
ID          NameFirst  NameLast
----------- ---------- ----------
1000        Scott1     Knake
1001        Scott2     Knake
1002        Scott3     Knake

Again I stress this is a bad design and maintaining the current identity seed value in your application is another bad decision.