By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,300 Members | 1,682 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,300 IT Pros & Developers. It's quick & easy.

Get stored procedure output value back in VBA

P: n/a
I need to get the value of an output parameter back into my VBA
function calling a stored procedure.

I'm using the following construction to append a new record in a SQL
Server table:

Function ThisFunctionInsertsANewRecord()
On Error GoTo myErr

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandTimeout = 0
cmd.CommandText = "myStoredProcedureName"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("@parameter1", _
adVarChar, adParamInput, 4, Left(me.Field1, 4))
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@parameter2", _
adInteger, adParamInput, , me.Field2)
cmd.Parameters.Append prm

'note - this is an output parameter:
Set prm = cmd.CreateParameter("@UNIQUEID", _
adInteger, adParamOutput)
cmd.Parameters.Append prm

cmd.Execute Options:=adExecuteNoRecords

myExit:
On Error Resume Next
Set prm = Nothing
Set cmd = Nothing
Exit Function
myErr:
MsgBox Err.Number & " " & Err.Description
cmd.Execute "IF @@trancount > 0 ROLLBACK TRANSACTION", ,
adExecuteNoRecords
Resume myExit
End Function

The StoredProcedure looks like this:

Alter PROCEDURE myStoredProcedureName
@parameter1 nvarchar(4),
@parameter2 int,
@UNIQUEID int OUTPUT
AS

set nocount on

DECLARE @err int

BEGIN TRANSACTION

INSERT INTO
tblName (Column1, Column2)
VALUES
(@parameter1, @parameter2)

SELECT @UNIQUEID = SCOPE_IDENTITY()

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN
@err END

COMMIT TRANSACTION
SELECT @err = @@error IF @err <> 0 RETURN @err

What I want to do is to modify the VBA function so that it gets the
value of @UNIQUEID (SCOPE_IDENTITY()) so that I can open the new record
from the function.

Any help is appreciated!
lq

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Bri
Lauren,

I had a similar question and recieved an answer to it from Steve
Jorgensen. Here is his answer:

====
Steve Jorgensen wrote:
If you're using ADO, just have your stored procedure return the number
of records affected through an output parameter, and use the Parameters
collection of the Command object to retrieve the value. If you're
useing DAO, have your stored procedure return the count of rows affected
using a select statement, and use the OpenRecordset method of the
Querydef object to retrieve the recordset with the count contained in
the first row/field.
====

Now in my case, I am using DAO, but since he also mentioned how to do it
in ADO this may be of help to you.

--
Bri

laurenquantrell wrote:
I need to get the value of an output parameter back into my VBA
function calling a stored procedure.

I'm using the following construction to append a new record in a SQL
Server table:

Function ThisFunctionInsertsANewRecord()
On Error GoTo myErr

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandTimeout = 0
cmd.CommandText = "myStoredProcedureName"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParameter("@parameter1", _
adVarChar, adParamInput, 4, Left(me.Field1, 4))
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("@parameter2", _
adInteger, adParamInput, , me.Field2)
cmd.Parameters.Append prm

'note - this is an output parameter:
Set prm = cmd.CreateParameter("@UNIQUEID", _
adInteger, adParamOutput)
cmd.Parameters.Append prm

cmd.Execute Options:=adExecuteNoRecords

myExit:
On Error Resume Next
Set prm = Nothing
Set cmd = Nothing
Exit Function
myErr:
MsgBox Err.Number & " " & Err.Description
cmd.Execute "IF @@trancount > 0 ROLLBACK TRANSACTION", ,
adExecuteNoRecords
Resume myExit
End Function

The StoredProcedure looks like this:

Alter PROCEDURE myStoredProcedureName
@parameter1 nvarchar(4),
@parameter2 int,
@UNIQUEID int OUTPUT
AS

set nocount on

DECLARE @err int

BEGIN TRANSACTION

INSERT INTO
tblName (Column1, Column2)
VALUES
(@parameter1, @parameter2)

SELECT @UNIQUEID = SCOPE_IDENTITY()

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN
@err END

COMMIT TRANSACTION
SELECT @err = @@error IF @err <> 0 RETURN @err

What I want to do is to modify the VBA function so that it gets the
value of @UNIQUEID (SCOPE_IDENTITY()) so that I can open the new record
from the function.

Any help is appreciated!
lq


Nov 13 '05 #2

P: n/a
to be filed under DOH!...
I have solved my own question:

after: cmd.Execute Options:=adExecuteNoRecords
all you need is:

Dim X as long
x = cmd.Parameters("@UNIQUEID")

Nov 13 '05 #3

P: n/a
Bri,
Thanks for the response, but I solved my own question in a much simpler
way...

after: cmd.Execute Options:=adExecuteNoRecords
all you need is:

Dim X as long
x = cmd.Parameters("@UNIQUEID")

Nov 13 '05 #4

P: n/a
Bri
Lauren,

Actually, your solution is exactly what Steve was talking about. Glad
you found it on your own too.

--
Bri

laurenq uantrell wrote:
Bri,
Thanks for the response, but I solved my own question in a much simpler
way...

after: cmd.Execute Options:=adExecuteNoRecords
all you need is:

Dim X as long
x = cmd.Parameters("@UNIQUEID")

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.