STORED PROC @@IDENTITY OUTPUT PARAMETER

Does anyone know how in my Proc where I am doing an Insert statement return that new identity number to my ADO Code.  Could you please just give me an example on how to do this.  The way I do my stored proc currently is
strsql = "spInsertNew"
CurrentProject.Connection.Execute

I guess I will need to change this a little to get the output parameter.

Thanks all
bostonroxxAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
dtomynConnect With a Mentor Commented:
In your stored procedure, add an output parameter that will contain the new value.  A simple example of this would be:
IN SQL Server:

CREATE PROCEDURE dbo.spInsertNew
@FieldVal VARCHAR(30),
@id INT OUTPUT
AS
INSERT INTO dbo.SomeTable (SomeField)
VALUES (@FieldVal)

SELECT @id = @@IDENTITY

In VB, do something similar to this:

Private Sub CallSP()
Dim cmd As New ADODB.Command
Dim parm0 As New ADODB.Parameter
Dim parm1 As New ADODB.Parameter
Dim sSQL As String

cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "CountStoreQty"
   
parm0.Direction = adParamInput
parm0.Type = adChar
parm0.Size = 4
cmd.Parameters.Append parm0
   
parm1.Direction = adParamOutput
parm1.Type = adInteger
parm1.Size = 4
cmd.Parameters.Append parm1
   
parm0.Value = "SomeValue"
cmd.Execute
   
Text1.text = parm1.Value

End Sub

0
 
PaurthsCommented:
Hi bostonroxx,

u been asking 8 Q's in 10 days, and have not graded 1.
Didn't anyone provide a correct comment?

u should follow up on your Q's. Experts put in time and effort for u, u know!

cheers
Ricky
0
 
NetminderCommented:
Admin notified of User neglect. Force-accepted by
Netminder
CS Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.