Unable to use SCOPE_IDENTITY successfully 1

[PaulAH]

I wish to retrieve the identity of a record I've just previously written (using ADO to MS SQL 2000 DB).

Like this....

(table TEST_TABLE has an identity column)

sSQL = "INSERT INTO TEST_TABLE (TESTFIELD1 ) "
sSQL = sSQL & "VALUES(?)"
Set oCommand = New ADODB.Command
Set oCommand.ActiveConnection = ADOConn
oCommand.CommandText = sSQL
oCommand.CommandType = adCmdText
oCommand.Parameters(0) = "ABC"
oCommand.Execute

Dim vID as Variant
sSQL = "Select SCOPE_IDENTITY()"
oCommand.CommandText = sSQL
vID = oCommand.Execute

vID contains Null yet all the blurb I've read says it should contain the unique ID of the new record.

If I replace SCOPE_IDENTITY() with @@IDENTITY it works...however the scope of this is global so presumably will not be correct 100% of the time.

Any help would be appreciated...thanks

 

[gmmastros]

My suggestion is to create a stored procedure in the database. This stored procedure would insert the data AND return the identity value.

[COLOR=blue]Create[/color] [COLOR=blue]Procedure[/color] usp_InsertTestData
	@TestField1 [COLOR=blue]VarChar[/color](20)
[COLOR=blue]AS[/color]
[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] TEST_TABLE (TESTFIELD1 ) 
[COLOR=blue]Values[/color] (@TestField1)

[COLOR=blue]Select[/color] Scope_Identity() [COLOR=blue]As[/color] Id

You would call this stored procedure the same way you would call any other stored procedure that returns data. In this case, there will be 1 record with 1 column (named ID).

Give this a try. I think you will be pleasantly surprised.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JoeAtWork]

I second gmmastros, best to have it return the ID from a stored procedure.

 

[PaulAH]

Thanks for your suggestions guys

I've found @@Identity does work.
I've tested with other users creating records after I do and before I run the @@Identity statement and it still returns the correct ID..

Thanks again and a star for you

 

[gmmastros]

Paul,

Since you are using SQL Server, i would encourage you to NOT use @@Identity.

Most of the time @@Indentity will work for you, but it is possible that it could return unexpected values. You might think to yourself, "This will never happen to me", but don't let that stop you from doing the right thing.

Let me explain...

@@Identity will return the most recent identity value that was inserted in to a table, any table. So, if you or another developer adds an insert trigger to this table that inserts in to another table, then the @@Identity will return the value that was inserted in to the table that the trigger inserted, not the table that you inserted in to.

If you use Scope_Identity() you'll never go wrong.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Please read this for further explanation.

thread183-1353336

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom