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:

<asp:parameter direction="Output" name="answerID" type="Int32"></asp:parameter>
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


INSERT INTO [Answers] (



Set @answerID = scope_identity()

No comments:

Post a Comment

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