473,544 Members | 2,247 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Get stored procedure output value back in VBA

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 ThisFunctionIns ertsANewRecord( )
On Error GoTo myErr

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Set cmd = New ADODB.Command
Set cmd.ActiveConne ction = CurrentProject. Connection
cmd.CommandTime out = 0
cmd.CommandText = "myStoredProced ureName"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParam eter("@paramete r1", _
adVarChar, adParamInput, 4, Left(me.Field1, 4))
cmd.Parameters. Append prm

Set prm = cmd.CreateParam eter("@paramete r2", _
adInteger, adParamInput, , me.Field2)
cmd.Parameters. Append prm

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

cmd.Execute Options:=adExec uteNoRecords

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", ,
adExecuteNoReco rds
Resume myExit
End Function

The StoredProcedure looks like this:

Alter PROCEDURE myStoredProcedu reName
@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
4 45071
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 ThisFunctionIns ertsANewRecord( )
On Error GoTo myErr

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Set cmd = New ADODB.Command
Set cmd.ActiveConne ction = CurrentProject. Connection
cmd.CommandTime out = 0
cmd.CommandText = "myStoredProced ureName"
cmd.CommandType = adCmdStoredProc

Set prm = cmd.CreateParam eter("@paramete r1", _
adVarChar, adParamInput, 4, Left(me.Field1, 4))
cmd.Parameters. Append prm

Set prm = cmd.CreateParam eter("@paramete r2", _
adInteger, adParamInput, , me.Field2)
cmd.Parameters. Append prm

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

cmd.Execute Options:=adExec uteNoRecords

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", ,
adExecuteNoReco rds
Resume myExit
End Function

The StoredProcedure looks like this:

Alter PROCEDURE myStoredProcedu reName
@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
to be filed under DOH!...
I have solved my own question:

after: cmd.Execute Options:=adExec uteNoRecords
all you need is:

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

Nov 13 '05 #3
Bri,
Thanks for the response, but I solved my own question in a much simpler
way...

after: cmd.Execute Options:=adExec uteNoRecords
all you need is:

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

Nov 13 '05 #4
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:=adExec uteNoRecords
all you need is:

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

Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
8260
by: Steve Holden | last post by:
Has anyone, with any driver whatsoever, managed to retrieve output parameters from a SQL Server stored procedure? I've just been rather embarrassed to find out it's not as easy as it might seem, and people are saying bad things about Python as a result :-( mx.ODBC, which I regard as a highly-capable module, does not support the callproc()...
15
5979
by: Jarrod Morrison | last post by:
Hi All Im generally a vb programmer and am used to referencing multiple records returned from a query performed on an sql database and im trying to move some functions of my software into sql stored procedures. So far ive been able to move the functions relatively easily but im unsure about how to output multiple values from an sql stored...
0
6682
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft Visual Basic .NET version of this article, see 308049. For a Microsoft Visual C++ .NET version of this article, see 310071. For a Microsoft Visual J#...
5
1712
by: Eugene Anthony | last post by:
I have created the following stored procedure whereby it will check whether the categoryID is valid and if it is then the updation will be performed, else a -1 will be returned. This procedure will be execute with asp. Is this considered to be efficient?. create procedure usp_updateCategories @CategoryID int @CategoryDescription...
2
5441
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
3
1760
by: Bilbo | last post by:
I have a a headscratcher here: I have a form that when submitted should do 2 things when a user enters data and then clicks the Add button. Here goes: 1. Call a stored procedure called AddCompany to insert the company name from the Company Name textbox into the COMPANY table and return the @@IDENTITY of the company name just input into the...
6
2340
by: SandySears | last post by:
I am trying to use a stored procedure to insert a record using VS 2005, VB and SQL Server Express. The code runs without errors or exceptions, and returns the new identifer in the output parameters. It returns my error text message in another output parameter as "ok", which is the value that is set in the stored procedure prior to doing the...
9
4128
by: fniles | last post by:
I am using VB.NET 2003 and SQL2000 database. I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1 parameter (varchar(10)) and returns the identity column value from that table. When calling the stored procedure from VB.NET, in the CommandText, can I just say "INSERT_INTO_MYTABLE '12345'" instead of calling it with...
2
4084
by: jed | last post by:
I have created this example in sqlexpress ALTER PROCEDURE . @annualtax FLOAT AS BEGIN SELECT begin1,end1,deductedamount,pecentageextra FROM tax
0
7437
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7373
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7625
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7781
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7717
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
3421
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1848
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
993
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
677
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.