Probably the most frequent question I field from readers of my Working with Data in ASP.NET 2.0 tutorial series is, How do get the just-inserted ID value from the DAL? This is an important area, and one that I should have authored a tutorial on. There are many situations in which the just-inserted ID value is needed. For example, after a user adds a new record to the database you might want to automatically redirect them to the details page for that new record, something like, ~/Details.aspx?ID=X. In this case you need to know the just-inserted ID because that value is used in the querystring.
I did briefly discuss one technique for accomplishing this in the first tutorial. I showed how you could add a stand-alone Insert method to the TableAdapter and define the query so that it returns the newly inserted record (i.e., doing a SELECT SCOPE_IDENTITY() immediately after running the INSERT statement). Visit this screenshot to see the TableAdapter Query Configuration Wizard step that specifies the INSERT/SELECT statements. In addition to adding the SELECT statement, you also need to set the ExecuteMode property of the method to Scalar. Once this method has been added to the TableAdapter, calling it inserts the product and returns the just-inserted ID:
Dim productsAdapter As New NorthwindTableAdapters.ProductsTableAdapter()
Dim new_productID As Integer
new_productID = productsAdapter.InsertProduct(...)
While I illustrates this technique, I failed to use it in any subsequent data tutorials. Instead, the subsequent tutorial created a Business Logic Layer (BLL) that used the TableAdapter's batch Update() method to handle inserts and updates. The good news is you can instruct the TableAdapter to return information about the just-added record for those records added via the batch update pattern. When creating the TableAdapter, click the Advanced button in the wizard and check the 'Refresh the DataTable' option (see below). This causes the DataTable to be “refreshed” after adding or updating a record via the built-in DataTable data modification methods.
Next, update your BLL methods. For example, the tutorials create an AddProduct() method in the ProductsBLL class that returns a Boolean indicating whether a product was inserted or not. Modify this function to return an integer instead (since the ProductID field in the database is an int) and then adjust the method so that the code at the end looks like this:
' Add the new product
products.AddProductsRow(product)
' Return the newly inserted ProductID value...
Dim newProductID As Integer = product.ProductID
Return newProductID
The AddProductsRow is a method in the Products table's DataTable. After the row is added, it is 'refreshed' and the resulting, newly-inserted ProductID value is available via the object's ProductID property.
Now, how do you work with the newly-inserted ProductID value from the presentation layer? If you call the BLL programmatically, just work with the return value of the AddProduct() method:
Dim productAPI As New ProductsBLL
Dim newProductID As Integer
newProductID = productAPI.AddProduct(...)
That was pretty easy.
If you're using an ObjectDataSource to do the insert then you can grab the value returned by the InsertMethod in the Inserted event handler:
Protected Sub ProductDataSource_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceStatusEventArgs) Handles ProductDataSource.Inserted
Dim newProductID As Integer
newProductID = e.ReturnValue
...
End Sub
I have a very simple demo illustrating this. The demo is in VB and uses ad-hoc SQL queries, although the concepts are the same when using C# or working with stored procedures. Download the demo here.
Happy Programming!