Wednesday, 21 March 2012

Getting the ID of recently inserted record

To retrieve the ID of the recently inserted record in a sqldatasource control,

<asp:SqlDataSource ID="SqlDataSource" runat="server"
            ConflictDetection="CompareAllValues"
            ConnectionString="<%$ ConnectionStrings:DBConnectionString %>"
            InsertCommand="INSERT INTO [ClaimListing] ([UserID], [ClaimTypeCode]) VALUES (@UserID, @ClaimTypeCode); SELECT @NewClaimNo = SCOPE_IDENTITY();"
            OldValuesParameterFormatString="original_{0}"
            OnInserted="SqlDataSource_Inserted">
            <InsertParameters>
                <asp:Parameter Name="UserID" />
                <asp:Parameter Name="ClaimTypeCode" Type="Int32" />
                <asp:Parameter Direction="Output" Type="Int32" Name="NewClaimNo" />
            </InsertParameters>
        </asp:SqlDataSource>

And in the code behind,

protected void SqlDataSource_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {
        //Read the value of the @Identity OUTPUT parameter
        string ClaimNo = e.Command.Parameters["@NewClaimNo"].Value.ToString();
    }


The identity can be assigned to a hiddenfield control to be accessed from other function.

No comments:

Post a Comment