OUTPUT parameter variable in Stored Proc expects a value

[whool]

Dear clever commrades,

I am trying to excute an sp on SQLSvr200 from VB using ADO. The sp has two input parameters and one OUTPUT param. I am passing the two input parameters but SQL Server seems to be expecting the OUTPUT param as well.

The SP:

ALTER PROC uspTest

(@OP int OUTPUT,
@Listid Varchar(40),
@Description varchar (40))
AS
--UPDATE statement etc....
__________________________________

VB:

Sub Testsp()

Dim cmd As New ADODB.Command
Dim prmlistID As New ADODB.Parameter
Dim prmDesc As New ADODB.Parameter
Dim prmOUT As New ADODB.Parameter


With prmOUT
.Name = "@OP"
.Type = adInteger
.Direction = adParamReturnValue
End With
cmd.parameters.Append prmOUT

With prmlistID
.Name = "@ListID"
.Value = ListID
.Type = adVarChar
.Size = 38
.Direction = adParamInput
End With
cmd.parameters.Append prmlistID

With prmDesc
.Name = "@Description"
.Value = ListDescription
.Type = adVarChar
.Size = 38
.Direction = adParamInput
End With
cmd.parameters.Append prmDesc

With cmd
.ActiveConnection = g_cn
.CommandType = adCmdStoredProc
.CommandText = m_spName
.Execute 'Fails Here!
End With

End Sub



The code fails at cmd.execute and I get the error from SQLSrv :"...Error converting Datatype Varchar to Int." SQL seems to fill its param variables from left to right, which is fair enough but what if the first one is an OUTPUT param variable as in my example?? Or am I misunderstaning the meaing of the SQL OUTPUT reserved word .
Any help much appreciated.

Yael Wasserman

 

[cmmrfrds]

You are using the wrong option on the variable that is an expected output. The return from SQL Server is an integer and adParamReturnValue is telling SQL Server that this is a return code not an output parameter. Also, when you send adParamReturnValue it must be the first parameter in the list which you are doing so this is another indication to SQL Server that this is the return code.

With prmOUT
.Name = "@OP"
.Type = adInteger
.Direction = adParamReturnValue
End With
cmd.parameters.Append prmOUT

This can be
.Direction = adParamOutput
or
.Direction = adParamInputOutput

 

[tlbroadbent]

Take a look at these resources.

http://www.geocities.com/priyadarshu/tech/Ado_vb.htm

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=6195&pg=1

http://www.15seconds.com/issue/010718.htm

http://www.vb-zone.com/upload/free/features/vbpj/1999/05may99/ds0599/ds0599.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.