Tips for returning IDENTITY values from INSERT

There’s a good article by Scott Guthrie here that describes the basics. Just scroll down to Tutorial 5 for the INSERT specific bit – it is pretty straightforward and there is no point my repeating it here.

One point to note, however, and why I wrote this particular post. The key step in getting this working is changing the query type from NonQuery to Scalar. But this can get reset by Visual Studio if you choose the Configure option on the ObjectDataSource’s smart tags to modify the query in any way. If you don’t notice this, then suddenly the identity value stops getting returned and your code breaks.

The second tip is that by default the type returned by the insert query is decimal. This can cause some extra casting in your code if you were expecting it to be int. To fix this, just modify the SQL in Scott’s original article to be this:

SELECT CAST (SCOPE_IDENTITY() AS INT)

Leave a Reply