Retrieve Row ID (rowid) (SCOPE_IDENTITY();) after Detailsview Insert

Posted by admin on August 6, 2009 under 4-Code snippets | 2 Comments to Read

There are a number of examples on the web of how to retrieve a row ID after a SQL insert. But what if the insert is part of a declarative SQL statement defined for a SQLDataSource? How do you expose the identity? it turns out it is fairly simple. If you retrieve the scope_identity as part of the Insert statement, you can retrieve it in the item_inserted event. However, I found that you need to create the item_inserted event manually with different parameters than those generated automatically by Visual Studio.

Let’s say you are saving a payment record. Your detailsview is called dtlPayment. In the SQL Insert statement, you will have something like this:

<asp:SqlDataSource       …

 InsertCommand  = “INSERT INTO [tblPayments] ([BillingName], [BillingAmount]) VALUES (@BillingName, @BillingAmount);  Select @Payment_rowid=SCOPE_IDENTITY();”
 OnInserted=”dtlPayment_ItemInserted”
          …
</asp:SqlDataSource>

In the Insert parameters section, define the parameter as an output parameter

    <asp:parameter direction=”Output” name=”Payment_rowid” type=”Int32″ />

Now set up the following event:

Protected Sub dtlPayment_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
       Dim thisRowID As Integer
       thisRowID = e.Command.Parameters(”@Payment_rowid”).Value
End Sub