Link to home
Start Free TrialLog in
Avatar of doddwell
doddwellFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Return @@Identity to ADP Front End

Hello
I have an SP in SQL Server 2000 that returns an Identity.  I want to display the Identity to the user via a message box in my Access Data Project (ADP).
My SP looks like this:

Create Procedure uspMySp
@AssetName char (10), @AssetTown char (10)
AS
INSERT INTO tblMyTable
([Assetname], [AssetTown])
values
(@AssetName, @AssetTown)
return @@Identity

MyTable contains 3 fields (@AssetCode, @AssetName, @AssetTown)

How would you execute the SP and provide the user with the @@Identity via a message box?  I do not know how to use parameters...so if that is the way forward I could do with an example.

Many thanks, Simon
ASKER CERTIFIED SOLUTION
Avatar of flavo
flavo
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aneesh
Can you modify your sp like this

Create Procedure uspMySp
@AssetName char (10), @AssetTown char (10)
AS
SET NOCOUNT ON
INSERT INTO tblMyTable
([Assetname], [AssetTown])
values
(@AssetName, @AssetTown)
SELECT SCOPE_IDENTITY()

GO
Avatar of doddwell

ASKER

Flavo - that worked..thanks very much.
anneeshattingal - what does your mod do....please advise.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks to you both of you for your help.  Most points to flavo for answering question directly...remainder to anneeshattingal for supplementary info.
No probs for points.. But dont forget to follow the above rules while creating sps
Thanks for that...have checked out your suggestions in BOL and looks like good practice...have applied it in this sp and will try and remember to do it in future.