scope_identity question

I have a app that is inserting data into a SQL 2005 database and I would like to return the UniqueID of the inserted record.

I am using

Dim queryStringAsString ="INSERT INTO dbo.DATATABLE (FIELD) VALUES (@.FIELD);SELECT Scope_Identity()"

Dim sIDAsString = comSQL.ExecuteScalar()

This isn't working - it says the value returned is DBNull...

Any ideas on how to make this work?

You can convert the adhoc SQL to a stored proc and use a proper OUTPUT parameter and retrieve the ID. Also, is that your entire code? Where are you opening the connection? where is it define/initialized etc?

|||

hi mate,

The syntax used for retriving identity value is worn...It will be as follows

SELECT @.@.SCOPE_IDENTITY

Otherwise you can use

SELECT @.@.Identity

Hope now i can see ur smile

Thanx

Vijay

|||

Your use of scope_identity() is correct. Perhaps your insert is failing. Can you make sure that new row are actually being added to the table?

|||

elegantkvc:

The syntax used for retriving identity value is worn...It will be as follows

SELECT @.@.SCOPE_IDENTITY

This is incorrect. The correct way to use it is SCOPE_IDENTITY().