In ASP.NET you often require the ID of a row that you have inserted into a SQL database, either to display the record, or to use the ID to modify data in linked tables.

If you are using an SqlDataSource or ObjectDataSource then you can do this with the ReturnValue method.

As an example, consider the following FormView with an InsertItemTemplate:

<asp:formview datakeynames="id" datasourceid="DataSource1" id="FormView1" 
runat="server" defaultmode="Insert">
    <InsertItemTemplate>
        Name: 
        <asp:TextBox runat="server" ID="txtName" 
            Text='<%# Bind("Name") %>'  />
    </InsertItemTemplate>
</asp:formview>

This is a very simple Formview with just one textbox which asks for a name.

The Formview is linked to a database table which has 2 columns, an ‘ID’ field which is an auto-incremented primary key, and my ‘Name’ field.

In my .aspx page, I have the following SQLDataSource (you can use an ObjectDataSource just as easily):

<asp:SqlDataSource ID="DataSource1" runat="server" 
    OnInserted="dsFormView1_OnInserted"
    ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
    InsertCommand="INSERT INTO MyTable (Name) VALUES (@Name) 
                    SET @ID = SCOPE_IDENTITY();">
    <InsertParameters>
        <asp:Parameter Name="Name" Type="string" />
        <asp:Parameter Name="ID" Type="Int32" Direction="Output" />
    </InsertParameters>
</asp:SqlDataSource>

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

Finally, within your code_behind file, the ID ends up in your Data Sources EventArgs:

Protected Sub dsFormView_OnInserted(ByVal sender As Object, 
        ByVal e As SqlDataSourceStatusEventArgs)

    Dim myID As String = command.Parameters("@ID").Value.ToString()
    Response.Write(myID)

End Sub

One last point – your query should be of type ExecuteScalar, so set that within your database designer in Visual Studio, or set it via code to ensure that your query returns the ID.

Hope this helps, and If you enjoyed this article, then subscribe to my RSS feed.