Getting info From SQL server

[aidanh]

Hello everyone

I am having a problem using the command method. Basically I am trying to get a value returned from SQL Server. I have written a sql stored procedure which works I think, it looks like this:


------------------------------------------


CREATE procedure dbo.sp_add_to_clip_library (@id integer output)

-- Creates a new clip in the clip library with blank attributes
-- and the given clip id

as

begin transaction


insert into W_Clip_Library (ClipID)
values (null)

set @id = @@identity

commit
GO



----------------------------------------------------


Now the VB command should have id returned, but it doesn't I have set up the connection and the append command looks like this:

Public Sub ADOadd_to_table(objConn, stored_procedure, id)


Dim objCmd As New ADODB.Command

objCmd.ActiveConnection = objConn
objCmd.CommandText = stored_procedure
objCmd.CommandType = adCmdStoredProc

objCmd.Parameters.Append _
objCmd.CreateParameter("period_id", adInteger, adParamOutput, 32, id)

objCmd.Execute

End Sub


-----------------------------------------------



However id it still always = 0

It makes no sense

can you help??

 

[TerraSamba]

2 things: first set named parameters=true in the command object. (This should not make a difference in this case, but helps on bigger sp's)

add after the objCmd.execute:
id=objCmd.parameters("period_id&quot.value

I think that should do the trick



The gap between theory and practice is not as wide in theory as it is in practice.