ADP -- how to retrieve a procedure's output parameter in code?

[Hans De Schrijver]

Does anyone have an example of code that calls a stored procedure with an
input parameter and an output parameter, run the sproc and then retrieve the
output parameter... I'm trying to implement the classical example of adding
a new record to a table and capturing the record ID of the newly inserted
record to do something else with it.

Thanks in advance.

-- Hans

[BurtonRoberts]

Dim cnn as adodb.connection
Dim cmd as adodb.command
Dim prmIN as adodb.parameter
Dim prmOUT as adodb.parameter
Dim strInputValue as string
Dim intOutPutValue as integer

set cnn = currentproject.connection
set cmd = new adodb.command
strInputValue = "Hans"

With cmd
.CommandText = "procSprocName"
.CommandType = adCmdStoredProc
Set prmIN = .CreateParameter("@prm1name", adVarChar, adParamInput, 50,
strInputValue)
.Parameters.Append prmIN
Set prmOUT = .CreateParameter("@RecordID", adInteger, adParamOutput)
.Parameters.Append prmOUT
.ActiveConnection = cnn
.execute
End With

intOutPutValue = prmOUT.Value

For a shortcut to this see www.smartaccessnewsletter.com the september 2000
issue, "An ADO Command Factory For Stored Procedures".

Burton Roberts

".Hans De Schrijver" <t...@punctualgraphics.com> wrote in message
news:#KaBMT8sAHA.1912@tkmsftngp04...

[fro...@punctualgraphics.com]

Could you please also post a sample procedure that would go with your code?

Thanks in advance!

-- Hans

"BurtonRoberts" <Burton...@ims.msn.com> wrote in message
news:ecj8wh$sAHA.1968@tkmsftngp04...

[BurtonRoberts]

For the ADO code in my previous post, say you have a table, tblNames, with
2 fields: intID, strName.
intID is an Identity integer (autoincrementing) and strName is of type
varchar(50).

Alter Procedure procSprocName
@prm1name varchar(50),
@RecordID int OUTPUT

AS
--Insert the new record
INSERT tblNames (strName)
VALUES (@prm1name)

--Retrieve the new record number
SET @RecordID = @@Identity

RETURN

<fro...@punctualgraphics.com> wrote in message
news:#feNOgVtAHA.1436@tkmsftngp03...

[fro...@punctualgraphics.com]

I think the reason why it didn't work in my case was because the primary key
field was not an identity field.
My stored procedure is the following:

Alter Procedure sp_create_new_subject
@first_name nvarchar(20) = NULL,
@new_subject_id int OUTPUT

As

DECLARE @subject_id int

SELECT @subject_id = MAX(subject_id)
FROM tbl_subjects

IF @subject_id IS NULL
SET @subject_id = 1
ELSE

SET @subject_id = @subject_id + 1

INSERT INTO tbl_subjects (subject_id, first_name) VALUES (@subject_id,
@first_name)

SELECT @new_subject_id = MAX(subject_id) FROM tbl_subjects

Return 0

The subject_id field is primary key but not identity because I want to make
sure that the new subject_id is always 1 higher than the highest one,
regardless of whether the 5 last records were deleted. Identity will
incrementing from 10 to 11 to 12 to 13. Then if you delete records with id
12 and 13, the next insert will still be id=14. I need it to be id=12.
How can I return that new subject id in a reliable way. As you can see,
currently I insert a record, then retrieve the id of the highest id and
return that as an output parameter. However, my question is whether this is
reliable. What if another user inserted a record a fraction of a second
after my INSERT statement executed but before the MAX() function retrieves
the highest id. Will I get my id or the id of the record inserted after mine
was inserted? Can any other operations be performed between my INSERT and
SELECT statement, even though they're in one procedure?
I'd appreciate your help once again....

-- Hans

[Douglas J. Steele]

Since you're calculating the value of the key in your stored procedure,
simply return the value you calculated.

I believe you can use:

SELECT @new_subject_id = @subject_id

--

Doug Steele, Microsoft Access MVP
Beer, Wine and Database Programming. What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://I.Am/DougSteele/

<fro...@punctualgraphics.com> wrote in message
news:uXx3W#YtAHA.1752@tkmsftngp05...