Does anyone have a working example in Visual Web Developer 2005 of  using a stored procedure to insert a record and return the primary key of the inserted row?

Hey,

Your stored procedure has to do the insert, then select the ID value, then in your code, when you execute through a Command, use the ExecuteScalar() method of execution, which returns the first value of a single field select statement.

Hi Brian, Thanks for the reply. I have created a Stored procedure to insert the record... and it inserts fine. Could you give me an example of what you mean? Thanks in advance.

Stored procedure has to:

insert into table (..) values (..)
select @@identity

Then in your code, setup the command, then do:

Dim objValue As Object = objCommand.ExecuteScalar()
if (IsNumeric(objValue)) then
   Dim intID as Integer = ctype(objValue, Integer)
end if

Thanks Brian.... I've now got it to work.... I can retrieve the Instance ID of the newly created row. Now I have a new problem... The record is being inserted Twice.  I'm using a SqlDataSource with the commandfield Insert Button, I've placed the code you gave me in the

Protected Sub SqlDataSource1_Inserted

the code you gave me, the ExecuteScalar() does it cause the stored procedure to be fired again? If so, then where and how can I avoid this situation?

 

Oh OK, sorry, I didn't know you were using a SqlDataSource; I'm not sure of the best way to do this with a Data Source control, but maybe by adding another parameter as an output parameter to the stored procedure, then add this to your insertparameters collection, and check that parameters defaultvalue property (sqlds.InsertParameters["RETURN_VALUE"].DefaultValue).

http://www.mikesdotnetting.com/Article.aspx?ArticleID=54

Here is an example of Scope_Identity  with Stored Procedures.

-smc