For heavens sake, this took me quite some time to get around. Okay here goes. 1,2,3 easy steps to get ID from MS SQL using datasets.

1. In visual studio, under your DAL layer, you should have a dataset where you hold your table adapters.
2. right click on the table adapter and click configure to get to your TABLEADAPTER CONFIGURATION Wizard.
3. Click on advanced options
4. Make sure Refresh the data Table option is checked
5. Save the changes
6. Go to configure your insert statement in the ADAPTER
7. Enter a new line with — Return the applicationsID for the newly created record
   SELECT SCOPE_IDENTITY()
8. Select the Insert Query and in the Properties box, change ExecuteMode is Scalar
9. Going back to your code (or BLL) where you initiated the table adapter, you can access your id using
   int x = Convert.ToInt32(myTableAdapter.InsertQuery(……..));
10. Remember the convert to else it will give an object error. And Volia! You get the damned product ID.

Pardon my french but this is oddly simple and frustrating to work with.