Problems with ADO commands from VB to SQL

[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??

 

[evaleah]

You say you "think" this works. Have you run the query? My first instinct is to tell you that the final piece might do better as:
SELECT id = @@identity

I am not sure that this is all you need, but it is how I do it and it works for me. This doesn't use it as an output parameter but it does give you the result you are looking for. Bottom line is to get out the new identity. This is always how I do that.

Hope that helps.

Just as a side note, I am sure you just typed it this way for the example but you have:
objCmd.CommandText = stored_procedure

"stored_procedure" should be "dbo.sp_add_to_clip_library" which is the name of your stored proc.

Eva

 

[Jmichael2468]

Try this:

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

I think the parameter should be the same as it is in SQL. If this doesn't help do a search on

http://www.vbforums.com.

I think I have seen this topic discussed there a few times.

 

[JamesLean]

Best thing to do is try it directly from SQL Query Analyzer to check the SP is working how you expect:

DECLARE @i int

EXEC sp_add_to_clip_library @i OUTPUT

SELECT @i AS TheIDValue

If this returns the correct value then start looking at your call from the VB. If it doesn't work then your SP is obviously at fault and needs to be corrected.

--James

 

[Jmichael2468]

I also don't know if the 32 and id are needed at the end of the append statement.

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