Returning an 'increment identity' field from Stored Proc to VB

[echowave]

Thanks in advance for any help that you can offer me:

So here is the scenario. I am working with a SQL Server 7 backend end and a VB front end.

I have a table that we can call ‘class’ and that table has two fields:
1. Class_Id – int(identity increment.)
2. Class_Name – Varchar
3. etc..

Once I click the ‘save’ button on my class form I insert the values by calling a stored proc and it adds the new record with the next incremented value for Class_Id. I need this new Class_Id value for my VB code so that I can perform some more stored procs based on this value. So my question is how do I return this class_id value from my stored proc to my vb app? I know that I should probably be using parameters but I have no idea how to do that. I hope this is enough info to answer my question, if not let me know..thanks.

 

[AWithers]

if you do a

select max(class_id) from class

immediately after the insert you should get the most recent record inserted. Of course this will only work if no other records are appended by other processes/users in the meantime

if you include it as part of the procedure

@newclass_id int OUTPUT

--your insert statement

set @newclass_id = select max(class_id) from class

then you can get at the value stored in @newclass_id immediately after the insert



Andy

 

[2314]

use this for parameter declaration
@newclassid int output
and this in your store proc code
set @newclass_id=@@identity

but be sure that you are using the procedure in a transaction so that you dont get the identity value
inserted by any other user.

 

[DangerPowers]

Without using output parameters (the most efficient way to go), at the end of your stored procedure, just do:

select @@Identity

This will return the identity that was create in the insert.

 

[echowave]

Thanks for the help so far..greatly appreciated.
Thus far, I think the way to go will be to use the @@identity within my stored proc since this will be a multi-user application. Well this leads me to another question..

The code that I am using to call the stored proc from VB is:
strSQLSave = "Exec sp_Insert_Class '" & txtClassName.Text & "'"
gcntConnection.Execute strSQLSave

What I need to know is how do I how do I return the output parameter @@identity to VB? I know that I have to use an output variable within the stored proc and use select @@identity, but once again I have no idea in how to return it to VB....

Any suggestions?

 

[sunila7]

Hi,
This is a VB code which will after execution return the output indentity from the SP to the variable lngOutputID.
----- This how VB code should look------
Dim objCmd As New ADODB.Command
Dim objDb As New ADODB.Connection


Set objDb = ConnectionString

With objCmd
.CommandText = "SPname"
.CommandType = adCmdStoredProc
.ActiveConnection = objDb
.Parameters.Refresh
.Parameters(1).Value = firstParam
.Parameters(2).Value = SecondParam

.Execute

End With

If Not IsNull(objCmd(3)) Then
lngOutputID = objCmd(3) 'this the return from the SP to the VB app.
Else
lngOutputID = 0
End If
Set objCmd = Nothing
Set objDb = Nothing

--- This the SP which returns the output parameter to the calling program.
create proc SPname
@firstParam int,
@secondparam int,
@outparam int output

AS

insert into tbl(fld1,fld2) values(1,2)
Select outputparam = @@identity
--- This the SP which returns the output parameter to the calling program.


Sunil