Calling a stored procedure with a output parameter

[kkbullen]

I've been testing a stored procedure that has an output parameter.

When I add parameters to the sqlcommand this way, the output parameter
returned is always what I pass in:
With InsertLeg
.Transaction = mySQLTrans
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@Load_Num", SqlDbType.Int))
.Parameters.Add(New SqlParameter("@RC", SqlDbType.Int, 0,
ParameterDirection.Output))
End With

If I declare the sqlcommand this way, it works, the output parameter
from the stored procedure is returned in the @RC parameter.
With InsertLeg
.Transaction = mySQLTrans
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@Load_Num", SqlDbType.Int))
Dim Param As New SqlParameter("@RC", SqlDbType.Int)
Param.Direction = ParameterDirection.Output
.Parameters.Add(Param)
End With

Can someone tell me why these declarations are treated differently?
Thanks,
Kevin

 

[William Vaughn]

Frankly, I don't use either syntax to build the Parameters collection, I use
the Constructor.

cmd.Parameters.Add("@Outparm", SqlDbType.Int).Direction =
ParameterDirection.Output

I would also turn on the SQL Profiler to see what's being sent for each
syntax. It would also help to see the stored procedure. Is this "RC" really
the RETURN value?

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________