[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

STORED PROC @@IDENTITY OUTPUT PARAMETER

Posted on 2001-06-23
5
Medium Priority
?
286 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 5

Accepted Solution

by:
dtomyn earned 200 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 5

Expert Comment

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

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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
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.

649 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