Monday, 23 November 2009

Accessing an output paramater from a stored procedure - getting the id of an inserted row

What I want to do is access the id of the row I just inserted into the database. To do this I use the itemInserted event of the datasource.

Here I'm inserting using a SqlDataSource configured with the following output parameter within the InsertParameters tags:

<InsertParameters>
<asp:parameter direction="Output" name="answerID" type="Int32"></asp:parameter>
</InsertParameters>
and here's the inserted event handler
Protected Sub answerInserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs) Handles InsertAnswerDS.Inserted
theValueIWant = Convert.ToInt32(e.Command.Parameters("@answerID").Value)
End Sub
And what's going on in the stored procedure? It looks like this:
CREATE PROCEDURE [dbo].[InsertAnswer]
@questionID integer,
@answerText varchar(50),
@answerID integer output

AS
BEGIN

INSERT INTO [Answers] (
[questionID],
[answerText]
)

VALUES 
(
@questionID,
@answerText
)

END

SELECT SCOPE_IDENTITY()
Set @answerID = scope_identity()

No comments:

Post a Comment

Comments are moderated, so you'll have to wait a little bit before they appear!