Returning Value on INSERT

[Patrick Pohlmann]

Hi,

I am using a Stored Procedure to insert a new record into a table. I am
accessing this Stored Procedure via VBA in my Access adp like this:

cnn.Execute "exec MyProcedure MyParameter1, MyParameter2"

Can anybody tell my how to get the new Indentity ID after INSERT to use it
within Access?

Thank you for your help.

Regards

Patrick

[Paul Shapiro]

Post by Patrick Pohlmann
I am using a Stored Procedure to insert a new record into a table. I am
cnn.Execute "exec MyProcedure MyParameter1, MyParameter2"
Can anybody tell my how to get the new Indentity ID after INSERT to use it
within Access?

Your stored procedure can return the new ID as an output parameter. Here's
an example from Books OnLine:
CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()

BOL has more info under SCOPE_IDENTITY.

[Patrick Pohlmann]

Hi Paul,

thanks a lot. Yes, SCOPE_IDENTITY is what I need.

But how can I get the value into VBA? I presume cnn.Execute will not return
any values, would it?

Thanks again for help.

Best regards

Patrick

Post by Paul Shapiro
Your stored procedure can return the new ID as an output parameter. Here's
CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
BOL has more info under SCOPE_IDENTITY.

[Sylvain Lafontaine]

Two possible solutions. The first one would be to simply a Select statement
at the end of the SP in order to return the desired values and you could
catch this result into a Recordset on the VBA side. Note that you will add
to add the statement « SET NOCOUNT ON » at the very beginning of your SP.

The second possibility would be to use an ADO Command object to pass and
retrieve the parameters. This would be the most normal way of doing this.
There are multiple examples on the Internet on how to use the Command object
of ADO.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Post by Patrick Pohlmann
Hi Paul,
thanks a lot. Yes, SCOPE_IDENTITY is what I need.
But how can I get the value into VBA? I presume cnn.Execute will not
return any values, would it?
Thanks again for help.
Best regards
Patrick

Post by Paul Shapiro
Your stored procedure can return the new ID as an output parameter.
CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
BOL has more info under SCOPE_IDENTITY.

[Bob McClellan]

I agree with Sylvain. ADO is the way to go..
something like this should work..

dim ReturnScopeID as long

Dim oCmd As Command, param As Parameter
Dim cn As New ADODB.Connection, sqlString As String

sqlString = "ReturnValExample"
Set oCmd = New ADODB.Command
Set cn = CurrentProject.Connection
Set oCmd.ActiveConnection = cn
oCmd.CommandText = sqlString
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 15

'Param1
Set param = New ADODB.Parameter
param.Type = adInteger
param.Direction = adParamInput
param.Value = Me.p1
param.Name = "Param1"
oCmd.Parameters.Append param

'Param2
Set param = New ADODB.Parameter
param.Type = adInteger
param.Direction = adParamInput
param.Value = Me.p2
param.Name = "Param2"
oCmd.Parameters.Append param

'Return ScopeID
Set param = New ADODB.Parameter
param.Type = adInteger
param.Direction = adParamOutput
param.Name = "scID"
oCmd.Parameters.Append param

oCmd.Execute , , adExecuteNoRecords
ReturnScopeID = ocmd.parameters("scID")
cn.Close
set cn = nothing

hth,
..bob

Post by Patrick Pohlmann
Hi Paul,
thanks a lot. Yes, SCOPE_IDENTITY is what I need.
But how can I get the value into VBA? I presume cnn.Execute will not
return any values, would it?
Thanks again for help.
Best regards
Patrick

Post by Paul Shapiro
Your stored procedure can return the new ID as an output parameter.
CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
BOL has more info under SCOPE_IDENTITY.

[Dave Engle]

I have been looking all over the place for a working example. All I needed was the return value from the SPROC since all it does it assigns the next client id no. Thank you Bob!

Have A Great Thanksgiving Every One! Mine, I know will be better since this part is done in my ADP

