469,938 Members | 2,492 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,938 developers. It's quick & easy.

SQLCommand ParameterDirection.ReturnValue not what it was in Classic ADO

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
Apr 28 '07 #1
0 2075

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by Henrik Dahl | last post: by
2 posts views Thread by Dmitri Khanine | last post: by
1 post views Thread by Tom | last post: by
3 posts views Thread by Jason Huang | last post: by
9 posts views Thread by fniles | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.