Unable To Get Return Value From Stored Procedure

Hi everyone

I am trying something that should be so simple but I cant get it to work. I am calling a stored procedure to lock a table and update a counter. I have tried to follow the exact code use in the MSDN examples but it doesnt work and always returns VT_EMPTY. It also returns a closed recordset so attempting to close it fails. The database is updated correctly and there are no exceptions just no return value.

Here is the stored procedure

CREATE PROCEDURE GETMODELID AS
DECLARE @i INT
BEGIN TRANSACTION
 SET @i=(SELECT ModelID from tblModelID WITH (HOLDLOCK,TABLOCKX))+1
 UPDATE tblModelID SET ModelID = @i
COMMIT TRANSACTION
RETURN @i
GO


Here is the ADO written in C++.

TDatabase DB;

try

{

if(DB.Open()==false)

{

AfxMessageBox(DB.m_ErrStr);

return FALSE;

}

_variant_t vtEmpty2 (DISP_E_PARAMNOTFOUND, VT_ERROR);

_CommandPtr spCMD;

CREATEiNSTANCE(spCMD,Command);

spCMD->ActiveConnection = DB.m_Cnn;

spCMD->CommandText = "GETMODELID" ;

spCMD->CommandType = adCmdStoredProc;

spCMD->Parameters->Refresh();

_RecordsetPtr spRS;

_variant_t vRa;

spRS = spCMD->Execute( &vRa, &vtEmpty2, adCmdStoredProc );

_variant_t rtn(DEF_PARAM(spCMD,0L));

DB.Close();

return (int) rtn;

}