Solved

STORED PROC @@IDENTITY OUTPUT PARAMETER

Posted on 2001-06-23
5
269 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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

896 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now