how to read returned identity value from stored procedure
-
I am inserting record in table using stored procedure. CREATE PROCEDURE add_member( @UserName Varchar( 50 ), @Password Varchar( 50 ), @dob datetime, @education Varchar(50), @designation Varchar(50), @name Varchar(50) ) AS If Exists( SELECT username FROM member WHERE username = @username ) RETURN -1 ELSE BEGIN DECLARE @ID int -- new ID value holder INSERT member ( username, password, dob, education, designation, name ) VALUES ( @username, @password, @dob, @education, @designation, @name ) -- get the new identity column value SET @id = @@IDENTITY END GO ------------------------------- I am using classic asp to add records in this way - sql = "add_member " & "'" & request.form("username") & "'" & "," & "'" & request.form("password") & "'" & "," & "'2/2/2'" & "," & request.form("education") & "," & request.form("designation") & "," & "'" & request.form("name") & "'" set rsAdd = Server.CreateObject("ADODB.Recordset") rsAdd.Open sql, con Its adding new record successfully But I am not able to read id of newly added record. Plzzzzzzz help
-
I am inserting record in table using stored procedure. CREATE PROCEDURE add_member( @UserName Varchar( 50 ), @Password Varchar( 50 ), @dob datetime, @education Varchar(50), @designation Varchar(50), @name Varchar(50) ) AS If Exists( SELECT username FROM member WHERE username = @username ) RETURN -1 ELSE BEGIN DECLARE @ID int -- new ID value holder INSERT member ( username, password, dob, education, designation, name ) VALUES ( @username, @password, @dob, @education, @designation, @name ) -- get the new identity column value SET @id = @@IDENTITY END GO ------------------------------- I am using classic asp to add records in this way - sql = "add_member " & "'" & request.form("username") & "'" & "," & "'" & request.form("password") & "'" & "," & "'2/2/2'" & "," & request.form("education") & "," & request.form("designation") & "," & "'" & request.form("name") & "'" set rsAdd = Server.CreateObject("ADODB.Recordset") rsAdd.Open sql, con Its adding new record successfully But I am not able to read id of newly added record. Plzzzzzzz help
-
That's because you aren't returning it. You set @ID, but you need to either set an output parameter or select @ID to return it to your code.
Bob Ashfield Consultants Ltd
How to return and read in asp please explain in detail Thanks in advance
-
How to return and read in asp please explain in detail Thanks in advance
You can either use an OUTPUT parameter in your query (search SQL Help for OUTPUT PARAMETER) or simply do a select and return the value in a recordset just like any other select statement does. An output parameter is more efficient. To be honest, its so long since I used ADODB that I can't remember the syntax for retriving output parameters, but I do remeber you have to close the recordset first, strange as it seems. Hopefully this will give you some pointers to enable you to resolve your problem after a little work on Google.
Bob Ashfield Consultants Ltd
-
I am inserting record in table using stored procedure. CREATE PROCEDURE add_member( @UserName Varchar( 50 ), @Password Varchar( 50 ), @dob datetime, @education Varchar(50), @designation Varchar(50), @name Varchar(50) ) AS If Exists( SELECT username FROM member WHERE username = @username ) RETURN -1 ELSE BEGIN DECLARE @ID int -- new ID value holder INSERT member ( username, password, dob, education, designation, name ) VALUES ( @username, @password, @dob, @education, @designation, @name ) -- get the new identity column value SET @id = @@IDENTITY END GO ------------------------------- I am using classic asp to add records in this way - sql = "add_member " & "'" & request.form("username") & "'" & "," & "'" & request.form("password") & "'" & "," & "'2/2/2'" & "," & request.form("education") & "," & request.form("designation") & "," & "'" & request.form("name") & "'" set rsAdd = Server.CreateObject("ADODB.Recordset") rsAdd.Open sql, con Its adding new record successfully But I am not able to read id of newly added record. Plzzzzzzz help
Add this as you last line.
Select @ID as IDNever underestimate the power of human stupidity RAH
-
Add this as you last line.
Select @ID as IDNever underestimate the power of human stupidity RAH
Then How to read this value in asp
-
Then How to read this value in asp
The resilt can be used as either executescalar directly to a varaible or as a dataset/table using .fill.
Never underestimate the power of human stupidity RAH