Solved

STORED PROC @@IDENTITY OUTPUT PARAMETER

Posted on 2001-06-23
5
271 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:bostonroxx
5 Comments
 
LVL 5

Accepted Solution

by:
dtomyn earned 50 total points
ID: 6221763
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
 
LVL 12

Expert Comment

by:Paurths
ID: 6278100
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
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6293927
0
 

Expert Comment

by:amp072397
ID: 6759007
0
 
LVL 5

Expert Comment

by:Netminder
ID: 6949399
Admin notified of User neglect. Force-accepted by
Netminder
CS Moderator
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question