Returning an identity value from SQL Server after an insert using an ObjectDataSource

How to return the new identity value from SQL Server after an insert using the objectDataSource in Visual Studio 2005



This is incredibly badly documented, and there are no clear instructions anywhere else on the Internet that I know of! I spent an entire day getting to the bottom of this...



Assumptions: you're using Visual Studio 2005, SQL Server 2000 or later or SQL Express, you can use the Dataset designer.



Create a dataset in Visual Studio



Create a TableAdapter for the relevant table



Add an Insert Query to the TA eg: INSERT INTO [Table1] ([data1]) VALUES (@data1);select @id= scope_identity(); - note that there is an insert query and a select query!





After creating the query (say, named InsertQuery) go to the Properties of the query (ie highlight the query and inspect Properties.)



In the Parameters collection, add a new parameter matching the output

parameter - here it's @id - create a parameter named @id. Set the various values such as direction and type and name it the same @id. Make sure you set the datatypes and make Direction "output".





Now go to your web page in the designer. On the relevant web page, create an objectdatasource. Highlight the objectdatasource and select the property Input Parameters. Add a new parameter matching our

output parameter. In this case, name the parameter "id", source none,

then select Advanced Properties.





In Advanced Properties, set Direction, Name and Type (here, it's Output, ID, int32)



Now you wire up your various textboxes or whatever, and make them inputs for your datasource. When all the input parameters are sorted out, create a "Submit" button.



The code-behind for the Submit is, at it's most basic:



objectDataSourceName.submit()



Actually you'll want to do validation and error trapping etc - probably in the Inserting event for the objectDataSource.



In the Inserted event for your objectDataSource (ie in the code behind, create a sub for the Inserted event for the ObjectDataSource) you can access the new parameter thus:

Dim intNewIdentity As string = e.OutputParameters("id").tostring



intNewIdentity will contain the identity value (in most cases - if it's an incredibly high traffic site there is a chance that you will get someone else's identity value due to way the database returns scope_identity(). I miss sequences in Oracle...