[RESOLVED] Problem with ADODB Command & Stored procedure

[vnatar]

I have a problem registering parameters for a stored procedure.

Please see this SP carefully.

CREATE PROCEDURE [dbo].[proc1] @ObjectType numeric AS
 SET NOCOUNT ON
 DECLARE @ObjectID INT
 BEGIN TRANSACTION
   UPDATE tblObject
   SET
       m_lCurrentObjectID = m_lCurrentObjectId + 1,
       @ObjectID = m_lCurrentObjectId + 1
     WHERE
       m_lObjectId = @ObjectType
 COMMIT TRANSACTION
 RETURN @ObjectID

That's it, we take an input parameter of type : NUMERIC, update a value in table
Return the updated value as a return value from SP, type INTEGER.

Now the ADODB portion of the code :

Set command = New ADODB.command
    command.ActiveConnection = myConn
    command.CommandType = adCmdStoredProc
    command.CommandText = "dbo.proc1"
    
    Set param1 = command.CreateParameter("ObjectType", adNumeric, adParamInput, 10)
    
    command.Parameters.Append param1
    param1.Value = 105
    Set param2 = command.CreateParameter("ObjectID", adInteger, adParamReturnValue, 10)
    command.Parameters.Append param2
    command.Execute

I get an exception : "Mutiple step OLE DB operation generated errors.

I dont understand why !
Can someone help me pls...

Thanks.

[Hack]

Moved To Database Development

[NeedSomeAnswers]

Is this SQL Server ?

Should your SP Input Variable be of type - Int ?

Also can we see your Connection Code, where MyConn is being opened ?

[vnatar]

Is this SQL Server ?

Should your SP Input Variable be of type - Int ?

Also can we see your Connection Code, where MyConn is being opened ?

Hi, Thanks for your time.

Yes, it is SQL Server, SP can't be changed.
MyConn is all good, because I just tried calling another SP which does not take any parameters, the same code worked fine. I could call & execute that SP.

^^ I mean to say, if i repalce the SP name & cut off the parameters portion in the "same" VB code posted above, i could execute an SP.

Problem here is with the parameters & the way i am handling it i guess. Do you see any issue in the VB code i posted here ?

Thanks again for your time.

[dee-u]

Try removing the values for the sizes.

[vnatar]

Try removing the values for the sizes.

No luck ! Same error : "Mutiple step OLE DB operation generated errors.

[techgnome]

you need to reverse how you add the parameters... RETURN values ALWAYS come first.... and they don't need a parameter name....

Set command = New ADODB.command
    command.ActiveConnection = myConn
    command.CommandType = adCmdStoredProc
    command.CommandText = "dbo.proc1"
    
    Set param2 = command.CreateParameter("RETURNVALUE", adInteger, adParamReturnValue, 10)
    command.Parameters.Append param2

    Set param1 = command.CreateParameter("ObjectType", adNumeric, adParamInput, 10)
    
    command.Parameters.Append param1
    param1.Value = 105
    command.Execute

messagebox.show command.parameters("RETURNVALUE").Value.tostring

See if that works.

-tg

[vnatar]

you need to reverse how you add the parameters... RETURN values ALWAYS come first.... and they don't need a parameter name....

Set command = New ADODB.command
    command.ActiveConnection = myConn
    command.CommandType = adCmdStoredProc
    command.CommandText = "dbo.proc1"
    
    Set param2 = command.CreateParameter("RETURNVALUE", adInteger, adParamReturnValue, 10)
    command.Parameters.Append param2

    Set param1 = command.CreateParameter("ObjectType", adNumeric, adParamInput, 10)
    
    command.Parameters.Append param1
    param1.Value = 105
    command.Execute

messagebox.show command.parameters("RETURNVALUE").Value.tostring

See if that works.

-tg

That was easy
Thanks a lot bro, it worked.

I'm a newbie, i never knew that there is an order !

Thanks[a ton] again.