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?
Printable View
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?
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...
VB Code:
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
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?
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?
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
VB Code:
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 :D
hth
Cheers
MarkusJ:D
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?