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.

 

[MarkSweetland]

You could try this. I haven't tested, but should give you the idea...

Dim cmd As New ADODB.Command
Dim strClassName As String
Dim LastId As Integer

With cmd
.ActiveConnection = &quot;<your connection here>&quot;
.CommandTimeout = 0
.CommandType = adCmdStoredProc
.CommandText = &quot;sp_InsertAsNew&quot;
.Parameters.Refresh
' Input param
.Parameters(1).Value = strClassName
' Output Param
.Parameters(2).Direction = adParamOutput
.Parameters(2).Type = adInteger
.Execute
'Stored proc returns identity created
LastId = .Parameters(4).Value
End With


'-------------------------------
' Stored proc would look something like:
'
' Create dbo.sp_AddasNew AS
' @ClassName varchar(40),
' @ID integer output
'AS
' INSERT INTO Class(Class_Name) Select @ClassName
' SELECT @ID = IDENT_CURRENT('Class')
'


Mark

 

[echowave]

Thank everybody for your help, I got it working. Yahoo!!