return value from sp

[Guest]

Hi all,

I'm having problems returning an OUTPUT parameter from a
stored procedure in ADP. I want to insert a record in a
table an return the Identity using @@IDENTITY function.
The stored procedure is OK...working from QA. However,
from Access I can't retrive anything.

Below is the code I'm using from Access

*****************************************************
Set cmd = New ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "sp_Insert"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters("ParamIn1") = varParamIn1
cmd.Parameters("ParamIn2") = varParamIn2
cmd.Parameters("ParamIn3") = varParamIn3

cmd.Execute

MsgBox (cmd.Parameters("ParmOut").Value)
..

 

[Peter Kaufman]

Hi Peter,

You only post part of the Access code and did not post the sp, so it
is tough to say where it is going wrong. However, this does work

Stored proc
=========
CREATE proc procIdentity
@pMin as integer, @pMax as integer, @pIDS as integer OUTPUT
AS
-- Pubs SQL database
INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',@pMin,@pMax)
SELECT @pIDS=@@IDENTITY

calling code
===========
Function fnIDS()
Dim conn As Connection
Dim cmd As ADODB.Command
Set conn = CurrentProject.Connection

Set cmd = New ADODB.Command
cmd.Parameters.Append cmd.CreateParameter("@Min", adInteger,
adParamInput, , 10)
cmd.Parameters.Append cmd.CreateParameter("@Max", adInteger,
adParamInput, , 50)
cmd.Parameters.Append cmd.CreateParameter("@pIDS", adInteger,
adParamOutput)
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "procIdentity"
cmd.Execute
MsgBox cmd.Parameters("@pIDS")

Set cmd = Nothing
Set conn = Nothing
End Function

Peter

 

[Guest]

Thanks, it worked!!!

-----Original Message-----
Hi Peter,

You only post part of the Access code and did not post the sp, so it
is tough to say where it is going wrong. However, this does work

Stored proc
=========
CREATE proc procIdentity
@pMin as integer, @pMax as integer, @pIDS as integer OUTPUT
AS
-- Pubs SQL database
INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',@pMin,@pMax)
SELECT @pIDS=@@IDENTITY

calling code
===========
Function fnIDS()
Dim conn As Connection
Dim cmd As ADODB.Command
Set conn = CurrentProject.Connection

Set cmd = New ADODB.Command
cmd.Parameters.Append cmd.CreateParameter("@Min", adInteger,
adParamInput, , 10)
cmd.Parameters.Append cmd.CreateParameter("@Max", adInteger,
adParamInput, , 50)
cmd.Parameters.Append cmd.CreateParameter("@pIDS", adInteger,
adParamOutput)
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "procIdentity"
cmd.Execute
MsgBox cmd.Parameters("@pIDS")

Set cmd = Nothing
Set conn = Nothing
End Function

Peter



.
!