Post by Patrick Pohlmann
Hi,
I am using a Stored Procedure to insert a new record into a table. I am
cnn.Execute "exec MyProcedure MyParameter1, MyParameter2"
Can anybody tell my how to get the new Indentity ID after INSERT to use it
within Access?
Thank you for your help.
Regards
Patrick

Your stored procedure can return the new ID as an output parameter. Here is
CREATE PROCEDURE dbo.InsertCategory
@CategoryName nvarchar(15),
@Identity int OUT
AS
BOL has more info under SCOPE_IDENTITY.

Post by Patrick Pohlmann
Hi Paul,
thanks a lot. Yes, SCOPE_IDENTITY is what I need.
But how can I get the value into VBA? I presume cnn.Execute will not return
any values, would it?
Thanks again for help.
Best regards
Patrick

Post by Sylvain Lafontaine
Two possible solutions. The first one would be to simply a Select statement
at the end of the SP in order to return the desired values and you could
catch this result into a Recordset on the VBA side. Note that you will add
to add the statement ? SET NOCOUNT ON ? at the very beginning of your SP.
The second possibility would be to use an ADO Command object to pass and
retrieve the parameters. This would be the most normal way of doing this.
There are multiple examples on the Internet on how to use the Command object
of ADO.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Post by Bob McClellan
I agree with Sylvain. ADO is the way to go..
something like this should work..
dim ReturnScopeID as long
Dim oCmd As Command, param As Parameter
Dim cn As New ADODB.Connection, sqlString As String
sqlString = "ReturnValExample"
Set oCmd = New ADODB.Command
Set cn = CurrentProject.Connection
Set oCmd.ActiveConnection = cn
oCmd.CommandText = sqlString
oCmd.CommandType = adCmdStoredProc
oCmd.CommandTimeout = 15
'Param1
Set param = New ADODB.Parameter
param.Type = adInteger
param.Direction = adParamInput
param.Value = Me.p1
param.Name = "Param1"
oCmd.Parameters.Append param
'Param2
Set param = New ADODB.Parameter
param.Type = adInteger
param.Direction = adParamInput
param.Value = Me.p2
param.Name = "Param2"
oCmd.Parameters.Append param
'Return ScopeID
Set param = New ADODB.Parameter
param.Type = adInteger
param.Direction = adParamOutput
param.Name = "scID"
oCmd.Parameters.Append param
oCmd.Execute , , adExecuteNoRecords
ReturnScopeID = ocmd.parameters("scID")
cn.Close
set cn = nothing
hth,
..bob
Submitted via EggHeadCafe
Creating a SharePoint Designer workflow
http://www.eggheadcafe.com/tutorials/aspnet/4b58a093-2c77-4568-81f0-f31feee2704a/creating-a-sharepoint-designer-workflow.aspx

[tom_willpa]

high quality Soccer jerseys NBA Jersey tracksuit and jackets, GHD
hairstraightener supplier from www.willpa.com

Are you a Retail businessman who bother by the purchase price? China
Cheapest TOP wholesale website can help you

we are specialize in replica sport goods manufacturing in china, we can
offer you all kinds of soccer jersey, NBA jersey,shoes and so on. they are
the best brand replica goods whih are look the same as the original goods.
excellent quality and steady supply for them. we have been marketed in Europe
and American for 3 year. all the goods we offer are AAA quality. our soccer
jersey are Thailand style. If any goods you buy from my company have problem,
we will refund or resend them again. Most of ourProducts have no minimum
order requirements,soyou can shop retail goods at wholesale prices. if you
can buy more than 300usd. We offer free shipping. The more you buy the more
discount for you.

National soccer jerseys: http://www.willpa.com
Club soccer jerseys: http://www.willpa.com
NBA Jerseys: http://www.willpa.com
T-shirt and shirt: http://www.willpa.com
Tracksuit: http://www.willpa.com
Hoody & Jackets: http://www.willpa.com
UGG boots: http://www.willpa.com
Hair style: http://www.willpa.com
shopping Index: http://www.willpa.com

EMS shipping. 7days arrive, paypal accept

want more information pls contact us or check our website: www.willpa.com