Web Forms

Re: Returns the primary key of the inserted row.

smcirish — Thu, 15 May 2008 13:02:14 GMT

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

Here is an example of Scope_Identity with Stored Procedures.

-smc

Re: Returns the primary key of the inserted row.

bmains — Tue, 10 Jan 2006 17:34:43 GMT

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).

Re: Returns the primary key of the inserted row.

Patheon — Tue, 10 Jan 2006 16:06:32 GMT

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?

Re: Returns the primary key of the inserted row.

bmains — Tue, 10 Jan 2006 12:58:29 GMT

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

Re: Returns the primary key of the inserted row.

Patheon — Mon, 09 Jan 2006 18:28:21 GMT

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.

Re: Returns the primary key of the inserted row.

bmains — Mon, 09 Jan 2006 17:45:59 GMT

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.

Returns the primary key of the inserted row.

Patheon — Mon, 09 Jan 2006 17:15:03 GMT

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?