SQLCommand ParameterDirect ion.ReturnValue not what it was in Classic ADO

[Judge Garth]

With classic ADO you could attach a ReturnValue parameter to a command containing inline parameters. I didn't add any OTHER parameter objects to do so, The entire command was in the CommandText. The example is to insert a single new row to table Notes which has an Identity field named Notes_ID. There is no problem inserting the row, but I want the new Note_ID value returned with the command.

Ex:

In SQL Server (2000 or 2005)
_______________ _______________ __
CREATE PROCEDURE [dbo].[Notes_Insert]
@Note text
AS

DECLARE @Note_ID int, @RETURN int, @ERROR int
SELECT @RETURN = 0, @ERROR = 0

INSERT INTO Notes
SELECT @Note

SELECT @Note_ID = @@IDENTITY, @ERROR = @@ERROR

IF @ERROR <> 0 BEGIN SET @RETURN = @ERROR END
ELSE BEGIN SET @RETURN = @Note_ID END

RETURN @RETURN
_______________ _______________ __

In VB6 using ADO and OLEDB for SQL Server the code was like this.
_______________ _______________ __
Dim lngNewIdentity as long
Dim Cmd as New ADODB.Command
Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "Notes_Insert(' Reply to Bill on Friday')"

' No parameter object yet, and none can be successfully used at this
' point except for one of type ReturnValue like this.

Dim pRet as New ADODB.Parameter
pRET.Type = adInteger
pRET.Direction = adParamReturnVa lue
Cmd.Parameters. Append pRET

Cmd.ActiveConne ction = cnSem

Cmd.Execute

lngNewIdentity = pRet.Value
_______________ _______________ __

I have thousands of line of code written like this that work great. Now I am
using VB 2005 and the SqlClient.SqlCo mmand
_______________ _______________ __

Dim Cmd as New SqlCommand, lngNewIdentity as long
Cmd.Connection = cnSem
Cmd.CommandType = CommandType.Tex t
Cmd.CommandText = "Notes_Inse rt 'Reply to Bill on Friday' "


Dim pRET As New SqlParameter("R etValue", SqlDbType.Int)
pRET.Direction = ParameterDirect ion.ReturnValue
Cmd.Parameters. Add(pRET)

Cmd.ExecuteNonQ uery()

lngNewIdentity = pRet.Value
_______________ _______________ __

The parameter value always comes back 0. Note too that the command type was Text not StoredProcedure . That may be where the problem lies, but it won’t run as CommandType.Sto redProcedure. With hundreds of stored procedures to code to, some having 20 or 30 parameters, the in-line (as I call them) parameters save a ton of code.

So, is it no longer possible to do what I want, or have I just not explored enough options. I haven’t used the OleDB command object to date, and that might be the only reason I haven’t conquered this. So far I’ve been making a second trip with a scalar reader to get the @@IDENTITY created by the new record insert.

Any insight would be really appreciated.

Tom Garth