doddwell
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Flavo - that worked..thanks very much.
anneeshattingal - what does your mod do....please advise.
anneeshattingal - what does your mod do....please advise.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
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.
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