SQLCommand ParameterDirection.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 = adParamReturnValue
Cmd.Parameters.Append pRET

Cmd.ActiveConnection = 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.SqlCommand
________________________________

Dim Cmd as New SqlCommand, lngNewIdentity as long
Cmd.Connection = cnSem
Cmd.CommandType = CommandType.Text
Cmd.CommandText = "Notes_Insert 'Reply to Bill on Friday' "


Dim pRET As New SqlParameter("RetValue", SqlDbType.Int)
pRET.Direction = ParameterDirection.ReturnValue
Cmd.Parameters.Add(pRET)

Cmd.ExecuteNonQuery()

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.StoredProcedure. 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