[RESOLVED] Problem with ADODB Command & Stored procedure

I have a problem registering parameters for a stored procedure.

Please see this SP carefully.

Code:

---

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 :

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.

Moved To Database Development

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 ?

Quote:

---

Originally Posted by 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 ?

---

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.

Try removing the values for the sizes.

Quote:

---

Originally Posted by dee-u

Try removing the values for the sizes.

---

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

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

Code:

---

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

Quote:

---

Originally Posted by techgnome

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

Code:

---

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.