Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

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
0
doddwell
Asked:
doddwell
  • 3
  • 3
2 Solutions
 
flavoCommented:
Somethign like this:

    Dim cmd As ADODB.Command
   
    Set cmd = New ADODB.Command
   
    With cmd
        .ActiveConnection = CurrentProject.Connection
        .CommandType = adCmdStoredProc
        .CommandText = "uspMySp"
        .Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue)
        .Parameters.Append .CreateParameter("@AssetName", adChar, adParamInput, 10, "myValue") '// change myValue as req'd
        .Parameters.Append .CreateParameter("@AssetTown", adChar, adParamInput, 10, "myValue") '// change myValue as req'd
        .Execute
        MsgBox .Parameters("@RETURN_VALUE").Value
    End With

    Set cmd = Nothing
End Sub

Dave
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
doddwellAuthor Commented:
Flavo - that worked..thanks very much.
anneeshattingal - what does your mod do....please advise.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Aneesh RetnakaranDatabase AdministratorCommented:
I am not a front end expert, i knows only sql..

Create Procedure uspMySp
@AssetName char (10), @AssetTown char (10)
AS
SET NOCOUNT ON      /* this was missing in ur sp; If u dont put this, you will get the no.of rows affected along with the the return values */
INSERT INTO tblMyTable
([Assetname], [AssetTown])
values
(@AssetName, @AssetTown)
SELECT SCOPE_IDENTITY() /*Another good practice is to use SCOPE_IDENTITY() in leu of @@IDENTITY, refer BOL for detsils . Also it is better to return the values using either Output variable or using the select statements as i shown */

GO
0
 
doddwellAuthor Commented:
Thanks to you both of you for your help.  Most points to flavo for answering question directly...remainder to anneeshattingal for supplementary info.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
No probs for points.. But dont forget to follow the above rules while creating sps
0
 
doddwellAuthor Commented:
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.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now