Solved

STORED PROC @@IDENTITY OUTPUT PARAMETER

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

756 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