Adding auto number

[berrie_luv]

I got a add form which is able to increment a no, But how do i set in e sql statement? I wan to insert data into e database without asking e user to type wat record is that, i wan e form to auto increment e no. How do i done it?

[MarkusJ_NZ]

This is for SQL server and VB.Net:

Insert the record into the database first and let the database assign the autonumber (In SQL server its a special variable called @@IDENTITY). Retrieve the @@IDENTITY by passing back the value from a stored procedure to the calling function, SP shown below...

CREATE PROCEDURE InsertPage
@BookID int,
@PersonID int,
@PageOrder float,
@PageName varchar(50),
@TemplateInfo nvarchar(5) = NULL
 AS
BEGIN
Declare @nID int
 
-- Try to do the update first
INSERT INTO BookPage (BookID, PersonID, PageOrder, PageName,TemplateInfo) 
VALUES
(@BookID, @PersonID,@PageOrder, @PageName,@TemplateInfo)
-- Get the Identity
Select @nID = @@IDENTITY
END 
Return @nID
GO

This stored procedure passes back the inserted autonumber.

Call the Stored procedure via ADO.Net and then assign this number to your form

hth
Cheers
MarkusJ

[berrie_luv]

wat is @@IDENTITY? Can u briefly describe it?
I dun understand this line.
Select @nID = @@IDENTITY
END
Return @nID
I need to change anything from e sql server database?

[berrie_luv]

ya, i got it liao, thanks.

But if i auto increment e no from table01, i also need the incremented no to save to another table02 too.
How do i do it?

[MarkusJ_NZ]

Hi, there are a couple of ways using SQL. One is using a Trigger for Insert or you could use the Stored procedure to do the insert into the other table

CREATE PROCEDURE InsertPage
@BookID int,
@PersonID int,
@PageOrder float,
@PageName varchar(50),
@TemplateInfo nvarchar(5) = NULL
 AS
BEGIN
Declare @nID int
 
-- Try to do the update first
INSERT INTO BookPage (BookID, PersonID, PageOrder, PageName,TemplateInfo) 
VALUES
(@BookID, @PersonID,@PageOrder, @PageName,@TemplateInfo)
-- Get the Identity
Select @nID = @@IDENTITY
 
/* Now do the insert into the other table
 
INSERT INTO YourTable(ID, attribute1, attribute2) VALUES (@nID,'Bill','gates')
 
*/
END 
Return @nID
GO

If you are going to start doing multiple Inserts, updates etc don't forget to use transactions

hth
Cheers
MarkusJ

[berrie_luv]

Hi, but from table01, i need e increment no to refer to another table02. As my form make use of 2 insert statements.
How i do refer to it?